Outer join problem 1

[hazelsisson]

Hi guys,
It's an SQL problem - the query works fine without the date bits in the where clause, but as soon as I put them in it stops working. I've searched through many similar threads on the forum but none have pinpointed my problem!

Tables:
TIS_LINES
Line

CALLS_SUMMARY
Ln Total

TIS_LINES is a list of telephone extension numbers, and CALLS_SUMMARY has call prices for SOME of these lines. I want all the numbers to show though, which is the problem at the moment.

Desired output:
LINE TTOTAL
1234 30
1220 0 <- Line has no entry in CALLS_SUMMARY.

Actual Output:
LINE TTOTAL
1234 30

i.e. lines with no entries in CALLS_SUMMARY are omitted.

Here's my query:

SELECT t.LINE_NUMBER LINE, SUM(c.TOTAL) TTOTAL 
FROM TIS_LINES t, CALLS_SUMMARY c 
WHERE t.LINE_NUMBER(+) = c.LN
AND c.MIN_OF_START BETWEEN TO_DATE('18-Feb-2004','DD-MON-YYYY') AND TO_DATE('31-May-2004','DD-MON-YYYY') 
AND c.MAX_OF_END BETWEEN TO_DATE('01-Mar-2004','DD-MON-YYYY') AND TO_DATE('01-Jun-2004','DD-MON-YYYY') 
GROUP BY t.LINE_NUMBER

I can see that c.MIN_OF_START and c.MAX_OF_END are null if the line is in TIS_LINES and not in CALLS_SUMMARY, but I'm not sure how to overcome this in my query. It must be something simple!?

Thanks so much for your help, it's driving me crazy!
Hazel

 

[SantaMufasa]

Yes, Hazel, it is somethine simple. The way I remember which side needs the "(+)" is this: Think of the "(+)" as a "tombstone" that appears where we need "ghost" rows generated to match the "real" rows.

The way you have it coded now says, "My complete set of real rows is in CALLS_SUMMARY; I need 'ghost' rows generated from LINE_NUMBER to match all the real rows in CALLS_SUMMARY." You want just the opposite since all your real rows are in LINE_NUMBER and you need "ghost" rows generated in CALLS_SUMMARY. So the applicable code fragment should appear as follows:

...WHERE t.LINE_NUMBER = c.LN(+)
...

Let us know if this resolves your need.

Mufasa
(aka Dave of Sandy, Utah, USA @ 16:28 (02Sep04) UTC (aka "GMT" and "Zulu"), 09:28 (02Sep04) Mountain Time)

 

[hazelsisson]

Thanks for your response.
I like the way you remember which side the (+) goes on, I'll use it in the future!

Unfortunately it still doesn't work - as before, it does if I comment out the following lines:

AND c.MIN_OF_START BETWEEN TO_DATE('18-Feb-2004','DD-MON-YYYY') AND TO_DATE('31-May-2004','DD-MON-YYYY') 
AND c.MAX_OF_END BETWEEN TO_DATE('01-Mar-2004','DD-MON-YYYY') AND TO_DATE('01-Jun-2004','DD-MON-YYYY')

But if I leave them in it doesn't bring back the "ghost" rows.

 

[SantaMufasa]

Sorry, Hazel, I should have also included that since the "ANDs" you are using above are looking at specific date ranges for inclusion in the result set, we must also instruct Oracle to allow the "dates" from the "ghost" rows to appear in the results set. On the "ghost" rows, the dates are NULL. Therefore, your "ANDs" should read:

AND (c.MIN_OF_START BETWEEN TO_DATE('18-Feb-2004','DD-MON-YYYY') AND TO_DATE('31-May-2004','DD-MON-YYYY') 
AND c.MAX_OF_END BETWEEN TO_DATE('01-Mar-2004','DD-MON-YYYY') AND TO_DATE('01-Jun-2004','DD-MON-YYYY'))
OR (c.NIN_OF_START is NULL and c.MAX_OF_END is NULL)

Try that and let us know your findings.

Mufasa
(aka Dave of Sandy, Utah, USA @ 17:04 (02Sep04) UTC (aka "GMT" and "Zulu"), 10:04 (02Sep04) Mountain Time)

 

[hazelsisson]

Thanks very much Mufasa, it worked!
All I had to do was to add some extra brackets around the whole of that bit of code, so the final query was:

SELECT t.LINE_NUMBER LINE, SUM(c.TOTAL) TTOTAL 
FROM TIS_LINES t, CALLS_SUMMARY c 
WHERE t.LINE_NUMBER(+) = c.LN
AND ((c.MIN_OF_START BETWEEN TO_DATE('18-Feb-2004','DD-MON-YYYY') AND TO_DATE('31-May-2004','DD-MON-YYYY') 
AND c.MAX_OF_END BETWEEN TO_DATE('01-Mar-2004','DD-MON-YYYY') AND TO_DATE('01-Jun-2004','DD-MON-YYYY'))
OR (c.MIN_OF_START is NULL and c.MAX_OF_END is NULL))
GROUP BY t.LINE_NUMBER

Thanks again for your time.
Hazel