[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

Problem with query results on oracle

Posted on 2010-08-17
10
Medium Priority
?
769 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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 

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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
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.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Via a live example, show how to take different types of Oracle backups using RMAN.
Suggested Courses

591 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