Hi, For the query: SELECT * FROM RT_RINGTONE_SCHEDULE WHERE CATEGORY_ID = 10001 AND END_DATE =(select max(END_DATE) from RT_RINGTONE_SCHEDULE) Would like to confirm that the Category_ID is not the problem(the max row has category id=10001).The problem seems to be the max().
The following queries also generate no result SELECT * FROM RT_RINGTONE_SCHEDULE WHERE AND END_DATE =(select max(END_DATE) from RT_RINGTONE_SCHEDULE WHERE CATEGORY_ID = 10001) SELECT * FROM RT_RINGTONE_SCHEDULE WHERE AND END_DATE IN(select max(END_DATE) from RT_RINGTONE_SCHEDULE WHERE CATEGORY_ID = 10001) However a result is generated for SELECT * FROM RT_RINGTONE_SCHEDULE WHERE AND END_DATE IN(select END_DATE from RT_RINGTONE_SCHEDULE WHERE CATEGORY_ID = 10001) pls help, regards, Ajit -----Original Message----- From: ajit_cus Sent: Wednesday, February 26, 2003 9:32 AM To: '[EMAIL PROTECTED]' Subject: Bug? in date comparison SAPDB-Kernel-version 7.3.30 -EXTREMELY URGENT Hi can someone explain this, SAPDB-Kernel-version 7.3.30 All Queries are executed in SQL MODE ORACLE using SQL STUDIO The TO_CHAR is just to work around Date time overflow in SQLTUDIO. select TO_CHAR(END_DATE,'MM/DD/YYYY HH:MI:SS AM') FROM RT_RINGTONE_SCHEDULE returns: 11/11/2030 12:00:00 AM 12/15/2029 12:00:00 AM 12/15/2029 12:00:00 AM 12/15/2029 12:00:00 AM 12/15/2029 12:00:00 AM 12/15/2029 12:00:00 AM 11/11/2001 12:00:00 AM 11/11/2001 12:00:00 AM 12/15/2029 12:00:00 AM 12/15/2029 12:00:00 AM 12/15/2029 12:00:00 AM 11/11/2030 12:00:00 AM 11/11/2030 12:00:00 AM 12/15/2029 12:00:00 AM 12/15/2029 12:00:00 AM 12/15/2029 12:00:00 AM 12/15/2029 12:00:00 AM 12/15/2029 12:00:00 AM 11/11/2030 12:00:00 AM 11/11/2030 12:00:00 AM 11/11/2030 12:00:00 AM select TO_CHAR(max(END_DATE),'MM/DD/YYYY HH:MI:SS AM') FROM RT_RINGTONE_SCHEDULE 11/11/2030 12:00:00 AM which so far is correct then why does SELECT * FROM RT_RINGTONE_SCHEDULE WHERE CATEGORY_ID = 10001 AND END_DATE =(select max(END_DATE) from RT_RINGTONE_SCHEDULE) return no rows? I expect it to return the row containing END_DATE=11/11/2030 12:00:00 AM This is extremely urgent,pls get back to me ASAP, Regards, Ajit P.S: Just in case u need it this is the table DDL: CREATE TABLE RT_RINGTONE_SCHEDULE ( LOCATION_ID varchar2(10), CATEGORY_ID number(5) CONSTRAINT RT_RINGTONE_SCHEDULE_FK1 REFERENCES RT_CATEGORIES(CATEGORY_ID), RINGTONE_ID number(5) CONSTRAINT RT_RINGTONE_SCHEDULE_FK2 REFERENCES RT_TONE_INFO(RINGTONE_ID), START_DATE date, END_DATE date NOT NULL, SEQ_ORDER number(5), CONSTRAINT RT_RINGTONE_SCHEDULE_PK PRIMARY KEY(LOCATION_ID,CATEGORY_ID,RINGTONE_ID,START_DATE) ) _______________________________________________ sapdb.general mailing list [EMAIL PROTECTED] http://listserv.sap.com/mailman/listinfo/sapdb.general
