• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 461
  • Last Modified:

Working with Returned Null Values and Dates

Ok; I have modified the file that has been delivered to me from the supplying vendor and it worked the way it should. However; I am faced with yet another small issue. When I try and select from my under lying table (New table with no data) to compare dates ; I of-course will receive a null value in my select statement. In the code snippet below, this works perfectly well if there is data in my base table APP_REGISTRAR.T_VIRTUAL_ORIENT; but if there is no data in this table because it is a new table, how can I compensate for a returned NULL value of a date datatype so that it will still bring across the initial data from my External table? I am currently running on Oracle 11g.

SELECT NETID, TO_DATE(COMP_DATE,  'YYYY-MM-DD HH24:MI:SS') "COMP_DATE"
FROM APP_REGISTRAR.T_EXTERNAL_VIRTUAL_ORIENT
WHERE TO_DATE(COMP_DATE, 'YYYY-MM-DD HH24:MI:SS') > (SELECT MAX(b.COMP_DATE) FROM APP_REGISTRAR.T_VIRTUAL_ORIENT b);
0
Seaghost
Asked:
Seaghost
2 Solutions
 
slightwv (䄆 Netminder) Commented:
If the select max returns null, just use NVL to return a very early date:

WHERE TO_DATE(COMP_DATE, 'YYYY-MM-DD HH24:MI:SS') > NVL((SELECT MAX(b.COMP_DATE) FROM APP_REGISTRAR.T_VIRTUAL_ORIENT b),TO_DATE('01/01/0001','MM/DD/YYYY'));
0
 
sdstuberCommented:
SELECT netid, TO_DATE(comp_date, 'YYYY-MM-DD HH24:MI:SS') "COMP_DATE"
  FROM app_registrar.t_external_virtual_orient
 WHERE TO_DATE(comp_date, 'YYYY-MM-DD HH24:MI:SS') >
           (SELECT MAX(comp_date)
              FROM (SELECT comp_date FROM app_registrar.t_virtual_orient
                    UNION ALL
                    SELECT TO_DATE('1', 'j') FROM DUAL));
0
 
SeaghostAuthor Commented:
This information has really resolved my issues. I am really grateful for all that you do.
0

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now