Home » SQL & PL/SQL » SQL & PL/SQL » if in where or conditional where
if in where or conditional where [message #261909] Fri, 24 August 2007 01:01 Go to next message
annu-agi
Messages: 199
Registered: July 2005
Location: Karachi
Senior Member

hi experts

first see the select statement
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 '01-aug-2007' and '15-aug-2007'



Now i want to add one more variable in this select we lets suppose p_val . If p_Val = 1 then trans_date between 01-15 or if p_val =2 then trans_date should be between 15 to 30 ..
how it is possible .. can any one give me idea .. what is the best solution .. and whats the solution to avaoid this type of conditional where clause.



thanks

Anwer



**note**
This select is using in matrix report based on 1 to 15 day-wise item activity .
Re: if in where or conditional where [message #261911 is a reply to message #261909] Fri, 24 August 2007 01:07 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Something like:
b.trans_date between to_date('01-aug-2007','dd-mon-yyyy')+15*(p_val-1)
                 and to_date('01-aug-2007','dd-mon-yyyy')+15*p_val

Regards
Michel
Re: if in where or conditional where [message #261929 is a reply to message #261911] Fri, 24 August 2007 01:41 Go to previous messageGo to next message
annu-agi
Messages: 199
Registered: July 2005
Location: Karachi
Senior Member

thanks for replay ..
but its not meet the criteria .. actually you know in reports parameter forms if user select first quarter( P_VAL ) then report should be generate on 1 to 15 days and if user select second quarter then report should be run form 15th day to 30th. that is what i want .. any suggestions
Re: if in where or conditional where [message #261931 is a reply to message #261929] Fri, 24 August 2007 01:46 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Not so different:
b.trans_date between decode(p_val,
                            1,to_date('01-aug-2007','dd-mon-yyyy'),
                              to_date('16-aug-2007','dd-mon-yyyy'))
                 and decode(p_val,
                            1,to_date('15-aug-2007','dd-mon-yyyy'),
                              last_day(to_date('aug-2007','mon-yyyy'))

Just an example.

Regards
Michel

[Updated on: Fri, 24 August 2007 01:47]

Report message to a moderator

Re: if in where or conditional where [message #262080 is a reply to message #261909] Fri, 24 August 2007 08:00 Go to previous message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
annu-agi wrote on Fri, 24 August 2007 02:01

b.trans_date between '01-aug-2007' and '15-aug-2007'
[/code]



Learn to code properly. You cannot compare a DATE column to a character string.
Previous Topic: drop all views and sequences form a schema
Next Topic: Grouping DATA
Goto Forum:
  


Current Time: Sat Dec 03 08:20:56 CST 2016

Total time taken to generate the page: 0.15890 seconds