Solved

oracle

Posted on 2011-03-05
7
416 Views
Last Modified: 2012-05-11
Experts.... I have a table which has 3 timestamp columns. I have to select rows from it where the timestamp in one of the 3 columns is the latest. ie. let's say that the table has these columns

a  number   not null
b  varchar2(30)  null
c  datetime null
d  datetime  null
e  datetime  null
f   number  null
g  varchar2(50)  null

I need to select rows where the timestamps in c,d or e is the latest. c,d and e may have data or may not have datetime values as they are declared as null columns. Do I have to do this using CASE statements or is there a better way.
0
Comment
Question by:chickanna
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 18

Expert Comment

by:sventhan
ID: 35043346
CREATE TABLE t (
datecol1 DATE,
datecol2 DATE,
datecol3 DATE)
PCTFREE 0;

INSERT INTO t VALUES (SYSDATE+23, SYSDATE-10, SYSDATE-24);
INSERT INTO t VALUES (SYSDATE-15, SYSDATE, SYSDATE+15);
INSERT INTO t VALUES (SYSDATE-7, SYSDATE-18, SYSDATE-9);
COMMIT;

SELECT * FROM t;

SELECT GREATEST(datecol1, datecol2, datecol3)
FROM t;

http://psoug.org/reference/date_func.html
0
 

Author Comment

by:chickanna
ID: 35043733
but if one of the columns has no value as it is defined as null columns then the above query will return empty in the result. I want the greatest of the timestamps even if there are nulls or no value in any of the columns.
0
 
LVL 14

Expert Comment

by:ajexpert
ID: 35043829
Not sure but can you try this?

SELECT   GREATEST (NVL (datecol1, TO_DATE ('01/01/1900', 'MM/DD/YYYY')),NVL (datecol2, TO_DATE ('01/01/1900', 'MM/DD/YYYY')), NVL (datecol3, TO_DATE ('01/01/1900', 'MM/DD/YYYY'))) from t

Open in new window

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.

 
LVL 4

Expert Comment

by:pinkuray
ID: 35054005
Use as not null condition if you don't want to view the null records:

SELECT GREATEST(DATECOL1, DATECOL2, DATECOL3)
FROM T WHERE DATECOL1 IS NOT NULL
AND DATECOL2 IS NOT NULL
and DATECOL3 is not null;


0
 
LVL 4

Expert Comment

by:pinkuray
ID: 35054695
use this way for timestamp:

INSERT INTO t VALUES (SYSTIMESTAMP+23, SYSTIMESTAMP-10, SYSTIMESTAMP-24);
INSERT INTO t VALUES (SYSTIMESTAMP-15, SYSTIMESTAMP, SYSTIMESTAMP+15);
INSERT INTO T VALUES (SYSTIMESTAMP-7, SYSTIMESTAMP-18, SYSTIMESTAMP-9);
COMMIT;

SELECT * FROM t;


SELECT TO_DATE(GREATEST(DATECOL1, DATECOL2, DATECOL3),'DD-MON-YYYY HH24:MI:SS') AS GRETEST_DATE
FROM T
WHERE DATECOL1 IS NOT NULL
AND DATECOL2   IS NOT NULL
AND DATECOL3   IS NOT NULL;
0
 

Author Comment

by:chickanna
ID: 35138291
ajexpert--- can i put a null value instead of 1/1/1900 as records with all null values in the 3 timestamp columns will get this 1/1/1900 value instead of null
0
 
LVL 14

Accepted Solution

by:
ajexpert earned 500 total points
ID: 35138521
You can write a wrapper to decode it with NULL

Execute the attached script for table inserts help-ee-table-inserts.sql
SELECT DECODE(GREATEST_DATE, TO_DATE('01/01/1900', 'MM/DD/YYYY'), NULL, GREATEST_DATE ) FROM
(
  SELECT   GREATEST (NVL (datecol1, TO_DATE ('01/01/1900', 'MM/DD/YYYY')),NVL (
      datecol2, TO_DATE ('01/01/1900', 'MM/DD/YYYY')), NVL (datecol3, TO_DATE
      ('01/01/1900', 'MM/DD/YYYY'))) GREATEST_DATE
      from t
      )

Open in new window

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

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…
Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
Via a live example, show how to take different types of Oracle backups using RMAN.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

838 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