VTtigger10 wrote:
> 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
Posting the code was very helpful. NVL may be just the function that
you need. If a value is not null, the value is passed through as is,
but if it is null, the optional value that you specify is used instead.
For example:
NVL(H.OPENED_DATE,SYSDATE)
In the above, if H.OPENED_DATE is NULL, SYSDATE is returned, otherwise
H.OPENED_DATE is returned. With that in mind, you could modify the
query as follows (extra formatting was added for my benefit):
SELECT
G.CASE_MASTER_ID AS CASE_MASTER_ID,
G.CANDIDATE_ID AS CANDIDATE_ID,
NVL(H.OPENED_DATE,TO_DATE('01/01/1900','MM/DD/YYYY')) AS OPENED_DATE,
NVL(H.CLOSED_DATE,TO_DATE('01/01/9999','MM/DD/YYYY')) 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;
A NULL value can never be equal to any other value, including another
NULL value. However, the following is permitted:
WHERE NVL(COLUMN_1,'IS_NULL') = NVL(COLUMN_2,'IS_NULL')
WHERE NVL2(COLUMN_1,'IS_NOT_NULL', 'IS_NULL') =
NVL2(COLUMN_2,'IS_NOT_NULL','IS_NULL')
WHERE COLUMN_1 IS NULL AND COLUMN_2 IS NOT NULL
These are _not_ permitted:
WHERE COLUMN1 = NULL
WHERE COLUMN2 <> NULL
WHERE COLUMN2 LIKE NULL
Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc.
Received on Tue Jan 16 2007 - 16:30:47 CST
