Solved

oracle

Posted on 2011-03-05
7
418 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
[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
  • 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
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 
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

Independent Software Vendors: 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

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

738 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