Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2009
    Posts
    11

    Unanswered: SQL Query Gives Diff Results After Each Run

    Hi Guys,

    I would really appreciate your help in assisting me with this situation as it's really beating the crap out of me. I have this sql that brings back customer invoices for a six months span. However, each time it returns a different result. The first run brinks back the correct values for customer x, the second run brings back incomplete values for customer x and the third run brings the correct value. I know the sql is correct because when I run it for individual customers it always returns the correct value. However when I run it for everyone that's when things go wrong.




    Code:
    SELECT customer_name,account,Location,City,District,Add_Info,
           sum(Month1) Mnth1,
           sum(Month2) Mnth2,
           sum(Month3) Mnth3,
           sum(Month4) Mnth4,
           sum(Month5) Mnth5,
           sum(Month6) Mnth6,
           Credit_Limit,Deposit,acnt_tp,acnt_tp2
    FROM
    (
    SELECT pty.fullname customer_name, act.name account,act_tp.description acnt_tp,
        Case
           when addr.contacttype_id = 1 then addr.formattedcontactline1
           when addr.contacttype_id = 2 and instr(addr.formattedcontactline1,',') > 0 then substr(addr.formattedcontactline1,1,instr(addr.formattedcontactline1,',')-1)
          else addr.formattedcontactline1
        End Location,
        coalesce(coalesce( (select st.name from tytan.tadaddressdictionary st
                                   where st.id = addr.addressdictionary07_id),addr.formattedcontactline2),'N/A') City,
        coalesce(coalesce( (select st.name from tytan.tadaddressdictionary st
                                 where st.id = addr.addressdictionary06_id),addr.formattedcontactline3),'N/A') District,
        CASE
          WHEN act.accounttype_id = 8 THEN addr.formattedcontactline3
         ELSE ''
        END Add_Info,
        Case
          when bill_cycles.billcycledef_id = 1 and  bill_cycles.dfrom = add_months(to_date('30/06/2009','dd/mm/yyyy')+1,-6) then inv.netamount + inv.taxamount
          when bill_cycles.billcycledef_id = 2 and  bill_cycles.dfrom = add_months(to_date('30/06/2009','dd/mm/yyyy')+15,-7) then inv.netamount + inv.taxamount
         else 0
        End Month1,
        Case
          when bill_cycles.billcycledef_id = 1 and  bill_cycles.dfrom = add_months(to_date('30/06/2009','dd/mm/yyyy')+1,-5) then inv.netamount + inv.taxamount
          when bill_cycles.billcycledef_id = 2 and  bill_cycles.dfrom = add_months(to_date('30/06/2009','dd/mm/yyyy')+15,-6) then inv.netamount + inv.taxamount 
         else 0
        End Month2,
        Case
          when bill_cycles.billcycledef_id = 1 and  bill_cycles.dfrom = add_months(to_date('30/06/2009','dd/mm/yyyy')+1,-4) then inv.netamount + inv.taxamount
          when bill_cycles.billcycledef_id = 2 and  bill_cycles.dfrom = add_months(to_date('30/06/2009','dd/mm/yyyy')+15,-5) then inv.netamount + inv.taxamount
         else 0
        End Month3,
        Case
          when bill_cycles.billcycledef_id = 1 and  bill_cycles.dfrom = add_months(to_date('30/06/2009','dd/mm/yyyy')+1,-3) then inv.netamount + inv.taxamount
          when bill_cycles.billcycledef_id = 2 and  bill_cycles.dfrom = add_months(to_date('30/06/2009','dd/mm/yyyy')+15,-4) then inv.netamount + inv.taxamount
         else 0
        End Month4,
        Case
          when bill_cycles.billcycledef_id = 1 and  bill_cycles.dfrom = add_months(to_date('30/06/2009','dd/mm/yyyy')+1,-2) then inv.netamount + inv.taxamount
          when bill_cycles.billcycledef_id = 2 and  bill_cycles.dfrom = add_months(to_date('30/06/2009','dd/mm/yyyy')+15,-3) then inv.netamount + inv.taxamount
         else 0
        End Month5,
         Case
          when bill_cycles.billcycledef_id = 1 and  bill_cycles.dfrom = add_months(to_date('30/06/2009','dd/mm/yyyy')+1,-1) then inv.netamount + inv.taxamount
          when bill_cycles.billcycledef_id = 2 and  bill_cycles.dfrom = add_months(to_date('30/06/2009','dd/mm/yyyy')+15,-2) then inv.netamount + inv.taxamount
         else 0
        End Month6,
        act.creditlimit Credit_Limit,
        (select -sum(fin.ctuamount+fin.dtuamount) 
               from tytan.tfindocparts fin,
                    tytan.tfindoctypes tp
               where fin.doctype_id = tp.id and
                   fin.cpartyaccount_id = act.account_id and
                  (shortname like 'Deposit%' or
                   shortname like 'External Deposit%' or
                   shortname like 'Interest on Deposit%' or
                   shortname like 'Return Deposit%') ) Deposit,
      
       (Select act_tp2.description
        from tytan.tamcpartyaccountd act2,
               tytan.tamcpartyaccounttypes act_tp2
        where act2.accountdaccount_id = inv.cpartyaccount_id and
                  act2.accounttype_id = act_tp2.id and
                 act2.isvalid = 'Y' and fin.issuedate between nvl(act2.dfrom,sysdate-9999) and nvl(act2.dto,sysdate+9999)) acnt_tp2
    
                  
        FROM tytan.tamcpartyaccountd act, 
             tytan.tamcontractingpartyd pty,
             tytan.tamcpartyaccounttypes act_tp,
             tytan.tfininvoiceitems inv, 
             tytan.tfindocs fin, 
             tytan.tcdbillcycles bill_cycles,
             tytan.tamcontactusage clink,
             tytan.tamcontacts addr
        WHERE act.cparty_id = pty.cparty_id 
          --AND act.accounttype_id in (3,8,16)
          AND inv.cpartyaccount_id = act.account_id
          AND act.accounttype_id = act_tp.id 
          AND inv.doc_id = fin.id
          AND fin.billcycleno = bill_cycles.billcycleno
          AND fin.billcycledef_id = bill_cycles.billcycledef_id
          AND act.account_id = clink.cpartyaccount_id 
          AND clink.contact_id = addr.id
          AND clink.isvalid = 'Y' and clink.usagelevel = 'A' 
          AND clink.usagetype_id is not null and clink.dto is null
          AND inv.type = 'T'
          AND fin.type_id != 8 and /*  inv.cparty_id = 90718 --73372  */
          AND bill_cycles.dfrom between add_months(to_date('30/06/2009','dd/mm/yyyy')+15,-7) and add_months(to_date('30/06/2009','dd/mm/yyyy')+1,-1)
          --AND bill_cycles.dfrom in ('1-Jun-2009','15-May-2009')
    order by pty.fullname, act.name      
    )mnth
    GROUP BY District,City,Location,Add_Info,customer_name,account,Credit_Limit,Deposit,acnt_tp,acnt_tp2
    Last edited by rhayabusa22; 08-11-09 at 18:59.

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool Nulls is null

    Maybe you need to consider that some of the amounts/numbers may be null, whenever there is an amount/number you are using in an expression, you may want to use the NVL(theamt,0) function.

    I recommend it!


    PS: In the future, try to use the "code" tags to format your posted code.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    Aug 2009
    Posts
    11

    No Avail

    Thanks for the reply. I tried that, however still no change. Any suggesstions would be appreciated. Below is how the result would look for random customers.

    Code:
    Run1:
    
    Cust_Name   Mnth1  Mnth2  Mnth3  Mnth4  Mnth5  Mnth6
    CustomerA    $100    $350   $200    $150    $170    $200
    
    Run2:
    
    Cust_Name   Mnth1  Mnth2  Mnth3  Mnth4  Mnth5  Mnth6
    CustomerA    $100    $0       $0       $0       $0       $0
    
    Run3:
    
    Cust_Name   Mnth1  Mnth2  Mnth3  Mnth4  Mnth5  Mnth6
    CustomerA    $100    $350   $200    $150    $170    $200

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Hm, perhaps you should consider discarding results of every even run and accept only odd ones.

    Just kidding ... However, the same query executed on the same data set is supposed to return the same result. Isn't it? So, if the query didn't change, perhaps data set did?

  5. #5
    Join Date
    Aug 2009
    Posts
    11

    a little light at the end of the tunnel

    Thanks for the reply, maybe I will discard the odd runs . What's strange, is that when I run this report off a copy database, it yields the correct result each time. However, on the production database it yields the wrong result. The only thing I can think of is that there are always different queries and processes running on the production database. Do you think it's possible that some records become locked hence the missing of data sometimes? I will see if I can do some checks with the DBA.

    For the customers that yielded the wrong values on different occassions, I checed the system and nothing changed with their records. Thanks for the assistance.

  6. #6
    Join Date
    Aug 2009
    Posts
    11

    Some Progress

    Ok, I commented out some of the fields, Month 1 to 5. Running the report for the entire period but only selecting month 6 always yeilds the correct result. However, if I don't comment out the other field, the result for all months is off.




    Code:
    SELECT customer_name,account,Location,City,District,Add_Info,
           /*  sum(Month1) Mnth1,  */
    /*         sum(Month2) Mnth2,  */
    /*         sum(Month3) Mnth3,  */
    /*         sum(Month4) Mnth4,  */
    /*         sum(Month5) Mnth5,  */
           sum(Month6) Mnth6,
           Credit_Limit,Deposit,acnt_tp,acnt_tp2
    FROM
    (
    SELECT pty.fullname customer_name, act.name account,act_tp.description acnt_tp,
        Case
           when addr.contacttype_id = 1 then addr.formattedcontactline1
           when addr.contacttype_id = 2 and instr(addr.formattedcontactline1,',') > 0 then substr(addr.formattedcontactline1,1,instr(addr.formattedcontactline1,',')-1)
          else addr.formattedcontactline1
        End Location,
        coalesce(coalesce( (select st.name from tytan.tadaddressdictionary st
                                   where st.id = addr.addressdictionary07_id),addr.formattedcontactline2),'N/A') City,
        coalesce(coalesce( (select st.name from tytan.tadaddressdictionary st
                                 where st.id = addr.addressdictionary06_id),addr.formattedcontactline3),'N/A') District,
        CASE
          WHEN act.accounttype_id = 8 THEN addr.formattedcontactline3
         ELSE ''
        END Add_Info,
    /*      Case  */
    /*        when bill_cycles.billcycledef_id = 1 and  bill_cycles.dfrom = add_months(to_date('30/06/2009','dd/mm/yyyy')+1,-6) then nvl(inv.netamount,0) + nvl(inv.taxamount,0)  */
    /*        when bill_cycles.billcycledef_id = 2 and  bill_cycles.dfrom = add_months(to_date('30/06/2009','dd/mm/yyyy')+15,-7) then nvl(inv.netamount,0) + nvl(inv.taxamount,0)  */
    /*       else 0  */
    /*      End Month1,  */
    /*      Case  */
    /*        when bill_cycles.billcycledef_id = 1 and  bill_cycles.dfrom = add_months(to_date('30/06/2009','dd/mm/yyyy')+1,-5) then nvl(inv.netamount,0) + nvl(inv.taxamount,0)  */
    /*        when bill_cycles.billcycledef_id = 2 and  bill_cycles.dfrom = add_months(to_date('30/06/2009','dd/mm/yyyy')+15,-6) then nvl(inv.netamount,0) + nvl(inv.taxamount,0)   */
    /*       else 0  */
    /*      End Month2,  */
    /*      Case  */
    /*        when bill_cycles.billcycledef_id = 1 and  bill_cycles.dfrom = add_months(to_date('30/06/2009','dd/mm/yyyy')+1,-4) then nvl(inv.netamount,0) + nvl(inv.taxamount,0)  */
    /*        when bill_cycles.billcycledef_id = 2 and  bill_cycles.dfrom = add_months(to_date('30/06/2009','dd/mm/yyyy')+15,-5) then nvl(inv.netamount,0) + nvl(inv.taxamount,0)  */
    /*       else 0  */
    /*      End Month3,  */
    /*      Case  */
    /*        when bill_cycles.billcycledef_id = 1 and  bill_cycles.dfrom = add_months(to_date('30/06/2009','dd/mm/yyyy')+1,-3) then nvl(inv.netamount,0) + nvl(inv.taxamount,0)  */
    /*        when bill_cycles.billcycledef_id = 2 and  bill_cycles.dfrom = add_months(to_date('30/06/2009','dd/mm/yyyy')+15,-4) then nvl(inv.netamount,0) + nvl(inv.taxamount,0)  */
    /*       else 0  */
    /*      End Month4,  */
    /*      Case  */
    /*        when bill_cycles.billcycledef_id = 1 and  bill_cycles.dfrom = add_months(to_date('30/06/2009','dd/mm/yyyy')+1,-2) then nvl(inv.netamount,0) + nvl(inv.taxamount,0)  */
    /*        when bill_cycles.billcycledef_id = 2 and  bill_cycles.dfrom = add_months(to_date('30/06/2009','dd/mm/yyyy')+15,-3) then nvl(inv.netamount,0) + nvl(inv.taxamount,0)  */
    /*       else 0  */
    /*      End Month5,  */
         Case
          when bill_cycles.billcycledef_id = 1 and  bill_cycles.dfrom = add_months(to_date('30/06/2009','dd/mm/yyyy')+1,-1) then nvl(inv.netamount,0) + nvl(inv.taxamount,0)
          when bill_cycles.billcycledef_id = 2 and  bill_cycles.dfrom = add_months(to_date('30/06/2009','dd/mm/yyyy')+15,-2) then nvl(inv.netamount,0) + nvl(inv.taxamount,0)
         else 0
        End Month6,
        act.creditlimit Credit_Limit,
        (select -sum(fin.ctuamount+fin.dtuamount) 
               from tytan.tfindocparts fin,
                    tytan.tfindoctypes tp
               where fin.doctype_id = tp.id and
                   fin.cpartyaccount_id = act.account_id and
                  (shortname like 'Deposit%' or
                   shortname like 'External Deposit%' or
                   shortname like 'Interest on Deposit%' or
                   shortname like 'Return Deposit%') ) Deposit,
      
       (Select act_tp2.description
        from tytan.tamcpartyaccountd act2,
               tytan.tamcpartyaccounttypes act_tp2
        where act2.accountdaccount_id = inv.cpartyaccount_id and
                  act2.accounttype_id = act_tp2.id and
                 act2.isvalid = 'Y' and fin.issuedate between nvl(act2.dfrom,sysdate-9999) and nvl(act2.dto,sysdate+9999)) acnt_tp2
    
                  
        FROM tytan.tamcpartyaccountd act, 
             tytan.tamcontractingpartyd pty,
             tytan.tamcpartyaccounttypes act_tp,
             tytan.tfininvoiceitems inv, 
             tytan.tfindocs fin, 
             tytan.tcdbillcycles bill_cycles,
             tytan.tamcontactusage clink,
             tytan.tamcontacts addr
        WHERE act.cparty_id = pty.cparty_id 
          --AND act.accounttype_id in (3,8,16)
          AND inv.cpartyaccount_id = act.account_id
          AND act.accounttype_id = act_tp.id 
          AND inv.doc_id = fin.id
          AND fin.billcycleno = bill_cycles.billcycleno
          AND fin.billcycledef_id = bill_cycles.billcycledef_id
          AND act.account_id = clink.cpartyaccount_id (+)
          AND clink.contact_id = addr.id
          AND clink.isvalid = 'Y' and clink.usagelevel = 'A' 
          AND clink.usagetype_id is not null and clink.dto is null
          AND inv.type = 'T'
          AND fin.type_id != 8 /*  inv.cparty_id = 90718 --73372  */
          AND bill_cycles.dfrom between add_months(to_date('30/06/2009','dd/mm/yyyy')+15,-7) and add_months(to_date('30/06/2009','dd/mm/yyyy')+1,-1)
          --AND bill_cycles.dfrom in ('1-Jun-2009','15-May-2009')
    order by pty.fullname, act.name      
    )mnth
    GROUP BY District,City,Location,Add_Info,customer_name,account,Credit_Limit,Deposit,acnt_tp,acnt_tp2

Posting Permissions

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