SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Zealot LSW's Avatar
    Join Date
    Jul 2004
    Location
    Juneau Alaska
    Posts
    186
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Math Equation issue when data is missing.

    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:
    Code:
    SELECT
    reinstatementsum + claimsum - (indebtpaidsum * -1) + indebtadjustsum 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.

    Code:
    SELECT
    reinstatementsum + claimsum - (indebtpaidsum * -1) + 0.00 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!
    Thank You, Migwetth, Gunalche’esh, Ha’w'aa, Danke

    Kyle Lamson



  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    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)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Zealot LSW's Avatar
    Join Date
    Jul 2004
    Location
    Juneau Alaska
    Posts
    186
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.
    Thank You, Migwetth, Gunalche’esh, Ha’w'aa, Danke

    Kyle Lamson



  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    "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 --
    Code:
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •