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.
| Author |
Topic |
|
DeveloperIQ
Yak Posting Veteran
71 Posts |
 Posted - 2008-10-30 : 13:42:02
|
| What is wrong with the statementCASE WHEN (Select COUNT(1) FROM dbo.udft_ReturnCharIDs(@cpt)) > 0 THEN TM.TM_CPT_CODE IN (Select TheID FROM dbo.udft_ReturnCharIDs(@cpt)) ENDI have this in the where clause and keep getting the following error while compiling my SPMsg 156, Level 15, State 1, Procedure XXXX, Line 92Incorrect syntax near the keyword 'IN'.Msg 156, Level 15, State 1, Procedure XXXX, Line 94Incorrect syntax near the keyword 'END'.@cpt is of type varchar(8000) |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-10-30 : 13:45:32
|
you dont need the case statement. what you need is this...INNER JOIN (Select TheID FROM dbo.udft_ReturnCharIDs(@cpt)) tmpON tmp.TheID=TM.TM_CPT_CODE |
 |
|
|
DeveloperIQ
Yak Posting Veteran
71 Posts |
Posted - 2008-10-30 : 13:48:15
|
| I do Visakh.. The reason is that the subquery wont always return data in which case I would dont it to not filter at all. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-10-30 : 13:49:56
|
| then use left join LEFT JOIN (Select TheID FROM dbo.udft_ReturnCharIDs(@cpt)) tmpON tmp.TheID=TM.TM_CPT_CODE |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-10-30 : 13:50:35
|
| post your full query if you need full solution. |
|
|
|
DeveloperIQ
Yak Posting Veteran
71 Posts |
Posted - 2008-10-30 : 13:51:35
|
quote:
Originally posted by visakh16
then use left join LEFT JOIN (Select TheID FROM dbo.udft_ReturnCharIDs(@cpt)) tmpON tmp.TheID=TM.TM_CPT_CODE
Ok This makes more sense. Dumb me. But just out of curiousity, do you see a problem with the above case statement? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-10-30 : 13:54:15
|
| yup. thats not a place where you use case. you cant use case to return you different filter conditions. case is normally use to conditionally assign value to a column. |
|
|
|
DeveloperIQ
Yak Posting Veteran
71 Posts |
Posted - 2008-10-30 : 13:55:11
|
| Thanks Visakh! |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-10-30 : 14:01:14
|
welcome  |
|
|
|
|
|
|
Case Statement Error