Problem With Case Function

Hi! Guys
i have problem with order by clause.
i have table some T1 with two columns say Itemname and Status
and data like this
ItemName Status
T1 G
T2 D
T3 US
T4 S
T5 G
T6 NULL
T7 NULL
Now i want to show with status like
G
D
S
US
SELECT
CASE Status
WHEN 'G' THEN 0
WHEN 'NULL' THEN 0
WHEN 'D' THEN 1
WHEN 'S' THEN 2
WHEN 'US' THEN 3 END AS STATUSORDER
FROM T1
But it is not ececuting properly.it is not exact answer what i want.
plz , give solutionHi, shiva
You can use one of these queries:
SELECT
CASE
WHEN Status='G' THEN 0
WHEN Status IS NULL THEN 0
WHEN Status='D' THEN 1
WHEN Status='S' THEN 2
WHEN Status='US' THEN 3
END AS STATUSORDER
FROM T1
or:
SELECT
CASE Status
WHEN 'D' THEN 1
WHEN 'S' THEN 2
WHEN 'US' THEN 3
ELSE 0
END AS STATUSORDER
FROM T1
Razvan|||What does it mean "not properly" ?
CREATE TABLE #Test
(
col VARCHAR(2) NULL
)
INSERT INTO #Test VALUES ('G')
INSERT INTO #Test VALUES ('D')
INSERT INTO #Test VALUES ('US')
INSERT INTO #Test VALUES ('S')
INSERT INTO #Test VALUES ('G')
INSERT INTO #Test VALUES (null)
INSERT INTO #Test VALUES (null)
SELECT col FROM #TesT WHERE col IS NOT NULL
GROUP BY col
ORDER BY
CASE col
WHEN 'G' THEN 0
WHEN 'D' THEN 1
WHEN 'S' THEN 2
WHEN 'US' THEN 3 END
"shiva" <bany.shanker@.gmail.com> wrote in message
news:1133867516.797293.72880@.f14g2000cwb.googlegroups.com...
> Hi! Guys
>
> i have problem with order by clause.
>
> i have table some T1 with two columns say Itemname and Status
>
> and data like this
>
> ItemName Status
> T1 G
> T2 D
> T3 US
> T4 S
> T5 G
> T6 NULL
> T7 NULL
> Now i want to show with status like
> G
> D
> S
> US
> SELECT
> CASE Status
> WHEN 'G' THEN 0
> WHEN 'NULL' THEN 0
> WHEN 'D' THEN 1
> WHEN 'S' THEN 2
> WHEN 'US' THEN 3 END AS STATUSORDER
> FROM T1
> But it is not ececuting properly.it is not exact answer what i want.
> plz , give solution
>|||thanks Razvan,
it's working fine.
thanks a lot.

Problem with a where clause

I'd like to set the where clause of my query from a field in a table
ex:
Select TableX.field1
from tableX, TableY
where TableY.field2
Field2 is equal to "fieldW like '%blabla%' "
Thanks for your helpDynamic SQL:
DECLARE @.sqlStmt VARCHAR(2000)
DECLARE @.whereClause VARCHAR(500)
SELECT @.whereClause = field2
FROM tableY
WHERE {tableY.key_column} = {value}
SELECT @.sqlStmt = 'SELECT tableX.field1 FROM tableX, tableY WHERE '
SELECT @.sqlStmt = @.sqlStmt + @.whereClause
EXEC (@.sqlStmt)
Be careful about SQL Injection using this method.
"Epervier" <Epervier@.discussions.microsoft.com> wrote in message
news:D8F4A0A0-37CE-4FA5-B9CC-771BCE61B5FF@.microsoft.com...
> I'd like to set the where clause of my query from a field in a table
> ex:
> Select TableX.field1
> from tableX, TableY
> where TableY.field2
> Field2 is equal to "fieldW like '%blabla%' "
> Thanks for your help

Problem Using if condition in select stored procesure

Hi!
how can i use if condition in where clause of select statement
I want the work like this
but this is not working correct
select * from Table1
where
if (@.a>1)
field1=@.a
else
field2=@.a
This is not a recommended usage since the optimizer will probably not
generate a good plan. It would be better to use a IF statement and two
select statements. In addition, I find the logic more than a bit strange.
In any event, below is an example
use Northwind
go
--select * from invoices
declare @.a int
/*set @.a = 14
select * from invoices
where ProductID = @.a
set @.a = 10249
select * from invoices
where OrderID = @.a*/
set @.a = 10249
set @.a = 14
select * from invoices
where case when @.a > 1000 then OrderID else @.a end = @.a
and case when @.a <= 1000 then ProductID else @.a end = @.a
"microsoft" <shahzad@.procuresoft.com> wrote in message
news:enf58S05EHA.2964@.TK2MSFTNGP09.phx.gbl...
> Hi!
> how can i use if condition in where clause of select statement
> I want the work like this
> but this is not working correct
> select * from Table1
> where
> if (@.a>1)
> field1=@.a
> else
> field2=@.a
>
|||select * from Table1 where @.a>1 and field1=@.a
Union
select * from Table1 where @.a<1 and field2=@.a
"microsoft" wrote:

> Hi!
> how can i use if condition in where clause of select statement
> I want the work like this
> but this is not working correct
> select * from Table1
> where
> if (@.a>1)
> field1=@.a
> else
> field2=@.a
>
>
|||where
(@.a > 1 and field1 = @.a) or (@.a <= 1 and field2 = @.a)
or (probably less efficient)
where
@.a = case when @.a > 1 then field1 else field2 end
If you have a non-ansi setting for null comparisons,
where
(@.a > 1 and field1 = @.a) or ((@.a <= 1 or @.a is null) and field2 = @.a)
Steve Kass
Drew University
microsoft wrote:

>Hi!
>how can i use if condition in where clause of select statement
>I want the work like this
>but this is not working correct
>select * from Table1
> where
> if (@.a>1)
> field1=@.a
> else
> field2=@.a
>
>