Help w/ left outer join in 8.5 w/ODBC

[bbwrede]

Hi,
I am having trouble figuring out how to get the data I need. I have 2 tables - a deliquint account table and an adjustment table. The deliquint account table can have multiple rows for one account. The adjustment table may or may not have 1 row for a subscriber if there is an adjustment on their account. I need to know every subscriber who has a delinquent account as defined:

Add up &quot;3060&quot; and &quot;6090&quot; rows for each subscriber (there may be multiple rows...) and then check the adjustment table and if there is an adjustment (type = 'A' and amount < 0) then subtract that amount from the &quot;3060&quot; + &quot;6090&quot; amount and if this is greater than 25 show record in report. Also, show records that are > 25 even if they don't have an adjustment.

Here is what I have so far.


SELECT
delAccount.&quot;3060&quot;, delAccount.&quot;6090&quot;, adjustment.&quot;amount&quot;
FROM
delAccount LEFT OUTER JOIN adjustment ON delAccount.&quot;subid&quot; = adjustment.&quot;subid#&quot;
WHERE
adjustment.&quot;type&quot; = 'A' AND adjustment.&quot;amount&quot; < 0
ORDER BY
delAccount.&quot;subid&quot; ASC

I haven't figured out how to sum the multiple rows for one subid in the delAccount table, but I can't even get this left join to work. It only gives me equal join records.

Any help is appreciated!
Thanks,
Barb

 

[lbass]

You are cancelling the left outer join by placing conditional criteria on it. If you remove the criteria from the record select, you can then write a formula {@adj}:

if isnull({adjustment.subidno}) or
not({adjustment.type} = 'A' and {adjustment.amount} < 0) then 0 else {adjustment.amount}

Place this in the detail section.

Group on {delAcct.subid}. Create a formula {@30606090):

{table.3060}+{table.6090}

To get the desired report display, go to report->edit selection formula->group and enter:

sum({@30606090},{delAcct.subid}) - sum({@adj},{delAcct.subid}) > 25

This should work as long as you don't have duplicates in your details section.

-LB

 

[bbwrede]

Thanks, I am going to try it now!!

 

[bbwrede]

I still can't get it to work and I am not exactly sure why. I am getting duplicate rows. I think that it might have something to do with the outer join. Since I can't limit it with my where clause (type = &quot;A&quot then I am getting a bunch of other matches and it is causing me to get duplicate rows back.

Does this make sense?
Thanks,Barb

 

[lbass]

Can you provide an example of the results that are returned and also a sample of how you would like the report to look? Are you getting duplicates within type = A so that summaries are inflated? A little more info, please.

-LB

 

[bbwrede]

Thanks a lot for helping me.

Yes, my summeries are inflated. I think it is because when I do the left outer join I am not able to limit by the type = 'A' and amount < 0. Each subscriber # could have multiple entries in this table under different types. I will make example tables below.

delAccount
subid 3060 6090

10 2.00 45.00
10 1.00 1.00
13 0 0
13 40.00 20.00
14 30.00


adjustment
subid type amount
10 A -3.00
10 M 54.00
10 M 0
10 F 0
14 A -6.00


The results I need are

subid totalpastdue
10 46.00 /* 45+2+1+1 - 3 */
13 60.00 /* 40+20 */


I think the problem is happening in the example above with subid 10 with all of the extra entries in the right adjustment table under different types. In my report if a subid has a lot of different types in the adjustment table the summery is inflated.

Let me know if I still need to give you more info.

Thanks alot for your time.

 

[lbass]

For each table is there a unique field that identifies individual rows? This could be an ID other than {delAccount.subid} or {adjustment.subid}, e.g., {delAccount.acctID} and {adjustment.adjID}. If not, is there a datetime field that would be unique to each record? This would help in eliminating duplicates from calculations, so let us know...

-LB

 

[bbwrede]

Yes, I can use a combination of the subid and one other field cid. The combination of these two fields is unique. This is for the delAccount table. What do I do to get the dupes out?
Thanks

 

[lbass]

This is a different approach, using running totals. First, group on {delAccount.subid}. Then create a formula {@uniqueID}:

{delAccount.subid} + {delAccount.cid}

Then group on {@uniqueID}. Then create {#30606090} by using the running total editor:
Select {@30606090} as in previous post, sum, evaluate on change of Group 2 (Unique ID), reset on change of Group 1 (SubID).

Next create a second running total {#adjamt} (this assumes one adjustment per subID--can their be more?):
Select {adjustment.amount}, sum, evaluate based on a formula:

(isnull({adjustment.type} or
{adjustment.type} = &quot;A&quot and
{delAccount.subID} <> previous({delAccount.subID})

Reset on change of Group 1 (SubID).

Next create a formula in the formula editor {@totpastdue}:

{#30606090}+{#adjamt}

Place this in the group 1 (SubID) footer. Go to format section->group headers,details, and footers->suppress->x+2 and enter:

{@totpastdue} < 25

-LB

 

[bbwrede]

Thanks for helping. I have a quick question. Should I still have my left outer join?

 

[lbass]

Yes, absolutely, or else you'd end up only with those records which had adjustments.

-LB

 

[bbwrede]

I am still not getting the correct data. I think it might be the evaluate on the adjAmt running total. I think the isnull case isn't working. I don't think I am getting the subscribers who owe more than 25 but don't have an adjustment amount. I do still have the left outer join in my query. Also, I added this to the totpastdue formula

if {#adjAmt} < 0 then
{#306090} + {#adjAmt}
else
{#306090}

I was afraid to add anything to the adjAmt running total so I put the adjamount < 0 test here.

Here is what I have for the adjAmt running total evaluate:

(isnull({TIPTRNU.TRTYPE}) or
{TIPTRNU.TRTYPE} = &quot;A&quot and
{TIPCGSMY.SMSUB#} <> previous({TIPCGSMY.SMSUB#})

Thanks for sticking with me through this.
Barb

 

[lbass]

It's hard to tell exactly what's happening since I can't recreate your data situation, but I think the problem is with the evaluate and the reset on the {#adjamt}. Try this instead for the evaluate:

{adjustment.type} = &quot;A&quot;

For the reset, try resetting on {@uniqueID}.

I'm assuming your data looks something like this, using your data example above:

UniqueID SubID 3060 6090 AdjType AdjAmt #30606090 #AdjAmt
10A 10 2 45 A -3 47 -3
10A 10 2 45 M 54 47 -3
10A 10 2 45 M 0 47 -3
10A 10 2 45 F 0 47 -3
10B 10 1 1 A -3 49 -3
10B 10 1 1 M 54 49 -3
10B 10 1 1 M 0 49 -3
10B 10 1 1 F 0 49 -3

You might try adding the running totals to the detail section to verify that this is the way your data is behaving. This will also let you see what it's counting and what it isn't.

Let me know if this works, and if not, please provide a display of actual data as above.

-LB

 

[synapsevampire]

What database are you using?

I would do this on the database side using a View or SP if possible.

LB's approach will work after you flesh out all of the details, but here's another approach:

Instead of ODBC, use ADO:

Data Explorer->More Data Sources->Active Data->Active Dataq (ADO)->Select your ODBC connection->OK

Double click you new ADO connection, select SQL.

You can now use real SQL here. This would mean building the SQL to return the rowset, and then pasting it in.

-k

 

[bbwrede]

Hi,
Everything that I try screws up the left outer join. I noticed in the output I got I only get people who have data in the adjustment table.Please see output below. I can't use views or stored procedures. I can only use ODBC. I am connecting to an AS/400.

Anyway, I have decided to try a different approach. Please let me know if this is impossible. Can't I just write a report with a subreport that returns the adjustment amount? 2 seperate queries for each line. It maybe really ineffieicent, but right now I just need to get it done!
Thanks,
Barb

Here is the sample output you requested above... The unique id may seem confusing. Sometimes the data that that I add to is -1. That is why the unique ID is sometimes the id itself.

unqid subid 3060 6090 90 amount type @306090 adjA

2616 2617 83.82 7.96 0 125.00 A 91.78 125.00
2616 2617 83.82 7.96 0 20.00 C 91.78 125.00

2617 2617 0 0 0 125.00 A 91.78 125.00
2617 2617 0 0 0 20.00 C 91.78 125.00

2905 2617 0 0 0 125.00 A 91.78 125.00
2905 2617 0 0 0 20.00 C 91.78 125.00

8770 2617 0 0 0 125.00 A 91.78 125.00
8770 2617 0 0 0 20.00 C 91.78 125.00

(next subid)

2948 2949 92.09 0 0 250.00 A 92.09 250.00
2948 2949 92.09 0 0 20.00 C 92.09 250.00

2949 2949 0 0 0 250.00 A 92.09 250.00
2949 2949 0 0 0 20.00 C 92.09 250.00

3171 2949 0 0 0 250.00 A 92.09 250.00
3171 2949 0 0 0 20.00 C 92.09 250.00

9102 2949 0 0 0 250.00 A 92.09 250.00
9102 2949 0 0 0 20.00 C 92.09 250.00

 

[lbass]

Can you confirm that your left join is from the delAccount table to the adjustment table and that the unique ID is using fields from the delAccount table?

-LB

 

[bbwrede]

HI,
Is this correct: delAccount LEFT OUTER JOIN adjustment
I have the delAccount table on the left.

The unique ID is using fields from the delAccount table.

Thanks,
Barb