Home » SQL & PL/SQL » SQL & PL/SQL » case in where ... what i m doing wrong??
case in where ... what i m doing wrong?? [message #262730] Tue, 28 August 2007 02:48 Go to next message
annu-agi
Messages: 203
Registered: July 2005
Location: Karachi
Senior Member

hi experts

i m using following sql code Sql in report and also i want to use is sql plus but i dont know how to use and describe variables in sql plus.

select a.com_Cd,a.item_Cd||' '||c.item_nm Item_Desc ,c.item_unit,to_char(b.trans_date,'DD') Day ,a.item_qty from inv_srs_dtl a, inv_srs_mst b, item_info c
where
a.com_Cd=b.com_Cd and
a.trans_Cd=b.trans_Cd and
a.com_Cd=c.com_cd and
a.item_Cd=c.item_Cd and
b.trans_date between ( case 	when :p_val=1 then '01-aug-2007' and '15-aug-2007'  when :p_val=2 then '15-aug-2007' and '31-aug-2007' end )


:p_Val is a user parameter in reports

but it is not working error is missing key words and tell me also how is this sql work on sql plus ?.. how do i discribe variable in sql plus working on line ..

regards
anwer
Re: case in where ... what i m doing wrong?? [message #262734 is a reply to message #262730] Tue, 28 August 2007 03:12 Go to previous messageGo to next message
muzahid
Messages: 281
Registered: September 2004
Location: Dhaka, Bangladesh
Senior Member
case
when
end
return one value

Use the following query

select a.com_Cd,a.item_Cd||' '||c.item_nm Item_Desc ,c.item_unit,to_char(b.trans_date,'DD') Day ,
a.item_qty
from inv_srs_dtl a, inv_srs_mst b, item_info c
where
a.com_Cd=b.com_Cd
and a.trans_Cd=b.trans_Cd
and a.com_Cd=c.com_cd
and a.item_Cd=c.item_Cd
and b.trans_date between (
case
when :p_val=1 then '01-aug-2007'
when :p_val=2 then '15-aug-2007'
end
)
and
(
case
when :p_val=1 then '15-aug-2007'
when :p_val=2 then '31-aug-2007'
end
)
Re: case in where ... what i m doing wrong?? [message #262736 is a reply to message #262730] Tue, 28 August 2007 03:14 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
You can declare variables in sql*plus.
Look at this also.

By
Vamsi

[Updated on: Tue, 28 August 2007 03:21]

Report message to a moderator

Re: case in where ... what i m doing wrong?? [message #262777 is a reply to message #262730] Tue, 28 August 2007 04:58 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
annu-agi wrote on Tue, 28 August 2007 09:48
b.trans_date between ( case 	when :p_val=1 then '01-aug-2007' and '15-aug-2007'  when :p_val=2 then '15-aug-2007' and '31-aug-2007' end )




compare dates to dates. Not to strings.
Previous Topic: Partition Query
Next Topic: Sql query
Goto Forum:
  


Current Time: Sat Dec 10 16:25:42 CST 2016

Total time taken to generate the page: 0.12492 seconds