case statement in where clause

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 statement in where clause

Author

Topic

sent_sara
Constraint Violating Yak Guru

377 Posts

Posted - 2008-12-24 : 01:07:30

Dear Friends,
Can any one help me in correcting the query .while iam executing the below query "Incorrect syntax near the keyword 'between'.".

Thanks in Advance

Declare @status char(1)
set @status='T'
select * from fin_ods..rpt_receipt_hdr(nolock)
where receipt_status='D'
and
case when @status='T' then
receipt_date between '01 apr 2008' and '30 apr 2008' and fb_id='crmpfb'
else
receipt_date between '01 apr 2008' and '15 apr 2008' and fb_id='crmpfb'
end

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2008-12-24 : 01:17:56

Try Like this
Declare @status char(1)
set @status='T'
select * from fin_ods..rpt_receipt_hdr(nolock)
where receipt_status='D'
and
receipt_date between '01 apr 2008' and
case when @status='T' then
'30 apr 2008'
else
'15 apr 2008'
end
and fb_id='crmpfb'

sent_sara
Constraint Violating Yak Guru

377 Posts

Posted - 2008-12-24 : 02:04:34

Thanks mr.Bklr for immediate reply

Actually based on my input (ie)
when status='T' then I need to take receipt_amount between from_date and to_date
when status='S' then i need to take receipt_amount between from_date
and to_date and and day(receipt_date) between 1 and 20

when status='E' then i need to take receipt_amount between from_date
and to_date and and day(receipt_date)>20

quote:
Originally posted by bklr

Try Like this
Declare @status char(1)
set @status='T'
select * from fin_ods..rpt_receipt_hdr(nolock)
where receipt_status='D'
and
receipt_date between '01 apr 2008' and
case when @status='T' then
'30 apr 2008'
else
'15 apr 2008'
end
and fb_id='crmpfb'

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-12-24 : 02:11:05

quote:
Originally posted by sent_sara

Dear Friends,
Can any one help me in correcting the query .while iam executing the below query "Incorrect syntax near the keyword 'between'.".

Thanks in Advance

Declare @status char(1)
set @status='T'
select * from fin_ods..rpt_receipt_hdr(nolock)
where receipt_status='D'
and
case when @status='T' then
receipt_date between '01 apr 2008' and '30 apr 2008' and fb_id='crmpfb'
else
receipt_date between '01 apr 2008' and '15 apr 2008' and fb_id='crmpfb'
end

Use the logic like this

Declare @status char(1)
set @status='T'
select * from fin_ods..rpt_receipt_hdr(nolock)
where receipt_status='D'
and
(
(@status='T' and receipt_date between '01 apr 2008' and '30 apr 2008' and fb_id='crmpfb')
or
(receipt_date between '01 apr 2008' and '15 apr 2008' and fb_id='crmpfb')
)

Madhivanan

Failing to plan is Planning to fail

sent_sara
Constraint Violating Yak Guru

377 Posts

Posted - 2008-12-24 : 04:21:40

Txs Madhivanan ,now its working fine and txs to Bklr

quote:
Originally posted by madhivanan

quote:
Originally posted by sent_sara

Dear Friends,
Can any one help me in correcting the query .while iam executing the below query "Incorrect syntax near the keyword 'between'.".

Thanks in Advance

Declare @status char(1)
set @status='T'
select * from fin_ods..rpt_receipt_hdr(nolock)
where receipt_status='D'
and
case when @status='T' then
receipt_date between '01 apr 2008' and '30 apr 2008' and fb_id='crmpfb'
else
receipt_date between '01 apr 2008' and '15 apr 2008' and fb_id='crmpfb'
end

Use the logic like this

Declare @status char(1)
set @status='T'
select * from fin_ods..rpt_receipt_hdr(nolock)
where receipt_status='D'
and
(
(@status='T' and receipt_date between '01 apr 2008' and '30 apr 2008' and fb_id='crmpfb')
or
(receipt_date between '01 apr 2008' and '15 apr 2008' and fb_id='crmpfb')
)

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-12-24 : 07:26:45

You are welcome

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources