Solved

Problem with query results on oracle

Posted on 2010-08-17
10
759 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
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!

 

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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
Suggested Courses

624 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