Help in CR9 with left outer join 1

[bbwrede]

HI,

I am trying to create a report involving a left outer join. I couldn't figure it out in 8.5 so I finally upgraded to 9, but I still can't get it to work. Here is the situation:


I have 3 tables with the following fields

subscriber - each subscriber has one record

subnum
subname
etc...

subaccount - each subscriber will have at least one, but can have many - (must add up all 3060's for one subnumber)

subnum
3060
6090
90


subadjustment - can have none or many - add up adjAmt+TaxAmt on all records for one subscriber

subnum
adjAmt
TaxAmt



This query should result in the total past due amounts for each subscriber taking into account any adjustment amount. The adjustment amount needs the left outer join.

subscriber table
100 JOhn Smith
200 Barb Smith
300 Alex Carter

subAccount table

subnum 3060 6090 90
100 20.00 0 0
100 5.00 25.00 0
200 0 0 0
300 23.00 1.00 45.00
300 0 23.00 0
300 10.00 1.00 5.00
400 60.00 0 0

subAdjustment table

subnum adjAmt taxAmt
300 2.00 0
300 -10.00 -1.00
300 -2.00 0
400 -20.00 -10.00
400 -10.00 0
700 -20.00

When I run my query I want all subscribers that have a past due balance of > 25.00. A past due balance is defined as all 3060,6090,90 past due records for each subnum + all negative adjAmt's (only select adjAmt's that are negative and add in taxAmt) for that subscriber.

The result for the 3 above tables would be:

subnum 3060 6090 90 adjAmt PastDueAmount
100 25.00 25.00 0 0 50.00
300 33.00 25.00 50.00 -13.00 37.00


400 will not be in the list because the adjAmt brings this subscriber below 25.00.

I have tried the following:

COMMAND

SELECT
SUM(ADJAMT) + SUM(TAXAMT) AS adjamt
FROM
subAdjustment
WHERE
ADJAMT < 0
GROUP BY SUBNUM

Then I link the other two tables - subscriber -> subaccount inner join
subscriber -> subAdjustment left outer join

This will run forever and when I stop it - it stops on the 1st record with an adjAmt.

I have also tried 2 commands and that takes to long.

I have also tried one big query with an inner join on subscriber and subaccount and then left outer to bring in the subAdjustment, but it doesn't work. I only get subscribers that have an adjustment amount - cr treats it like an inner join.

I'm sorry this is so long. Thanks for any help.
Barb Wrede

 

[FVTrainer]

First, a command object should encompass all the tables and joins you need. Do not use a command and then join to tables or another command unless you want really bad perfomance. It's like joining a stored procedure to a table. Very bad performance will be the result.

The second problem is with the big query your wrote (I have also tried one big query...). Here, your logic simulates an inner join. Think about it: even with the left outer join, your placing a limitation on the resultset using a field from the table you're doing an outer join on. So how can the resultset give you back a record that doesn't have adjamt value when you've said WHERE ADJAMT < 0? If the ADMAMT field is NULL, which would be the result from a Left Outer join to that table when the subscriber has no subAjustment records, it is exculded by your logic.

That's not a crystal problem. That sql statement would behave the same in a stored procedure (well, at least in a MS SQL Server environment).

A third challenge in your query is that you have a 1:many relationship between subscriber and subaccount, and also a one to many relationship betwee subscriber and subAjustment. So you'll end up with duplicates (given the table schema shown) if you just do joins using subnum as the key field.

Below is a Command object that I think will meet your needs, although I suspect the performance will be slow (assuming a database of some size):

SELECT
s.subnum, n.3060, n.6090, n.90, a.adjAmt, a.taxAmt
FROM
subscriber s INNER JOIN
(SELECT subnum, sum(3060) as '3060', sum(6090) as '6090', sum(90) as '90' FROM subaccount GROUP BY subnum) n ON s.subnum = n.subnum
LEFT OUTER JOIN (SELECT subnum, SUM(adjAmt) as adjAmt, SUM(taxAmt) as taxAmt FROM subAdjustment WHERE adjAmout IS NULL or adjAmount < 0 GROUP BY subnum) a ON s.subnum = a.subnum

This would be better done in a stored procedure rather than a command object, I think, but it should serve your purpose if I've understood your problem correctly.

 

[bbwrede]

Thank you so much. I have been struggling with this for a long time. It works perfectly. I didn't know you could use a subquery in this way. I have only used them with IN or EXISTS etc. If I want to search for info in my database book or on the internet what would I search under? Is it a subquery?
I can't thank you enough - I can have my nights back!!