Left Outer Join Acts As An Inner Join on View Hierarchy

[horatiog]

Hi,

I have been designing a view in Oracle that is then used by Crystal reports. This view is becoming more and more complex and I have eventually hit a problem that, hopefully, somebody can help me out with.

Simplified, the problem is with one of the tables used in the main view. It shows a record of sales for each property on the system. However, sales may be cancelled and then re-sold. Thus, the Sales Table includes a column holding the Sale sequence number.

My problem results from a procedure change by the system users. The 3rd party system will not allow the amendment of the reservation and cancellation dates (and other important data) once the sale has been cancelled. Occasionally, users have cancelled a sale which contained incorrect data. This causes problems in a raft of other reports we have designed. (e.g. when the reservation date of the subsequent sale precedes the cancellation date of the previous sale because the latter date was input incorrectly.)

The users have decided that they will use another column on the Sales table, the “Cancellation Reason” code, to flag these rows as “Cancellation Due To Administrative Error” (Code = 998). After changing the code to 998, the users will then go on to re-create and re-cancel the Sale with the correct dates, etc. We have been told to completely ignore any rows with a cancellation reason code of 998.

The view that I am having problems with, requires rows that have an effective start date equal to when the current sale was reserved and an effective end date equal to when the next sale was reserved. Previously I declared the Sales Table under two separate aliases and joined them by ( NextSale.Sequence = (ThisSale.Sequence + 1) )

Of course, since the introduction of “rows to ignore”, the solution is now not so simple. I have tried to break the problem down into a hierarchical set of two views. I firstly designed the child view as follows (considerably simplified for the example):

CREATE OR REPLACE VIEW SalesView
AS
SELECT
Sales.PlotRef,
( SELECT
COUNT(*)
FROM
Sales Sales2
WHERE
( Sales2. PlotRef = Sales1. PlotRef
AND
Sales2. Sequence < Sales1. Sequence
AND
Sales2. CancelReason <> '998'
)
)
Sales.Sequence,
.
.
FROM
Sales
WHERE
Sales2. CancelReason <> '998'

This view excludes all the Sales rows that have been cancelled due to Admin Error and re-sequences the Sales records for each plot. (i.e. the sequence numbers will still be contiguous, even if an “Admin Error” row has been excluded.) Running a query on this view suggests that it is working correctly. (i.e. the number of rows in the base Sales table minus the “Cancellation Code = ‘998’” rows.

In the parent view, I then declare this child view twice to allow me to join each sales row to its successor. Again, I have considerably simplified the view for this example:


SELECT
<View Fields>
FROM
SalesView ThisSale,
SalesView NextSale
WHERE
( NextSale.PlotRef (+) = Sales.PlotRef
AND
( NextSale.Sequence (+) = (ThisSale.Sequence + 1) )
)


The left outer join here is because for each plot there will be at least one sales row (the most recent) that has no successor. I use an NVL function to generate “high values” effective end date in this case.

Theoretically, I should get the same number rows in this view as the number of non-998 rows in the base Sales table. However, I only seem to be getting rows where there is an actual successor to the Sales record. (i.e. where the plot has been legitimately cancelled and resold.) This suggests to me that the Left Outer Join, as shown above, is still acting as a standard Inner Join.

I hope that somebody can help me out here. Hopefully, it is a simple oversight but, if not, perhaps somebody can suggest an alternate strategy?

Many thanks for any help.

Regards,
Harry.

 

[SantaMufasa]

Harry,

Actually, (as you probably suspected) your code is doing exactly what you told it to, which, (in English pseudo-code is) "Show me only those rows which match on PlotRef between the two views AND the plot has been legitimately cancelled and resold." This means that the only rows that show will be cancelled/resold rows.

Although I don't know our data or application, I'm guessing that the following code would give you what you want:

SELECT
<View Fields>
FROM
    SalesView    ThisSale,
    SalesView    NextSale
WHERE
    (    NextSale.PlotRef (+) = Sales.PlotRef
           AND
        ( NextSale.Sequence (+) = (ThisSale.Sequence + 1) )
    )
OR
    (    NextSale.PlotRef (+) = Sales.PlotRef
           AND
        ( NextSale.Sequence (+) = ThisSale.Sequence )
    )
;

In the above adjusted code, you are asking for rows that either a) matching PlotRefs that have been cancelled or resold or b) matching PlotRefs that have not be cancelled or resold. Is that what you wanted?

Let us know your results.

Mufasa
(aka Dave of Sandy, Utah, USA @ 16:11 (03Aug04) UTC (aka "GMT" and "Zulu"), 09:11 (03Aug04) Mountain Time)

 

[horatiog]

Hi Mufasa,

Hmmm - the plot thickens!

Not absolutely sure this is what I'm after. In english I want for each property, details of each legitimate sales PLUS the reservation date of the subsequent sales record for the same plot, or NULL if there is no subsequent sales record.

In your example, did you mean:

SELECT
<View Fields>
FROM
SalesView ThisSale,
SalesView NextSale
WHERE
( NextSale.PlotRef (+) = THISSALE.PlotRef
AND
( NextSale.Sequence (+) = (ThisSale.Sequence + 1) )
)
OR
( NextSale.PlotRef (+) = THISSALE.PlotRef
AND
( NextSale.Sequence (+) = ThisSale.Sequence )
)
;

(i.e. did you mean "THISSALE" where you put "Sales" or did you intend me to refer back to the base table?)

In my mind, I think your code should return

1. All legitimate sales along with their next subsequent sales record (whether or not they actually have a sucessor - note the LOJ in this clause!)

PLUS

2. All legitimate sales duplicated.

Just for the record, what I actually get, when I run the above query is an error. Heres is the actual code and the response:

1 SELECT
2 COUNT(*)
3 FROM
4 "&&Environment.DTA".F55H501 SM,
5 "&&Environment.DTA".F55H501 SMN
6 WHERE
7 ( SMN.SMMCU (+) = SM.SMMCU
8 AND
9 SMN.SMCSSEQ (+) = (SM.SMCSSEQ + 1)
10 )
11 OR
12 ( SMN.SMMCU (+) = SM.SMMCU
13 AND
14 SMN.SMCSSEQ (+) = SM.SMCSSEQ
15* )

SQL> /

old 4: "&&Environment.DTA".F55H501 SM,
new 4: "PRODDTA".F55H501 SM,
old 5: "&&Environment.DTA".F55H501 SMN
new 5: "PRODDTA".F55H501 SMN
SMN.SMCSSEQ (+) = SM.SMCSSEQ
*
ERROR at line 14:
ORA-01719: outer join operator (+) not allowed in operand of OR or IN

....or maybe I am missing your point?

Thanks for your help and hope you can help me some more.

Regards,
Harry.

 

[SantaMufasa]

Harry,

Sorry about the Oracle error (Outer join does not work with "OR"), but we can fix that.

Let's first clarify what we want/need. In your original code, the WHERE clause says, "Any single row that displays must have BOTH of these conditions true:

...(    NextSale.PlotRef (+) = THISSALE.PlotRef
           AND
        ( NextSale.Sequence (+) = (ThisSale.Sequence + 1) )
    )...

Doesn't this mean that the only rows that can possibly display are rows that have a "follow-on sequence" (i.e., ThisSale.Sequence + 1). How do "ThisSale.Sequence" rows ever print out if we are asking only to see
"ThisSale.Sequence + 1"?

Now, to fix the error you received, try this alternate code:

SELECT
<View Fields>
FROM
    SalesView    ThisSale,
    SalesView    NextSale
WHERE
    (    NextSale.PlotRef (+) = THISSALE.PlotRef
           AND
        ( NextSale.Sequence (+) = (ThisSale.Sequence + 1) )
    )
UNION
SELECT
<View Fields>
FROM
    SalesView    ThisSale,
    SalesView    NextSale
WHERE
    (    NextSale.PlotRef (+) = THISSALE.PlotRef
           AND
        ( NextSale.Sequence (+) = ThisSale.Sequence )
    )
;
[code]

Let us know how that works...And please correct any misunderstanding I have in my clarification question, above.

[santa]Mufasa
(aka Dave of Sandy, Utah, USA @ 17:58 (03Aug04) UTC (aka "GMT" and "Zulu"), 10:58 (03Aug04) Mountain Time)

 

[horatiog]

Oops,

Maybe it's a problem with my understanding of how a Left Outer Join works.

Note that I am using the "old syntax" for the Left Outer Join of "(+)". I thought that this meant that the query will return ALL rows of the table on the left the join (irrespective of whether they meet the join criteria). Where the join criteria is successful, the columns from the right hand table are also returned, otherwise NULLs are returned in their place????

However, I took your advice and ran your suggested query. The actual code I ran was:

SELECT
SM.SMMCU,
SM.SMCSSEQ,
SMN.SMCSSEQ
FROM
"&&Environment.DTA".F55H501 SM,
"&&Environment.DTA".F55H501 SMN
WHERE
( SMN.SMMCU (+) = SM.SMMCU
AND
SMN.SMCSSEQ (+) = (SM.SMCSSEQ + 1)
)
UNION
SELECT
SM.SMMCU,
SM.SMCSSEQ,
SMN.SMCSSEQ
FROM
"&&Environment.DTA".F55H501 SM,
"&&Environment.DTA".F55H501 SMN
WHERE
( SMN.SMMCU (+) = SM.SMMCU
AND
SMN.SMCSSEQ (+) = SM.SMCSSEQ
)

where:

SMMCU = Property Ref
SMCSSEQ = SalesSequence
F55H501 = The child SalesView
SM = the alias for ThisSale
&
SMN = the alias for NextSale

However when I ran the query, I have now got a new error:

SQL> /
old 6: "&&Environment.DTA".F55H501 SM,
new 6: "PRODDTA".F55H501 SM,
old 7: "&&Environment.DTA".F55H501 SMN
new 7: "PRODDTA".F55H501 SMN
old 19: "&&Environment.DTA".F55H501 SM,
new 19: "PRODDTA".F55H501 SM,
old 20: "&&Environment.DTA".F55H501 SMN
new 20: "PRODDTA".F55H501 SMN
SMN.SMCSSEQ (+) = SM.SMCSSEQ
*
ERROR at line 24:
ORA-01417: a table may be outer joined to at most one other table

Note that this error has occurred in the second part of your union. I am wondering whether Oracle does not like the Outer Join on the SMCSSEQ, because this is a "generated column" in the child "SM" & SMN views? (See my initial posting.)

I am theorising that the parser does not trap this error in the first part of the union because I am performing arithmetic on the problematic column( (SM.SMCSSEQ + 1) ). However, when the query is finally submitted, the dataset returned appears to be that of an equi-join, because of this limitation?

Your thoughts/corrections would be appreciated.

Again, thank you for your help to date.

Regards,
Harry

 

[SantaMufasa]

Harry,

Correct, one cannot effect an outer join against a multi-table view. That is why you received the error, "ORA-01417: a table may be outer joined to at most one other table". In this case, however, we need that behaviour. Therefore, I propose the following solution (that should run just as quickly and may be simpler to understand):

Section 1 -- Sample F55H501 View data:

select smmcu, smcsseq from f55h501;

 SMMCU    SMCSSEQ
------ ----------
     1          1
     1          2
     3         20
     4         35
     4         36
     5         42
     6         55
     6         56

8 rows selected.

With the above data, we should see NEXT_SALE_SEQ values for PROP_REFs 1, 4, and 6 and NULL for PROP_REFs 3 and 5. (Correct?)

Section 2 -- A function which returns the SMCSSEQ if there is a "next sale seq" for a given PROP_REF and THIS_SALE sequence, or NULL if there is no "next sale seq":

create or replace function get_next_Sale (Prop_Ref number, This_sale number)
	return number 
is
	Next_sale_hold	number;
begin
	select smcsseq into next_sale_hold from f55h501
		where smmcu = prop_ref
		  and smcsseq = This_sale+1;
	return next_sale_hold;
exception
	when no_data_found then
		return null;
end;
/

Function created.

Section 3 -- a SELECT statement the produces the results you want:

select SMMCU Prop_ref, SMCSSEQ this_sale, get_next_sale(SMMCU, SMCSSEQ) Next_sale
from F55H501
where get_next_sale(SMMCU, SMCSSEQ-2) is null;

  PROP_REF  THIS_SALE  NEXT_SALE
---------- ---------- ----------
         1          1          2
         3         20
         4         35         36
         5         42
         6         55         56

5 rows selected.

Note: The WHERE statement, above, prevents printing extraneous "next sale seq" rows following appropriate "this sale/next sale" rows.

Let us know if this is an acceptable alternative.

Mufasa
(aka Dave of Sandy, Utah, USA @ 20:54 (03Aug04) UTC (aka "GMT" and "Zulu"), 13:54 (03Aug04) Mountain Time)

 

[horatiog]

Hi Mufasa,

Thanks for your continuing help and am sorry for the delay in getting back to you regard your latest input. I'm based in UK and so was on my way home when this arrived.

Anyway, the idea of a function is great. I had forgotten you could do this and then refer back to the function within a non-PL view.

I took your suggested function and modified it to get exactly what I was needing. I have made it recursive to increment down the Sales sequences until it finds the next valid Sales record or "falls off the end". Also, what I really needed the function to return is the reservation date of the next Sales record so that I can provide an "Effective End Date" for the current Sales record I'm considering. Thus, my code is as follows:

CREATE OR REPLACE FUNCTION "&&Environment.DTA".next_reservation_date
(
Plot_Ref VARCHAR,
Sequence NUMBER
)
RETURN NUMBER
IS
Cancellation_Reason CHAR(3);
Next_Reservation_Date NUMBER(6);
BEGIN
SELECT
SM.SMSDJ,
SM.SMCSRCD
INTO
Next_Reservation_Date,
cancellation_Reason
FROM
"&&Environment.DTA".F44H501 SM
WHERE
SM.SMMCU = PLot_Ref
AND
SM.SMCSSEQ = (Sequence + 1);

IF Cancellation_Reason = '998'
THEN
RETURN "&&Environment.DTA".next_reservation_date(Plot_Ref,(Sequence + 1));
ELSE
RETURN Next_Reservation_Date;
END IF;

EXCEPTION
WHEN NO_DATA_FOUND
THEN
RETURN NULL;
END;
/

This seems to do the trick although I'm going to have to do a bit more testing on it.

Once again, thanks for your rapid turnarounds and helpful advice. Much appreciated.

Regards,
Harry.

 

[SantaMufasa]

Harry,

I'd be a bit surprised if your new function does not give you either a compile-time or a run-time error. Your declared RETURN datatype is "NUMBER", but I infer from your code and your data names that your procedural RETURN datatype is DATE. That inconsistency should throw you an error. To fix it, just change your declared RETURN datatype (up before your "IS" declaration) to DATE (unless your returning "date" values really are NUMBER).

Mufasa
(aka Dave of Sandy, Utah, USA @ 11:49 (05Aug04) UTC (aka "GMT" and "Zulu"), 04:49 (05Aug04) Mountain Time)

 

[horatiog]

Hi Mufasa,

Aha! the thing I forgot to mention to you is that the database I am working with is that for a 3rd party ERP system, JD Edwards.

The way that the JDE system stores dates is pseudo-julian. E.g. The 30th January 2004 is held as the number 104030. (the first 3 characters is the number of years since 1900 and the second 3 digits is the day number of the year.

Its the front end of JDE that translates this number back to a date as we would recognise it. Since my final product is an Oracle view that maps back into a JDE view within the system, then I have to keep the date format consistent.

I've now incorporated the function into three views that require this functionality and it appears to work perfectly

Many thanks to yourself and Tektips for overcoming this hurdle.

Regards,
Harry.