Home » SQL & PL/SQL » SQL & PL/SQL » Need quick help with a Max Query
Need quick help with a Max Query [message #218221] Wed, 07 February 2007 06:29 Go to next message
kipp@plancknet.com
Messages: 25
Registered: September 2006
Junior Member
Hello... I have a select statment with joining several tables. The query has worked like it is but now we are needing to add the following condiotion:
original:
FROM tablea RQN,
address_table DOD,
LOOKUP_table LK
WHERE SUBSTR(RQN.zzdoc_NBR,1,6)
NOT IN (
SELECT ods.aac
FROM ODS_EX_LOOKUP ods
WHERE ods.aac=substr(RQN.doc_NBR,1,6))
AND RQN.id = LK.id
AND RQN.newdob = to_date(sysdate-1,'DD-MON-YY')
AND SUBSTR(RQN.zzdoc_NBR,1,6) = DOD.aac (+));
COMMIT;

I need to take the above query and have the following condition,
If the MAX dob is not equal to the DOB, then we need to look for records with system date minus 2.
If the MAX dob is equal to the DOB then use system date minus 1.

Thanks in Advance.

[Updated on: Wed, 07 February 2007 06:33]

Report message to a moderator

Re: Need quick help with a Max Query [message #218239 is a reply to message #218221] Wed, 07 February 2007 07:58 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
kipp@plancknet.com wrote on Wed, 07 February 2007 07:29

AND RQN.newdob = to_date(sysdate-1,'DD-MON-YY')



SYSDATE is already a date and using a to_date function on it will lead to unpredictable results.
Additionally, SYSDATE has a time componennt associated with it, so trying to compare a date to a date will in all likelihood fail unless your date column matches to the second.
Re: Need quick help with a Max Query [message #218255 is a reply to message #218239] Wed, 07 February 2007 08:33 Go to previous message
kipp@plancknet.com
Messages: 25
Registered: September 2006
Junior Member
Thanks...
So does that get the MAX dob sysdate -1, but how do I get the second condition if it is not the first
1. MAX dob is equal to the DOB then use system date minus 1.
2. MAX dob is not equal to the DOB, then we need to look for records with system date minus 2.

sorry for the ignorance...
Previous Topic: Replacing variable string with a fixed part
Next Topic: exist in parent not in child
Goto Forum:
  


Current Time: Sat Dec 03 01:06:59 CST 2016

Total time taken to generate the page: 0.11900 seconds