Case qry - 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

SQL Server 2000 Forums

SQL Server Development (2000)

Case qry

Author

Topic

niranjankumark
Posting Yak Master

164 Posts

Posted - 2008-09-25 : 02:20:24

Check the syntax of case in where clause ...

Need to add @input_days based on case condition , it shud be out side the case ???

How to split days from date ... dd/mm/yyy need days alone and add to @input_days.

(
AND MTTR_STS.MTTR_STS_CD = "open"
AND CASE MTTR.MTTR_TYP_CD
WHEN "litigation"
THEN MTTR_STS.MTTR_STS_DT + @input_days
ELSE REQ_REF.REF_DT + @input_days
END
)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-25 : 02:34:01

may be this (not clear from query posted which column you want to compare to)

AND MTTR_STS.MTTR_STS_CD = "open"
AND <comparingcolumn> =CASE MTTR.MTTR_TYP_CD 
WHEN "litigation" 
THEN MTTR_STS.MTTR_STS_DT + @input_days
ELSE REQ_REF.REF_DT + @input_days 
END

niranjankumark
Posting Yak Master

164 Posts

Posted - 2008-09-25 : 02:45:28

AND MTTR_STS.MTTR_STS_CD = "open"
AND <comparingcolumn> =CASE MTTR.MTTR_TYP_CD
WHEN "litigation"
THEN MTTR_STS.MTTR_STS_DT + @input_days
ELSE REQ_REF.REF_DT + @input_days
END

i split Here comparing column shud be like

AND MTTR_STS.MTTR_STS_DT = CASE MTTR.MTTR_TYP_CD
WHEN "litigation"
THEN MTTR_STS.MTTR_STS_DT + @input_days

LIKE THIS

REQ_REF.REF_DT = CASE MTTR.MTTR_TYP_CD
WHEN "litigation"
THEN REQ_REF.REF_DT + @input_days

need to write in single case ..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-25 : 02:50:28

you cant assign two columns values using a single case. you need seperate case construct for each column.

niranjankumark
Posting Yak Master

164 Posts

Posted - 2008-09-25 : 02:57:17

ok i split like this ..One condition ONLY qill be true in my case , in second case need to include <> "litigation" ...
below is ok ??

(
AND MTTR_STS.MTTR_STS_DT = CASE MTTR.MTTR_TYP_CD
WHEN "litigation"
THEN MTTR_STS.MTTR_STS_DT + @input_days
END
OR
REQ_REF.REF_DT = CASE MTTR.MTTR_TYP_CD
WHEN <> "litigation"
THEN REQ_REF.REF_DT + @input_days
END
)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-25 : 03:08:37

If you use ASCII 34 around a string, it means SQL Server should treat the name as a column, not as a value.

Question already asked and answered here
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=111262

E 12°55'05.63"
N 56°04'39.26"

niranjankumark
Posting Yak Master

164 Posts

Posted - 2008-09-25 : 03:36:25

ss but need to add sepeartely ...
This in in the link :
where case Mat.Mattype when 'litigation' then Mat.Mat_statusdate else @refdate end > '20080901'

shall it be add like this ..

where case Mat.Mattype when 'litigation' then Mat.Mat_statusdate else @refdate end + 10

Mat.Mat_statusdate - split the days alone add it with 10 .

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-25 : 04:16:24

quote:
Originally posted by niranjankumark

ss but need to add sepeartely ...
This in in the link :
where case Mat.Mattype when 'litigation' then Mat.Mat_statusdate else @refdate end > '20080901'

shall it be add like this ..

where case Mat.Mattype when 'litigation' then Mat.Mat_statusdate else @refdate end + 10

Mat.Mat_statusdate - split the days alone add it with 10 .

where's the right side? you have just specified expression which giving equating part.

niranjankumark
Posting Yak Master

164 Posts

Posted - 2008-09-25 : 04:45:09

my requirement is

case Mat.Mattype when 'litigation'

Mat.Mat_statusdate + 10 ( add 10 days to this column by taking the days)

else add 10 days with ref days like

REQ_REF.REF_DT + 10 ....

cud u convert now ???

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-25 : 05:05:10

quote:
Originally posted by niranjankumark

my requirement is

case Mat.Mattype when 'litigation'

Mat.Mat_statusdate + 10 ( add 10 days to this column by taking the days)

else add 10 days with ref days like

REQ_REF.REF_DT + 10 ....

cud u convert now ???

You cant update columns from two different tables in a single statement. you need two seperate updates.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-25 : 05:06:15

Or you need a make a view out of two tables and do update by means of an INSTEAD OF TRIGGER.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources