Subquery select case

C Coudou

mates, need help. i'm stuck on sql query.

SELECT * FROM TABLE1
WHERE MYFLG =0 
AND MYFLG =(SELECT CASE WHEN COLUMN1 = 1 AND COLUMN2 = 0 THEN 1 ELSE 0 END AS MYFLG FROM TABLE1)

got an error: Invalid column name 'MYFLG'. I know the error because it is an alias name in case statement which is not totally exist in table1 but how will i correct this one. My aim is only select those MYFLG = 0 in my case statement.

C# コードMicrosoft End User 2000-2008 「「「「「「「「「「「「「「「「「「「「「「「「「「「「 The best things in life are free 」」」」」」」」」」」」」」」」」」」」」」」」」」」」

Karthik A

I have not tried running my suggestions - 1) Try w/ brackets as below

SELECT * FROM TABLE1
WHERE MYFLG =0
AND MYFLG =(SELECT (CASE WHEN COLUMN1 = 1 AND COLUMN2 = 0 THEN 1 ELSE 0 END) AS MYFLG FROM TABLE1)

What are you trying to accomplish w/ this query? Seeing your query, I guess you could simplify it as

SELECT * FROM TABLE1 WHERE (MYFLG = 0 OR (COLUMN1 = 1 AND COLUMN2 = 0))

MYFLG = 0 AND MYFLG = ... doesn't make sense. How could MYFLG could be both 0 and 1? It should be an "or" I guess I may be wrong, in that case, try to explain your question in detail...

Cheers, Karthik

Alegria_Lee

I guess "MYFLG" is not a column of table1. what result he wants to get is that rows "

not in where COLUMN1 = 1 AND COLUMN2 = 0

" If so,Why don't use this

Select * from TABLE1 where PK not in ( Select PK From TABLE1 Where COLUMN1 = 1 AND COLUMN2 =0)

modified on Wednesday, October 13, 2010 10:37 PM

Karthik A · modified on Wednesday, October 13, 2010 10:37 PM

I don't think so, if you see the posters' query, it has

WHERE MYFLG =0

Cheers, Karthik

Alegria_Lee

but he alse got this Error infomation "got an error: Invalid column name 'MYFLG'".

C Coudou

@Karthik. A thank you for your suggestion. yes. Alegria_Lee is correct MYFLG is not a column it is an alias from my case statement. sorry for my explanation not in details. anyway. this is the actual query.

SELECT CASE WHEN SMPTABLE.RPFLG = 1 AND SMPTABLE.NDFLG= 0 THEN 
1 ELSE 0 END AS MYFLG
FROM  [TBMASTER] ,SMPTABLE,ANLTABLE,WKTABLE
WHERE (SMPTABLE.CERTCODE BETWEEN 1 AND 5 OR
SMPTABLE.CERTCODE BETWEEN 11 AND 15 or
SMPTABLE.CERTCODE = 17) AND
(SMPTABLE.DDATE IS NOT NULL) AND
(SMPTABLE.CERTNNO = 0 OR
SMPTABLE.CERTNNO IS NULL) AND ((SMPTABLE.DIVCODE = 1) OR
(SMPTABLE.DIVCODE = 5))
AND (ANLTABLE.ROWNO = 1) AND (ANLTABLE.KMKCD <> 1998)
AND [TBMASTER].SAMPLECD = SMPTABLE.SAMPLECD
AND ANLTABLE.SHINO=SMPTABLE.SHINO
AND  SMPTABLE.ORDNO = ANLTABLE.ORDNO
AND WKTABLE.ORDNO =SMPTABLE.ORDNO
AND ((WKTABLE.SECT=31) OR (WKTABLE.SECT =51))
AND ([ANLTABLE].ENVFLG = 0)

the result of MYFLG temporary columns are MYFLG 0 0 0 0 1 1 0 1 0 ; then i want to query again the result for those MYFLG = 0. that is what my approach is subquery. SELECT (SELECT). :: i will try the NOT IN statement given by Alegria_Lee.

C# コードMicrosoft End User 2000-2008 「「「「「「「「「「「「「「「「「「「「「「「「「「「「 The best things in life are free 」」」」」」」」」」」」」」」」」」」」」」」」」」」」

Blue_Boy

Here is it

WITH myTable as
(
--Put here all your query
)
select * from myTable where MYFLAG=0

For more about[

WITH

](http://www.tsqltutorials.com/with-common-table-expressions.php)

I Love T-SQL "Don't torture yourself,let the life to do it for you." If my post helps you kindly save my time by voting my post. www.aktualiteti.com

C Coudou

this is what i'm looking for. thank you so much. i tried already and it's working. great job!:thumbsup:

C# コードMicrosoft End User 2000-2008 「「「「「「「「「「「「「「「「「「「「「「「「「「「「 The best things in life are free 」」」」」」」」」」」」」」」」」」」」」」」」」」」」

Blue_Boy

You are welcome. :)

I Love T-SQL "Don't torture yourself,let the life to do it for you." If my post helps you kindly save my time by voting my post. www.aktualiteti.com