Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

oracle

Posted on 2011-03-05
7
Medium Priority
?
422 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
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!

 
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 2000 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

Technology Partners: 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

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.

670 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