case st err - SQL Server Forums

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

case st err

Author

Topic

under2811
Constraint Violating Yak Guru

366 Posts

Posted - 2010-04-05 : 07:26:40

SELECT count(*)
FROM
TBL1 R (NOLOCK) INNER JOIN
TBL2T (NOLOCK)
ON R.KEY_T = T.KEY_T AND
R.Key_S = T.Key_S
WHERE
R.SALID = 0 AND
CASE R.CHKID
when 0 then T.DATE BETWEEN R.FRDATE AND R.TODATE
when 1 then T.DATE BETWEEN R.ADFRDATE AND R.ADENDDATE

end

GETTING ERR
Msg 156, Level 15, State 1, Line 13
Incorrect syntax near the keyword 'BETWEEN'.

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-04-05 : 07:38:26

Not sure whether this is what you are looking for. Give a try and check the output.

SELECT count(*)
FROM
TBL1 R (NOLOCK) INNER JOIN
TBL2T (NOLOCK)
ON R.KEY_T = T.KEY_T AND
R.Key_S = T.Key_S
WHERE
R.SALID = 0 AND
(( R.CHKID =0 and T.DATE BETWEEN R.FRDATE AND R.TODATE)
OR
( R.CHKID = 1 AND T.DATE BETWEEN R.ADFRDATE AND R.ADENDDATE )
)

Regards,
Bohra.

I am here to learn from masters and help new bees in learning.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-04-05 : 07:39:33

you can't use CASE WHEN this way.

CASE WHEN should return a value not a statement.


WHERE R.SALID = 0
AND   (
          (R.CHKID = 0 AND T.DATE BETWEEN R.FRDATE AND R.TODATE)
      OR  (R.CHKID = 1 AND T.DATE BETWEEN R.ADFRDATE AND R.ADENDDATE)
      )

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-04-05 : 07:39:56

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources