Math Equation issue when data is missing

Databases
1

I have no better idea how to describe it in the name as I do in a Google search.

Background: I am rewriting part of an ASP classic app into crystal reports using my own SQL. The app used declarations and such to create 4 different sums from fields from 4 different tables and then the final total I want comes from a mathematical string using those four sums. (PL SQL in Toad for Crystal XI)

Result: I created a whole PL SQL ‘Script’ using “WITH” rather than sub-queries to create the four sums (1 sum per table) and then the final SELECT does the math using those 4 sums.

Problem: My test member is only in 3 of the tables. The first 3 sums work fine (mathematically) and I get the sum I want. However it includes action on the sum of the 4th table which they are not in. As soon as it reaches the fourth table, rather than see it as 0.00, it crashes and gives me no return at all including the member ssn.

Basically this:

SELECT
reinstatementsum + claimsum - (indebtpaidsum * -1) + [I]indebtadjustsum[/I] AS totalindebtedness
FROM totalreinstatement, totalclaim, totalindebtpaid, totalindebtadjust

reinstatementsum + claimsum - (indebtpaidsum * -1) returns what I want, $23529.68
I would need “indebtadjustsum” to return 0.00 or something if the ssn in question is not a member of the table it draws from.

SELECT
reinstatementsum + claimsum - (indebtpaidsum * -1) + [I]0.00[/I] AS totalindebtedness
FROM totalreinstatement, totalclaim, totalindebtpaid, totalindebtadjust

Right now it does the math and then drops it and returns an empty row just like when I query the table for the SSN, it returns nothing.

In theory, any one member could ‘not be in’ any of those tables in any mix.

Does anyone have any ideas how I can deal with this? I always fall back into coding like the app does with IF or OR statements. But I am lost how to do it with SQL.

I could use 4 different statements and do the math with Crystal formulas but the office prefers SQL and I need or formula to suppress the result under certain conditions and I would not know how to use a “Suppress IF” in the formula I am doing the math in. So I would appreciate an SQL answer if there is any.

Much Obliged, Cheers!

2

based on the sql you posted, the FROM clause is a cross join

this only works properly if each of those tables has only 1 row

and from this i conclude that your WITHs have hardcoded the (single) ssn

what you’re actually looking for is a LEFT OUTER JOIN

which would be easy to code if you use subqueries, and pass in the ssn to only the first of the 4 tables, and then joining them on the ssn column

(that way you could also run the calculation for more than one ssn at a time)

3

It is only hard coded in as far as the ssn’s are all a crystal variable and the code is only for one specific ssn at a time.

I played with joins and unions, but in the end there is no real connection between any of the 4 tables, so there is no real use for a Join, I just need the 4 sums in one place for the math part, so in fact each WITH table does have just one row.

Thanks for the input. But it seems that using with requires that the member be in all 4 tables which is not the case, any one member can be missing from any one or more of the 4 Oracle tables depending on the benefits chosen & whether money is owed. Then of course if the member has never owed money or paid it off already, it needs to return $0.00.

From the looks of things, I am going to have to set Variables within Crystal Reports and then use multiple IF statements to get it all to mesh. I was hoping to do it on the SQL side, never really trusted Crystal formulas. Oh well.

Thank you for your time and trying.

4

“any one member can be missing from any one or more of the 4 Oracle tables”

okay, so use a UNION, and then use crystal IFs to test the four numbers –

SELECT 'reinstatementsum' AS type
     , reinstatementsum AS amount
  FROM totalreinstatement     
UNION ALL
SELECT 'claimsum' AS type
     , claimsum AS amount
  FROM totalclaim
UNION ALL
SELECT 'indebtpaidsum' AS type
     , indebtpaidsum AS amount
  FROM totalindebtpaid
UNION ALL
SELECT 'indebtadjustsum' AS type
     , indebtadjustsum AS amount
  FROM totalindebtadjust