Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Problem with query results on oracle

Posted on 2010-08-17
10
Medium Priority
?
764 Views
Last Modified: 2013-12-19
Hi, I just want to ask, every time I query this sql statement on toad i dont get the right results

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

Open in new window


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
0
Comment
Question by:philpen01
  • 6
  • 3
10 Comments
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 33453289
what is the data type of field4 ?
0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 33453293
try the below :

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 TO_DATE(NVL(TO_CHAR(STAMPTODATE(field4),'HH12:MI pm, DD Mon YYYY'),' '),'MM/DD/YYYY') desc

0
 
LVL 28

Accepted Solution

by:
Naveen Kumar earned 1400 total points
ID: 33453304
This should also work. Try this as well.

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 TO_DATE(NVL(TO_CHAR(STAMPTODATE(field4),'HH12:MI pm, DD Mon YYYY'),' '),'DD-MON-YYYY') desc
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:philpen01
ID: 33453371
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
0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 33453401
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 ?
0
 

Author Comment

by:philpen01
ID: 33453426
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
0
 
LVL 58

Assisted Solution

by:cyberkiwi
cyberkiwi earned 600 total points
ID: 33453451
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 TO_DATE(NVL(TO_CHAR(STAMPTODATE(field4),'MM/DD/YYYY'),' '), 'MM/DD/YYYY')  desc, field4 desc
0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 33453457
what is this stamptodate() ? is this your own function ? if yes, can you give the code of it.
0
 

Author Comment

by:philpen01
ID: 33453473
I modified the order by clause you gave in this manner... this one worked for me
ORDER BY STAMPTODATE(field4)  desc
0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 33453482
Good.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious sideā€¦
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to take different types of Oracle backups using RMAN.
Suggested Courses

926 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question