Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> In need of Advanced Oracle Developer Help!!!

In need of Advanced Oracle Developer Help!!!

From: VTtigger10 <vttigger10_at_gmail.com>
Date: 16 Jan 2007 12:18:58 -0800
Message-ID: <1168978738.162485.20450@11g2000cwr.googlegroups.com>


Here is my issue....

I need to create a view over 2 tables. Sounds easy right? Well, here's the problem. The first part of the view is a full outer join from one of the tables to itself based on an ID in the table. The resulting fields would be Start Date, ID, End Date. Simple enough, however, this is going to leave me with null values in either Start Date or End Date for any particular record. That's not going to do. I need to be able to fill any null values in the date columns with the appropriate default value (Start Date - '01/01/1900', End Date '01/01/9999') Once that is done, I can use the ID to join it to the second table.

The reason I need those values to be not null is because I'm using the view in a look up within Informatica and I don't think you can compare a date to a null value.

Second question would be if you compare a valid date to a null value in SQL, would the valid date be greater than or less than the null value? or would it throw an error?

Any Ideas?

Here's the code I have so far:

SELECT G.CASE_MASTER_ID AS CASE_MASTER_ID, G.CANDIDATE_ID AS CANDIDATE_ID, H.OPENED_DATE AS OPENED_DATE, H.CLOSED_DATE AS CLOSED_DATE
FROM CASES_MASTER G,
(SELECT C.CASE_MASTER_ID AS CASE_MASTER_ID, C.OPENED_DATE AS
OPENED_DATE, F.CLOSED_DATE AS CLOSED_DATE FROM (SELECT A.CASE_MASTER_ID AS CASE_MASTER_ID, A.STATUS_DATE_SET AS OPENED_DATE
FROM CASE_STATUS_HISTORY A, TREF_CASE_STATUS B WHERE A.CASE_STATUS_ID = B.CASE_STATUS_ID AND B.CASE_STATUS_ID = 'OPEN') C
FULL OUTER JOIN
(SELECT D.CASE_MASTER_ID AS CASE_MASTER_ID, D.STATUS_DATE_SET AS
CLOSED_DATE
FROM CASE_STATUS_HISTORY D, TREF_CASE_STATUS E WHERE D.CASE_STATUS_ID = E.CASE_STATUS_ID AND E.CASE_STATUS_ID NOT IN ('OPEN','PENDING')) F ON C.CASE_MASTER_ID = F.CASE_MASTER_ID) H WHERE G.CASE_MASTER_ID = H.CASE_MASTER_ID Received on Tue Jan 16 2007 - 14:18:58 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US