Transact SQL :: Order By Used With Case Statement

Sep 24, 2015

I use Order By Used with Case Statement and may be this is the reason for which i am getting error

Windowed functions can only appear in the SELECT or ORDER BY clauses.

Here is my complete sql where i use Row_Number function for pagination with sorting.

DECLARE @StartIndex INT
DECLARE @EndIndex INT
DECLARE @SortColumn VARCHAR(MAX)
DECLARE @SortDirection VARCHAR(MAX)
SET @StartIndex = 1

[Code] ....

View 7 Replies


ADVERTISEMENT

Transact SQL :: CASE Order Of Evaluation

Sep 24, 2015

I have the following query in a user-defined function. It accepts a single string parameter, but for the sake of simplicity, I have substituted actual strings in the query. It basically checks the passed string. If it ends with "Id", it strips off the "Id" and returns the resulting string. If it ends with "Id" followed by a digit, it strips that off and returns the string.

SELECT CASE
WHEN LEN('IncidentViolationId') > 2 AND RIGHT('IncidentViolationId', 2) = 'Id' THEN LEFT('IncidentViolationId', LEN('IncidentViolationId') - 2)
WHEN PATINDEX('%Id[0-9]', 'IncidentViolationId') > 1 THEN LEFT('IncidentViolationId', PATINDEX('%Id[0-9]', 'IncidentViolationId') - 1)
ELSE 'IncidentViolationId' END

This code has worked flawlessly for quite some time and all of a sudden I get "Invalid length parameter passed to left function". I understand why LEFT() would normally fail if I passed it a -1 for the second parameter, but in this scenario, asI understand it, it never should have reached the second WHEN condition since the first one evaluates to true. Why and why all of a sudden?

View 10 Replies View Related

Alternative To CASE In The Order By Statement?

Feb 15, 2008

 The query below at the bottom works, but when I try to add the line below (a third line/argument in the CASE statement) then I get an error. It is not possible to have a table name and a DESC (sort order) in the same CASE line/argument. Are there other ways to accomplish this, because I would like to sort with both "h.ObjectType" and "h.ObjectType DESC"?             WHEN @SortBy = 2 THEN h.ObjectType DESC      SELECT weight, ObjectText.ObjectId, ObjectText.ObjectType, ObjectText.Title, ObjectText.ShortText    FROM @hits h INNER JOIN ObjectText ON h.id = ObjectText.ObjectId AND h.ObjectType = ObjectText.ObjectType    WHERE ObjectText.LanguageCode = @LanguageCode    ORDER BY        CASE WHEN @SortBy = 0 THEN weight             WHEN @SortBy = 1 THEN h.ObjectType        END    DESCRegards, Sigurd 

View 4 Replies View Related

Transact SQL :: Update Statement In Select Case Statement

May 5, 2015

I am attempting to run update statements within a SELECT CASE statement.

Select case x.field
WHEN 'XXX' THEN
  UPDATE TABLE1
   SET TABLE1.FIELD2 = 1
  ELSE
   UPDATE TABLE2
   SET TABLE2.FIELD1 = 2
END
FROM OuterTable x

I get incorrect syntax near the keyword 'update'.

View 7 Replies View Related

SQL 2012 :: Order Of Preference In Case Statement

Sep 9, 2015

I'm trying to set up a statement that gives me a field called 'BINNO' if the payor = Commercial. But, I have a few customers that don't have Commercial. They have a Payor of Grant or Part D. How would I set up a statement that looks for Commerical 1st...then Grant or Part D. I started with this

case when inscomp.payor = 'COMMERCIAL' then INSCOMP.BINNO

View 9 Replies View Related

Column Aliases In Case Statement In Order By

Jan 18, 2007

Hi All,

I have this query :

select col1, col2, col3, col4, col5,..... , (select col99 from tab2) as alias1 from tab1 where <condition>
order by
case @sortby
when 'col1' then col1,
when 'col2' then col2,
when 'col3' then col3,
when 'col99' then col99
end

when i execute the above query it gives me the following error message.

Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'col99'.

Thanks in advance.

Thanvi.

View 5 Replies View Related

Error Using Case Expression In Order By Statement

Feb 22, 2008

I have been trying to get the following Selects to work using a case expression in the order by section.

I know I can easily separate out the two statements but I want to do a select using the case statement below ;
however, I keep getting error 16 --"Order by items must appear in the select list if the statement contains a union.

If remove the case statement and put order by "internalID desc" I receive no errors. Moreover, when I take out the union statement and execute the two select statements with each including the case expression it runs as planned.

Can anyone tell what the problem is here? I have combed the web looking for an answer, but it seems that the statement is valid.


Thanks
J
declare @date
set @date = '2001'



select internalID from section_data_v3

union

select internalID from section_data_v4

order by
case when (@date = '2001') then internalID end desc

View 5 Replies View Related

Using Case Statement To Determine Order By Field And Direction (asc Or Desc) When Using Row_number

Aug 21, 2007

I am trying to order by the field and direction as provided by input parameters @COLTOSORTBY and @DIR while using a CTE and assigning Row_Number, but am running into syntax errors.

Say I have a table called myTable with columns col1,col2,col3,

Here's what I'm trying to do

with myCTE AS
(
Select
col1
,col2
,col3
,row_number() over (order by
case when(@DIR = 'ASC') then


case when @COLTOSORTBY='col1' then col1 asc
when @COLTOSORTBY='col2' then col2 asc
else col3 asc
end
else

case when @COLTOSORTBY='col1' then col1 desc
when @COLTOSORTBY='col2' then col2 desc
else col3 desc
end
end
from myTable
)



Please let me know what i can do with minimal code repetition and achive my goal of dynamically sorting column and direction. I do not want to use dynamic SQL under any circumstance.

Thanks.

View 7 Replies View Related

Transact SQL :: CASE Statement For A WHERE

Sep 25, 2015

I've been beating my head against this for a bit and haven't been able to figure this out I want to pull 1 set of values between a date time range if @Report = '1' but if @Report = 2 or 3 I want it to drop 3 of the parameters so it will pull all items. by dropping the where parameters for @BeginRangeDate and @LookOutDate and L.COLineStatus. My report works great if I go in and run Report 1 with the date parameters in the where statement and also works great for Reports 2, 3 if I REM out those 3 items in the Where statement. Here is what I currently have

CASE@Report
WHEN'1'THEN
WHEREL.RequestedShipDate>=@BeginRangeDate
ANDL.RequestedShipDate<=@LookoutDate
ANDDO.ForecastDate>=@ForecastDate

[Code] .....

View 7 Replies View Related

Transact-SQL Help - CASE Statement And Group By

Jul 20, 2005

I've always been mistified why you can't use a column alias in the group byclause (i.e. you have to re-iterate the entire expression in the group byclause after having already done it once in the select statement). I'mmostly a SQL hobbiest, so it's possible that I am not doing this in the mostefficient manner. Anyone care to comment on this with relation to thefollowing example (is there a way to acheive this without re-stating theentire CASE statement again in the Group By clause?):Select 'Age' =CaseWHEN(SubmittedOn >= DATEADD(dd, - 30, GETDATE())) AND (SubmittedOn <=DATEADD(dd, - 0, GETDATE()))THEN '0-30 Days Old'WHEN(SubmittedOn >= DATEADD(dd, - 60, GETDATE())) AND (SubmittedOn <=DATEADD(dd, - 31, GETDATE())) Then '31-60 Days Old'WHEN(SubmittedOn >= DATEADD(dd, - 90, GETDATE())) AND (SubmittedOn <=DATEADD(dd, - 61, GETDATE())) Then '61-90 Days Old'WHEN(SubmittedOn <= DATEADD(dd, - 91, GETDATE())) THEN '91+ Days Old'ELSE cast(SubmittedOn as varchar(22))end,max(SubmittedOn), COUNT(SCRID) AS NbrSCRsFrom SCRViewWHERE(StatusSort < 90) ANDCustomerID = 8 andUserID = 133group byCaseWHEN(SubmittedOn >= DATEADD(dd, - 30, GETDATE())) AND (SubmittedOn <=DATEADD(dd, - 0, GETDATE()))THEN '0-30 Days Old'WHEN(SubmittedOn >= DATEADD(dd, - 60, GETDATE())) AND (SubmittedOn <=DATEADD(dd, - 31, GETDATE())) Then '31-60 Days Old'WHEN(SubmittedOn >= DATEADD(dd, - 90, GETDATE())) AND (SubmittedOn <=DATEADD(dd, - 61, GETDATE())) Then '61-90 Days Old'WHEN(SubmittedOn <= DATEADD(dd, - 91, GETDATE())) THEN '91+ Days Old'ELSE cast(SubmittedOn as varchar(22))endOrder by max(submittedon) descThanks,Chad

View 4 Replies View Related

Transact SQL :: Grouping With Case Statement

Aug 9, 2015

In the below query, I can get the individual/single group by columns as well as multiple but I cannot control the order in which I would like to group by the data.

So lets say I want to group the data by OS->browser->browser_version(just one example) then I cannot achieve that as the order of OS column comes later in the query.

I know one option would be to write a dynamic SQL but i dont want to do that because of performance reasons. Any other way this can be achieved?

select 
case when @include_browser =
1 then browser_name end as browser_name, 
case when @include_browser_version
= 1 then browser_version end
as browser_version,

[Code] ....

View 4 Replies View Related

Transact SQL :: Case Statement With Different Columns

Oct 20, 2015

Is it possible to combine a CASE statement with two different columns to retrieve data into one result column? In one column it has multiple JobCode, but this needs to be divided.  The only way I can see to do this is take the data from another column to get the results.Ex. JobCode - has one code for several job descriptions (there are about 30), but all within this code cannot have the same level of access.  So I need to divide them out, and put them in one column for AccessLevel. 

 JobTitle - has one code for one job, (but there are over 100).I want to pull from both columns to get the results I need to assign appropriate access level in one column.

Case JobCode     (they all have same job code, but everyone in this job code should not have same access)
When '45' Then '1' (Principal, Asst. Prin, or any Administrator, Counselors)
When '25' Then '2' (this could be teacher, etc. )      
Case JobTitle   (this is how access should be)
        When '12345' Then '1' (this is Administration only) 

[code]....

View 12 Replies View Related

Transact SQL :: Update Statement With Case

Nov 11, 2015

I am trying to run the below but I get an error of 'Incorrect syntax ')''  --- I have tried every angle I can think of around the parens to fix this but nothing I do is working.

UPDATE abcdefg
SET [Date] = GETDate(),
[readytogo] =
(
CASE WHEN [customername] NOT IN (Select [customername] from [server].[database].[dbo].[view])
THEN 'Yes'
ELSE
'Needs Verification'

[code]....

View 5 Replies View Related

Transact SQL :: Case Statement In Update Clause

Jun 4, 2015

I have used the below update query. However, its updating only the first value. Like its updating AB with volume when c.Type  = ABC, similarly for CD. Its not updating based on the 2nd or the next case condition.
 
Update XYZ Set AB = a.Amt * (CASE WHEN c.Type = 'ABC'  THEN  (c.volume)
 WHEN c.TYPE = 'DEF'  THEN  (c.volume)
 WHEN c.Type = 'GHI'  THEN  (c.volume)
 Else 0
 END),
 CD = CASE WHEN c.Type = 'MARGIN' THEN '4105.31'
 WHEN c.Type = 'ABC' THEN '123.1'
 WHEN c.Type = 'DEF' THEN '234.2'
WHEN c.Type = 'GHI' THEN '567.1'
END
 from table1 a join table2 b
 on a.Cust = b.Customer
 join table3 c
 on b.account = c.account and b.channel =c.channel

Why its not working properly? But if i use Select statement instead of update query its working properly.

View 18 Replies View Related

Transact SQL :: How To Add A Letter In Front Of Value Using Case Statement

Jul 28, 2015

I am trying to add the letters 'MS' in front of value while using a case statement. If Dispo = 2 I want it to pull back 'Inactive', else I want it to pull back the Value with MS in front (eg. "MS14"). The data in the value column are numbers. Would I use a CONCAT? If so where does that need to go?

Case when dispo = 2 then 'Inactive' else cast(Value as varchar(11)) end ,

View 27 Replies View Related

Transact SQL :: How To Use Case Statement In Select Condition

Nov 6, 2015

below is my original query

select Value = count(*) from dbo.test 

I have 20 rows in dbo.test so i will get 20 as my output, now i need to write a case statement here such that when count(*) = 0 then it should display text filed 'NO Data' else it should display the count.

View 5 Replies View Related

Transact SQL :: How To Use A Between In Case Statement For Joining 2 Tables

Jul 27, 2015

below is the sql query i am trying to write and i receive an error saying Incorrect syntax near the keyword 'Between'.

Update dbo.Claims_Primary_Adjuster_Test_Sujith
SET
Claims_Primary_Adjuster_Test_Sujith.Supervisor = Claims_Internal_Transfer.Supervisor,
Claims_Primary_Adjuster_Test_Sujith.Office = Claims_Internal_Transfer.Office,
Claims_Primary_Adjuster_Test_Sujith.Specialty = Claims_Internal_Transfer.Specialty
from dbo.Claims_Primary_Adjuster_Test_Sujith
Left Outer Join Stg.HS_DW_RV_ClaimsON Claims_Primary_Adjuster_Test_Sujith.Claim_Number = HS_DW_RV_Claims.ClaimNumber

[code]....

how to modify my code such that it works for my condition?

View 10 Replies View Related

Transact SQL :: Nested Select Case Statement

May 18, 2015

I need to perform an update where there are multiple scenarios which determine the value that is entered. Below is a sort've psuedo code of how it needs to be.

Update MyTable SET MyColumn = CASE WHEN MyCol1 = 'Value1' Then NewValue Else
WHEN MyCol1 <> 'Value1' And MyCol2 = 'Active' Then 'Value1'

In the scenario where MyCol1 <> Value1 and MyCol2 <> 'Active' then no update would occur and the original value would remain intact.

View 2 Replies View Related

Transact SQL :: Using Case Statement With Update Query

Jun 22, 2015

I have a table A  and lookup table B.

table A:
| ID | FRUIT | VEGETABLE | GOOD |
--------------------------------------------
|  1  | orange | cabbage     |  no   |
|  1  | apple  | lettuce        |  yes   |
|  1  | kiwi     | broccoli      |  no   |
|  1  | pear    | kale           |  yes   |

table B:
| ID | FRUIT | VEGETABLE |
-------------------------------
| 1  | apple  |  lettuce       |
| 2  | pear    |   kale         |

If the fruit and vegetable in table A is found in table B, then set the GOOD column = yes, else no.

This is what I have so far.

update tableA
set GOOD =
(case when tableA.id = C.id then 'yes'
else 'no'
end
)
from
(select tableA.id as id
from tableA A
left join tableB B on B.fruit = A.fruit
and B.vegetable = A.vegetable) C

View 6 Replies View Related

Transact SQL :: Unable To Get Required Output Using Case Statement

May 17, 2015

Table Structure
EID    COLA    COLB
1    name    A
1    age    23
1    city    hyd
1    email    abc@live.in
1    mobile    45126
2    name    B
2    age    43

[code]....

how to display the result where any of the mandatory fields (name,age,city,email,mobile)are missing then it should display as that field as Null

View 9 Replies View Related

Transact SQL :: Case Statement In Where Clause - Select First / Default Value If Two Different Value Exists

Nov 16, 2015

I have scenario where i have to pick one particular value from where condition. Here is the example:A store can have different types i-e A or B , A and B or either A or B.

Store     Type    Sales
11           A        1000
23       A      1980
23       B       50
5         B      560

I want to filter the store in "where clause"  where

1)- if the store has type A and  B, then assign only A
2)- if the store has  type A associated with it then assign A
3)- if the store has type B associated with it, then assign B.

Select Store, sum(sales), Type
from table1
where (TYPE]=  (case when [TYPE] in ('A','B') then 'A'
when [TYPE]='A' then 'A' else 'B'end))
GROUP BY [store], [TYPE]

The above statement is not working for when store has only Type B associated with it.

View 7 Replies View Related

Transact SQL :: Find Exact Word In String Within Case Statement

Aug 6, 2015

I am required to find or check that a specific word exists in string or not.

Suppose I have to find the word 'st' than I need the result true if and only if the following occurrences are there.

1. 'St is valid;'    -> true
2. 'DOB is valid;ST is invalid;'    -> true
3. 'DOB is valid; ST is invalid;'    -> true
4. 'DOB is valid;invalid ST;'    -> true
5. 'DOB is valid; invalid ST;'    -> true
6. 'DOB is valid; invalid STate;'    -> false

Means the exact ST should be search. It is  not free text search.

T-SQL is needed to be used in select statement with case using PATINDEX, RegEx or any suitable t-sql command.

View 10 Replies View Related

Transact SQL :: Case Statement For A New Column Based On Date And Username Columns

Sep 2, 2015

Below is the SQL Query i am currently having

SELECT IG_FinancialTransactionSummary.ClaimNum,IG_FinancialTransactionSummary.TransactionCode,
IG_FinancialTransactionSummary.TransactionDate,IG_FinancialTransactionSummary.Username,
FinancialTransactionSummaryTest.ClaimNum,FinancialTransactionSummaryTest.TransactionAmount,
FinancialTransactionSummaryTest.UserName--,FinancialTransactionSummaryTest.TransactionDate

[Code] ....

And here is the result dataset

ClaimNumTransactionDateUsername ClaimNum TransactionAmountUserName
2000074 20150209jerry.witt 2000074 -10000DATAFIX INSERTED ON 20150626 AT 162152493 LOCAL
2000074 20150626DATAFIX INSERTED ON 20150626 AT 162152493 LOCAL 2000074 -10000DATAFIX INSERTED ON 20150626 AT 162152493 LOCAL

[Code] .....

So,if we look at the result set, we notice 2 conditions where the IG_FinancialTransactionSummary.Username is like 'Data' and if we see the transaction date then sometimes that is the max transaction date or sometimes there are transactions that happened after but that doesn't have like '%data%' in username . So, i need to add a new column to my sql query which should basically verify if the username is like '%data%' and if that is the max(transaction date) or even if there are any transactions after that doesn't have like '%data%' then YES else No.

View 13 Replies View Related

Transact SQL :: Adding Case When Statement With Group By Query Doesn't Aggregate Records

Aug 28, 2015

I have a a Group By query which is working fine aggregating records by city.  Now I have a requirement to focus on one city and then group the other cities to 'Other'.  Here is the query which works:

Select [City]= CASE WHEN [City] = 'St. Louis' THEN 'St. Louis' ELSE 'Other Missouri City' END, SUM([Cars]) AS 'Total Cars' 
From [Output-MarketAnalysis]
Where [City] IN ('St. Louis','Kansas City','Columbia', 'Jefferson City','Joplin') AND [Status] = 'Active'
Group by [City]

Here is the result:

St. Louis 1000
Kansas City 800
Columbia 700
Jefferson City 650
Joplin 300

When I add this Case When statement to roll up the city information it changes the name of the city to 'Other Missouri City' however it does not aggregate all Cities with the value 'Other Missouri City':

Select [City]= CASE WHEN [City] = 'St. Louis' THEN 'St. Louis' ELSE 'Other Missouri City' END, SUM([Cars]) AS 'Total Cars' 
From [Output-MarketAnalysis]
Where [City] IN ('St. Louis','Kansas City','Columbia', 'Jefferson City','Joplin') AND [Status] = 'Active'
Group by [City]

Here is the result:

St. Louis 1000
Other Missouri City 800
Other Missouri City 700
Other Missouri City 650
Other Missouri City 300

What I would like to see is a result like:

St. Louis 1000
Other Missouri City 2450

View 5 Replies View Related

Transact SQL :: Upper Case To Lower Case Conversion

May 4, 2015

I have column with value of all upper case, for example, FIELD SERVICE, is there anyway, I can convert into Field Service?

View 7 Replies View Related

Problem Using Result From CASE In Another CASE Statement

Nov 5, 2007

I have a view where I'm using a series of conditions within a CASE statement to determine a numeric shipment status for a given row. In addition, I need to bring back the corresponding status text for that shipment status code.

Previously, I had been duplicating the CASE logic for both columns, like so:




Code Block...beginning of SQL view...
shipment_status =
CASE
[logic for condition 1]
THEN 1
WHEN [logic for condition 2]
THEN 2
WHEN [logic for condition 3]
THEN 3
WHEN [logic for condition 4]
THEN 4
ELSE 0
END,
shipment_status_text =
CASE
[logic for condition 1]
THEN 'Condition 1 text'
WHEN [logic for condition 2]
THEN 'Condition 2 text'
WHEN [logic for condition 3]
THEN 'Condition 3 text'
WHEN [logic for condition 4]
THEN 'Condition 4 text'
ELSE 'Error'
END,
...remainder of SQL view...






This works, but the logic for each of the case conditions is rather long. I'd like to move away from this for easier code management, plus I imagine that this isn't the best performance-wise.

This is what I'd like to do:



Code Block
...beginning of SQL view...
shipment_status =
CASE
[logic for condition 1]
THEN 1
WHEN [logic for condition 2]
THEN 2
WHEN [logic for condition 3]
THEN 3
WHEN [logic for condition 4]
THEN 4
ELSE 0
END,


shipment_status_text =

CASE shipment_status

WHEN 1 THEN 'Condition 1 text'

WHEN 2 THEN 'Condition 2 text'

WHEN 3 THEN 'Condition 3 text'

WHEN 4 THEN 'Condition 4 text'

ELSE 'Error'

END,
...remainder of SQL view...


This runs as a query, however all of the rows now should "Error" as the value for shipment_status_text.

Is what I'm trying to do even currently possible in T-SQL? If not, do you have any other suggestions for how I can accomplish the same result?

Thanks,

Jason

View 1 Replies View Related

TSQL - Use ORDER BY Statement Without Insertin The Field Name Into The SELECT Statement

Oct 29, 2007

Hi guys,
I have the query below (running okay):



Code Block
SELECT DISTINCT Field01 AS 'Field01', Field02 AS 'Field02'
FROM myTables
WHERE Conditions are true
ORDER BY Field01

The results are just as I need:


Field01 Field02

------------- ----------------------

192473 8461760

192474 22810



Because other reasons. I need to modify that query to:



Code Block
SELECT DISTINCT Field01 AS 'Field01', Field02 AS 'Field02'
INTO AuxiliaryTable
FROM myTables
WHERE Conditions are true
ORDER BY Field01
SELECT DISTINCT [Field02] FROM AuxTable
The the results are:

Field02

----------------------

22810
8461760

And what I need is (without showing any other field):

Field02

----------------------

8461760
22810


Is there any good suggestion?
Thanks in advance for any help,
Aldo.

View 3 Replies View Related

ORDER BY CASE

Dec 22, 2006

this is my query="SELECT i.itemid,title,SortKey from Items AS i JOIN Links AS L ON(i.ItemID=L.ItemID) WHERE L.instructorID='12232' AND courseID='12' ANDtype='Audio' order by CASE WHEN Sortkey is not null then 1 else 0 end"My SortKey can be NULL. Here's the output I am getting:(the || is to denote sortkey column)37542 Tape 1 ||37544 Tape 2 ||37819 Symphony1 ||37820 Symphony2 ||37821 Symphony3 ||37828 Symphony ||60962 Test ||61570 New Test Record |Africa|61572 Test 3 |Africa 1|63186 Music for Strings |Brazil|I want use Sortkey when it is not null. desired output:61570 New Test Record |Africa|61572 Test 3 |Africa 1|63186 Music for Strings |Brazil|37542 Tape 1 ||37544 Tape 2 ||37819 Symphony1 ||37820 Symphony2 ||37821 Symphony3 ||37828 Symphony ||60962 Test ||

View 10 Replies View Related

SQL Server 2012 :: Update Statement With CASE Statement?

Aug 13, 2014

i was tasked to created an UPDATE statement for 6 tables , i would like to update 4 columns within the 6 tables , they all contains the same column names. the table gets its information from the source table, however the data that is transferd to the 6 tables are sometimes incorrect , i need to write a UPDATE statement that will automatically correct the data. the Update statement should also contact a where clause

the columns are [No] , [Salesperson Code], [Country Code] and [Country Name]

i was thinking of doing

Update [tablename]
SET [No] =
CASE
WHEN [No] ='AF01' THEN 'Country Code' = 'ZA7' AND 'Country Name' = 'South Africa'
ELSE 'Null'
END

What is the best way to script this

View 1 Replies View Related

How To Write Select Statement Inside CASE Statement ?

Jul 4, 2006

Hello friends,
I want to use select statement in a CASE inside procedure.
can I do it? of yes then how can i do it ?

following part of the procedure clears my requirement.

SELECT E.EmployeeID,
CASE E.EmployeeType
WHEN 1 THEN
select * from Tbl1
WHEN 2 THEN
select * from Tbl2
WHEN 3 THEN
select * from Tbl3
END
FROM EMPLOYEE E

can any one help me in this?
please give me a sample query.

Thanks and Regards,
Kiran Suthar

View 7 Replies View Related

2 Case Statements In Order By

Oct 31, 2013

It will be part of the stored proc, but for now I couldn't even get it running in ssms. It will be two parameters/variables, one for order by column name and other for order by direction, i.e. desc or asc.I have tried following three ways, but none is working:

(1)
order by case when @Sort_by= '[A_ID]' AND @Sort_Dir ='Desc' then A_ID end desc
case when @Sort_by= '[A_ID]' AND @Sort_Dir ='Asc' then A_ID end asc

(2)
order by case when @Sort_by= '[A_ID]' AND @Sort_Dir ='Desc' then A_ID desc end
case when @Sort_by= '[A_ID]' AND @Sort_Dir ='Asc' then A_ID asc end

(3)
ORDER BY CASE @Sort_by when '[A_ID]' then [A_ID] end
Case @Sort_Dir when 'Desc' then desc end

View 3 Replies View Related

ORDER BY, CASE, With Multiple Columns

Nov 7, 2003

I'm unable to specify multiple columns in my order by statement if i use a case statement.
Does anyone know why this is, or what syntax would make this work?

Thanks


SELECT ....
ORDER BY (CASE Lower(@SortExpression)
WHEN 'prodname' THEN prodname, prodprice
WHEN 'prodsize' THEN prodsize, prodname
WHEN 'prodprice' THEN prodprice, prodname
Else prodcompany, prodname
END)

View 5 Replies View Related

Sort Order/case-sensitivity

Aug 30, 2000

I have a case-insensitive sort order on my SQL7, NT4, SP1,2 installation. However, there are times when I want to do case-sensitive search ciriteria.

Is there something like the pseudo code:
SET SORT ORDER CASE-SENSITIVE ON
SELECT * FROM table WHERE name = 'Black'
SET SORT ORDER CASE-SENSITIVE OFF

..that doesn't return 'BLACK'

thanks in advance for your time
BL

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved