Solved

oracle

Posted on 2011-03-05
7
417 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
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!

 
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

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to recover a database from a user managed backup

763 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