WHERE xx IN ( CASE WHEN ... not allowed ?? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

WHERE xx IN ( CASE WHEN ... not allowed ??

Author

Topic

remyo
Starting Member

6 Posts

Posted - 2010-08-12 : 10:49:17

Why doesnt the following piece of code work
I come from oracle and i think it works there (though i cannot test it atm. Based on given input i need to execute a query which returns results on its own hierarchy level.

If this isnt allowed how can i program around it ?

WHERE kstplcode In
( Case WHEN field1 = 2 THEN
Select t4.kstplcode
From table3 t3, table2 t2, table1 t4
Where t4.Class_03 = t3.CostcenterClassCode
And t3.Class_02 = t2.CostcenterClassCode
And t2.CostcenterClassCode
= 'bbb'
ELSE
Select t4.kstplcode
From table3 t3, table2 t2, table1 t4
Where t4.Class_03 = t3.CostcenterClassCode
And t3.Class_02 = t2.CostcenterClassCode
And t3.CostcenterClassCode
= 'aaa'
END
)

Thanks in advance

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-08-12 : 10:51:00

Can you please show us the whole query.

remyo
Starting Member

6 Posts

Posted - 2010-08-12 : 10:56:03

k my whole query looks like this. Sorry about the formatting. The code is generated from a tool.
The errors are

Msg 156, Level 15, State 1, Line 16
Incorrect syntax near the keyword 'Select'.
Msg 156, Level 15, State 1, Line 21
Incorrect syntax near the keyword 'ELSE'.
Msg 102, Level 15, State 1, Line 27
Incorrect syntax near ')'.

Select Round(Sum(a.bedrag), 0) As Bedrag, Case
When LEFT(LTRIM(a.kostensoort), 1) = '8' Then LEFT(LTRIM(a.kostensoort), 1)
Else LEFT(LTRIM(a.kostensoort), 2)
End As kostensoort, LEFT(Convert(VarChar(8),a.Datum,112), 6) As maand
From (Select Case When exact_ind_gbkmut.reknr = '419101' Then '429101'
When exact_ind_gbkmut.reknr = '419991' Then '429991'
Else exact_ind_gbkmut.reknr
End As kostensoort, exact_ind_gbkmut.bdr_val As bedrag,
exact_ind_gbkmut.Datum As datum
From exact_ind_gbkmut
Where (LEFT(LTRIM(exact_ind_gbkmut.reknr), 1) = '8' Or
LEFT(LTRIM(exact_ind_gbkmut.reknr), 2) In ('41', '42', '43', '44', '45',
'46', '47', '48', '49')) And LEN(LTRIM(exact_ind_gbkmut.reknr)) = 6 And
exact_ind_gbkmut.kstplcode In
( Case WHEN '' = '2' THEN
Select t4.kstplcode
From exact_all_kp_class3 t3, exact_all_kp_class2 t2, exact_all_kstpl t4
Where t4.Class_03 = t3.CostcenterClassCode
And t3.Class_02 = t2.CostcenterClassCode
And t2.CostcenterClassCode = ''
ELSE
Select t4.kstplcode
From exact_all_kp_class3 t3, exact_all_kp_class2 t2, exact_all_kstpl t4
Where t4.Class_03 = t3.CostcenterClassCode
And t3.Class_02 = t2.CostcenterClassCode
And t3.CostcenterClassCode = '' END
)
And LTRIM(exact_ind_gbkmut.storno) = 0 And
exact_ind_gbkmut.oms25 Is Not Null And exact_ind_gbkmut.bkjrcode =
Left('20100531', 4) And MONTH(exact_ind_gbkmut.Datum) <=
Substring('20100531', 5, 2)) As a
Group By Case
When LEFT(LTRIM(a.kostensoort), 1) = '8' Then LEFT(LTRIM(a.kostensoort), 1)
Else LEFT(LTRIM(a.kostensoort), 2)
End, LEFT(Convert(VarChar(8),a.datum,112), 6)

X002548
Not Just a Number

15586 Posts

Posted - 2010-08-12 : 11:09:39

I thought it was because you didn't have code tags

Format the code..you will probably find your own mistakes

If not, post the formatted code with code tags..i'm tired of formatting other peoples code right now...

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-12 : 11:15:17

i cant understand the purpose of CASE WHEN at all..you've given a condition WHEN '' = '2' which is always false. then whats purpose of CASE WHEN?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Kokkula
Starting Member

41 Posts

Posted - 2010-08-13 : 01:30:51

Hello,

Try this

Select t4.kstplcode
From exact_all_kp_class3 t3, exact_all_kp_class2 t2, exact_all_kstpl t4
Where t4.Class_03 = t3.CostcenterClassCode
And t3.Class_02 = t2.CostcenterClassCode
And ((t3.CostcenterClassCode = '' AND '' <> '2')
OR (t2.CostcenterClassCode = '' AND '' = '2'))
-- ('' <> '2') condition in the case when // place the correct condition instead of that

The conditionin the where clause will be like below.

And exact_ind_gbkmut.kstplcode In (
Select t4.kstplcode
From exact_all_kp_class3 t3, exact_all_kp_class2 t2, exact_all_kstpl t4
Where t4.Class_03 = t3.CostcenterClassCode
And t3.Class_02 = t2.CostcenterClassCode
And ((t3.CostcenterClassCode = '' AND '' <> '2')
OR (t2.CostcenterClassCode = '' AND '' = '2'))
)

Hope its clear & helpful....

Pavan
Infosys Technologies Limited

remyo
Starting Member

6 Posts

Posted - 2010-08-13 : 03:25:02

Thanks Kokkula,

Your tip was great. It gave me enough tips.
I got my piece of code up and running now.

For those who wondered why '' = '2' was in the code.
This was generated code. I parse variables in the orginal code and variables can be NULL.
Based on user input it will say '2' = '2' or '' = '2'.
Why did i use a 2 and not a 1.
Its an hierarchical level. Made more sense to the functional design to use a 2 here.

Regards

Remy

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources