Solved

Problem with query results on oracle

Posted on 2010-08-17
10
754 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 350 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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 150 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
selective queries 7 30
Can anyone please tell me what does below Stored Procedure does? 4 30
how to trim oracle sql sentence in unix 17 53
Oracle dataguard 5 32
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
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.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

809 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