Home » SQL & PL/SQL » SQL & PL/SQL » need help in Query
need help in Query [message #219787] Fri, 16 February 2007 01:59 Go to next message
annu-agi1
Messages: 17
Registered: August 2006
Location: Pakistan
Junior Member

hi experts

data is like

code name sal dt_from dt_to
1021 ali 5000 1-1-2005 31-12-2005
10245 asif 5000 1-3-2005 31-12-2005
4567 aqeel 4500 10-10-2005 ----------
1021 ali 6500 1-1-2006 30-06-2006
1021 ali 7200 1-6-2006 31-12-2007
1021 ali 8000 1-1-2007 ----------
10245 asif 6000 1-1-2006 ----------

note "----------" is a null value and stands for TILL TO DATE


question is


what would we write a query if we need data between 01-04-2005 and 30-10-2005 dates.


regards

anwer



Re: need help in Query [message #219791 is a reply to message #219787] Fri, 16 February 2007 02:30 Go to previous messageGo to next message
Littlefoot
Messages: 20897
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
... WHERE date_column BETWEEN TO_DATE('01.04.2005', 'dd.mm.yyyy')
                          AND TO_DATE('30.10.2005', 'dd.mm.yyyy')
Re: need help in Query [message #219806 is a reply to message #219791] Fri, 16 February 2007 03:35 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
The standard algorithm for determining overlap is:

where date_from <= to_date('30-10-2005', 'dd-mm-yyyy')
and   nvl(date_to, to_date('01-04-2005', 'dd-mm-yyyy') >= to_date('01-04-2005', 'dd-mm-yyyy')


Since you did not say what exactly you want (partial overlap will do or period has to fall entirely within your testperiod) I can't tell you if this is exactly what you need.
Re: need help in Query [message #219857 is a reply to message #219787] Fri, 16 February 2007 06:39 Go to previous messageGo to next message
annu-agi1
Messages: 17
Registered: August 2006
Location: Pakistan
Junior Member

sorry ...........in a hurry i write here date format like '01-01-2005' but date is in orginale date format like '1-jan-2005'. and as seen your query i make one from my self but cant work .. lets check ..

select * from empl_breakup_info
where
dt_from >= &P_DT_FROM AND
DT_TO <= nvl(&p_dt_to,sysdate);

Enter value for p_dt_from: '01-jan-2007'
old 3: dt_from >= &P_DT_FROM AND
new 3: dt_from >= '01-jan-2007' AND
Enter value for p_dt_to: '31-dec-2007'
old 4: DT_TO <= nvl(&p_dt_to,sysdate)
new 4: DT_TO <= nvl('31-dec-2007',sysdate)

no rows selected



any suggetions .. pleas
Re: need help in Query [message #219872 is a reply to message #219857] Fri, 16 February 2007 08:37 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
annu-agi1 wrote on Fri, 16 February 2007 13:39
sorry ...........in a hurry [snip]
any suggetions .. pleas

Come back when you have more time...
Re: need help in Query [message #219878 is a reply to message #219787] Fri, 16 February 2007 08:55 Go to previous messageGo to next message
rigatonip
Messages: 50
Registered: December 2005
Member
Check your data. It could be that you don't have any "employee breakups" in 2007. The query ran. It just didn't return any results. Given your query inputs, both the dt_from and dt_to must be between 1-jan-2007 and 31-dec-2007. None of your sample data meets the criteria and it doesn't look like any of the data in your actual table does either. Test this by inspecting the data and then changing the date range to see if you get the results you expect.
Re: need help in Query [message #219881 is a reply to message #219857] Fri, 16 February 2007 09:11 Go to previous messageGo to next message
joy_division
Messages: 4641
Registered: February 2005
Location: East Coast USA
Senior Member
annu-agi1 wrote on Fri, 16 February 2007 07:39


select * from empl_breakup_info
where
dt_from >= &P_DT_FROM AND
DT_TO <= nvl(&p_dt_to,sysdate);

Enter value for p_dt_from: '01-jan-2007'
old 3: dt_from >= &P_DT_FROM AND
new 3: dt_from >= '01-jan-2007' AND
Enter value for p_dt_to: '31-dec-2007'
old 4: DT_TO <= nvl(&p_dt_to,sysdate)
new 4: DT_TO <= nvl('31-dec-2007',sysdate)

no rows selected



any suggetions .. pleas


Probably because you are comparing DATE columns to character strings.
Re: need help in Query [message #219908 is a reply to message #219881] Fri, 16 February 2007 11:37 Go to previous message
Ronald Beck
Messages: 121
Registered: February 2003
Senior Member
STOP COMPARING TEXT STRINGS TO DATE TYPES!

When you enter a date in your procedure, you're entering a character string. You MUST use the to_date function to translate the character string you entered into a DATE format that Oracle can understand.

See what happens if you change your query to the following:

select * from empl_breakup_info 
where 
dt_from >= to_date(&P_DT_FROM,'DD-mon-YYYY') AND
DT_TO <= nvl(to_date(&p_dt_to,'DD-mon-YYYY'),sysdate);


If you don't understand this, go back and read some of the previous messages on DATE types and converting strings to DATE format that Oracle understands. There are many examples in previous postings.

Ron
Previous Topic: How to Display as well as Insert in a single sql statement
Next Topic: Help required in converting query output.
Goto Forum:
  


Current Time: Wed Dec 07 06:41:09 CST 2016

Total time taken to generate the page: 0.85138 seconds