philpen01
asked on
Problem with query results on oracle
Hi, I just want to ask, every time I query this sql statement on toad i dont get the right results
I always get this result:
12:45 pm, 13 May 2009
11:59 am, 14 May 2009
11:58 am, 14 May 2009
10:45 am, 13 May 2009
and so on... it's sorting the value by time and not by it's date
i want to get something like 14 May followed by 13 May... and so on...
if i remove the NVL part from the select statement and select only field4 or 5, it sorts by date, but of course, it's in unix timestamp so i wouldn't get a human-readable date... I would only get something like
1281682145... and so on... is there a proper way of doing this? or converting the date without affecting the order by clause?
My database is ORACLE by the way
SELECT field1, field2,
CASE WHEN field3 IS NOT NULL THEN
CASE WHEN LENGTH(TRIM(TRANSLATE(SUBSTR(field3, INSTR(field3, ',') + 1), '0123456789', ' '))) IS NULL
THEN SUBSTR(field3, INSTR(field3, ',') + 1) ELSE 'NULL' END
ELSE 'NULL' END,
NVL(TO_CHAR(STAMPTODATE(field4),'HH12:MI pm, DD Mon YYYY'),' ') AS time1,
NVL(TO_CHAR(STAMPTODATE(field5),'HH12:MI pm, DD Mon YYYY'),' ') AS time2
FROM TABLE1
WHERE TO_DATE(NVL(TO_CHAR(STAMPTODATE(field4),'MM/DD/YYYY'),' '), 'MM/DD/YYYY')
BETWEEN TO_DATE('04/16/2009', 'MM/DD/YYYY') AND TO_DATE('01/05/2010', 'MM/DD/YYYY')
ORDER BY field4 desc
I always get this result:
12:45 pm, 13 May 2009
11:59 am, 14 May 2009
11:58 am, 14 May 2009
10:45 am, 13 May 2009
and so on... it's sorting the value by time and not by it's date
i want to get something like 14 May followed by 13 May... and so on...
if i remove the NVL part from the select statement and select only field4 or 5, it sorts by date, but of course, it's in unix timestamp so i wouldn't get a human-readable date... I would only get something like
1281682145... and so on... is there a proper way of doing this? or converting the date without affecting the order by clause?
My database is ORACLE by the way
what is the data type of field4 ?
try the below :
SELECT field1, field2,
CASE WHEN field3 IS NOT NULL THEN
CASE WHEN LENGTH(TRIM(TRANSLATE(SUBS TR(field3, INSTR(field3, ',') + 1), '0123456789', ' '))) IS NULL
THEN SUBSTR(field3, INSTR(field3, ',') + 1) ELSE 'NULL' END
ELSE 'NULL' END,
NVL(TO_CHAR(STAMPTODATE(fi eld4),'HH1 2:MI pm, DD Mon YYYY'),' ') AS time1,
NVL(TO_CHAR(STAMPTODATE(fi eld5),'HH1 2:MI pm, DD Mon YYYY'),' ') AS time2
FROM TABLE1
WHERE TO_DATE(NVL(TO_CHAR(STAMPT ODATE(fiel d4),'MM/DD /YYYY'),' '), 'MM/DD/YYYY')
BETWEEN TO_DATE('04/16/2009', 'MM/DD/YYYY') AND TO_DATE('01/05/2010', 'MM/DD/YYYY')
ORDER BY TO_DATE(NVL(TO_CHAR(STAMPT ODATE(fiel d4),'HH12: MI pm, DD Mon YYYY'),' '),'MM/DD/YYYY') desc
SELECT field1, field2,
CASE WHEN field3 IS NOT NULL THEN
CASE WHEN LENGTH(TRIM(TRANSLATE(SUBS
THEN SUBSTR(field3, INSTR(field3, ',') + 1) ELSE 'NULL' END
ELSE 'NULL' END,
NVL(TO_CHAR(STAMPTODATE(fi
NVL(TO_CHAR(STAMPTODATE(fi
FROM TABLE1
WHERE TO_DATE(NVL(TO_CHAR(STAMPT
BETWEEN TO_DATE('04/16/2009', 'MM/DD/YYYY') AND TO_DATE('01/05/2010', 'MM/DD/YYYY')
ORDER BY TO_DATE(NVL(TO_CHAR(STAMPT
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
tried it, I'm getting an ORA-01722: invalid number error
the data type of field 4 is NUMBER(20) based on toad schema browser
the data type of field 4 is NUMBER(20) based on toad schema browser
Can you give some sample values for field4 in your table. I think it is due to some bad data in your field4 column.
which one did you try ( first or second ) ? i gave you two statements right ?
which one did you try ( first or second ) ? i gave you two statements right ?
ASKER
I tried both... with my current sql statement it retrieves records, but as i've mentioned it's not sorting properly, when i tried the statements you gave, i get an error
here are sample values of the field 4
1281588784, 1281674431, 1281674610
here are sample values of the field 4
1281588784, 1281674431, 1281674610
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
what is this stamptodate() ? is this your own function ? if yes, can you give the code of it.
ASKER
I modified the order by clause you gave in this manner... this one worked for me
ORDER BY STAMPTODATE(field4) desc
ORDER BY STAMPTODATE(field4) desc
Good.