4 Replies Latest reply on Nov 3, 2015 1:30 PM by thatJeffSmith-Oracle

    Partial Data Results Returning

    e56fbb8f-420f-4c83-91a0-843046a9c3b2

      Greetings!

       

      Suddenly queries that ran without issue and continue to work for other users is now returning partial data. For example numerical data is dropping from the results. I have confirmed the fields are not NULL. My company Helpdesk is unable to figure it out. What are we missing? Please any guidance is needed as I cannot execute any queries with numerical data. I have tested in SQL Plus - no issue. Another colleague has used my pc/SQL Developer without issue. My role has been confirmed to match my colleague.

       

      Simple Query:

      Pay_Amt column returns primarily blank (2 out of 1000 have data)

      Select 

      a.p5429_trans_id,

      b.con_id,

      b.sup_id as "TCSS_SUP_ID",

      service_fy,

      Service_ap,

      service_type_cd,

      code_desc as "Service Description",

      to_char(pay_amt) as "Invoice Amt",

      b.run_date as "Invoice GL Date",

      b.run_id

      From scr.p5429_pay_unit_rate_details a JOIN pay_unit_rate b

      on a.p5429_trans_id = b.transaction_id

      WHERE TRUNC(b.run_date)= '21-OCT-2015'

      and b.processed_status = 'T'

      order by a.p5429_trans_id desc

      ;

      More Extensive Query:

      Numerical Data returns primarily blank (2 out of 1000 have data).

      SELECT DISTINCT SYSDATE "REPORT DATE",

              h.loc_cd "LOC_CD",

              l.loc_desc TCMT,         

              ( SELECT code_desc

                  FROM tcss.tcss_lov_code

                 WHERE text_key = 'LOC_CD'

                   AND lov_cd = ( SELECT substr(approval_process_id,1,2)

                                    FROM scr.scr_hdr

                                   WHERE scr_id = h.scr_id ) ) "OPER AREA",  

              h.con_id "CONTRACT NO",

              h.scr_id "SCR ID", 

              h.scr_status_cd "SCR STATUS CD",

              v.con_act_desc "TCSS ACTIVITY",

              v.eff_date "ACTIVITY EFF DATE",

              v.rec_status_cd "ACTIVITY STATUS",

              v.order_in_date "ORDER IN",

              b.cs_desc "TCSS CS",

              ( SELECT code_desc

                  FROM tcss_lov_code 

                 WHERE text_key = 'PAY_TYPE_CD' 

                   AND lov_cd = b.pay_type_cd ) "TCSS CS PAY TYPE",

             to_number(d.ann_cost) "TCSS COST (LINE20)",

              tup.user_first_name||' '|| tup.user_last_name "TCSS ACTIVITY CREATOR",

              ( SELECT MAX(eff_date)

                  FROM tcss.contract

                 WHERE con_id = v.con_id

                   AND eff_date < v.eff_date

                   AND v.eff_date IS NOT NULL

                   AND v.oa_id NOT IN (1,2,3,81) ) "PREV TCSS EFF DATE",

              ( SELECT ann_cost

                  FROM tcss.cost_data

                 WHERE con_id = v.con_id

                   AND cost_item_cd = '20'

                   AND cs_id = b.cs_id

                   AND eff_date = 

                   ( SELECT MAX(eff_date)

                       FROM tcss.contract

                      WHERE con_id = v.con_id

                        AND eff_date < v.eff_date

                        AND v.eff_date IS NOT NULL

                        AND d.ann_cost IS NOT NULL

                        AND v.oa_id NOT IN (1,2,3,81 ) ) ) "PREV TCSS COST",   

              d.ann_cost -

               ( SELECT ann_cost

                   FROM tcss.cost_data

                  WHERE con_id = v.con_id

                    AND cost_item_cd = '20'

                    AND cs_id = b.cs_id

                    AND eff_date = 

                    ( SELECT MAX(eff_date)

                        FROM tcss.contract

                       WHERE con_id = v.con_id

                         AND eff_date < v.eff_date

                         AND v.eff_date IS NOT NULL

                         AND d.ann_cost IS NOT NULL

                         AND v.oa_id NOT IN (1,2,3,81 ) ) ) "TCSS COST CHANGE",  

              ( SELECT code_desc

                  FROM tcss.tcss_lov_code

                 WHERE text_key = 'FUEL_PROGRAM_CD'

                   AND lov_cd = c.fuel_program_ind ) "FUEL PROGRAM",

              f.fuel_cost "TCSS FUEL COST",

              f.ann_fuel_units "TCSS GALLONS",

              ( SELECT fuel_cost

                  FROM tcss.fuel_cost

                 WHERE con_id = f.con_id

                   AND cs_id = f.cs_id

                   AND eff_date = ( SELECT MAX(eff_date)

                                      FROM tcss.contract

                                     WHERE con_id = v.con_id

                                       AND eff_date < v.eff_date )

                   AND  ( SELECT count (fuel_cost)       -- exclude fuel_cost records where we have > 1 record associated with a con_id

                            FROM tcss.fuel_cost

                           WHERE con_id = f.con_id

                             AND cs_id = f.cs_id

                             AND eff_date = ( SELECT MAX(eff_date)

                                                FROM tcss.contract

                                               WHERE con_id = v.con_id

                                                 AND eff_date < v.eff_date ))<2 ) "PREV TCSS FUEL COST",

              f.fuel_cost -

              ( SELECT fuel_cost

                   FROM tcss.fuel_cost

                  WHERE con_id = f.con_id

                    AND cs_id = f.cs_id

                    AND eff_date = ( SELECT MAX(eff_date)

                                       FROM tcss.contract

                                      WHERE con_id = v.con_id

                                        AND eff_date < v.eff_date )

                    AND  ( SELECT count (fuel_cost)       -- exclude fuel_cost records where we have > 1 record associated with a con_id

                             FROM tcss.fuel_cost

                            WHERE con_id = f.con_id

                              AND cs_id = f.cs_id

                              AND eff_date = ( SELECT MAX(eff_date)

                                                 FROM tcss.contract

                                                WHERE con_id = v.con_id

                                                  AND eff_date < v.eff_date ))<2 )  "TCSS FUEL COST CHANGE",                      

              ( SELECT ann_fuel_units

                   FROM tcss.fuel_cost

                  WHERE con_id = f.con_id

                    AND cs_id = f.cs_id

                    AND eff_date = ( SELECT MAX(eff_date)

                                       FROM tcss.contract

                                      WHERE con_id = v.con_id

                                        AND eff_date < v.eff_date )

                    AND  ( SELECT count (fuel_cost)       -- exclude fuel_cost records where we have > 1 record associated with a con_id

                             FROM tcss.fuel_cost

                            WHERE con_id = f.con_id

                              AND cs_id = f.cs_id

                              AND eff_date = ( SELECT MAX(eff_date)

                                                 FROM tcss.contract

                                                WHERE con_id = v.con_id

                                                  AND eff_date < v.eff_date ))<2 ) "PREV TCSS GALLONS",

              f.ann_fuel_units -

              ( SELECT ann_fuel_units

                  FROM tcss.fuel_cost

                 WHERE con_id = f.con_id

                   AND cs_id = f.cs_id

                   AND eff_date = ( SELECT MAX(eff_date)

                                      FROM tcss.contract

                                     WHERE con_id = v.con_id

                                       AND eff_date < v.eff_date )

                   AND  ( SELECT count (fuel_cost)       -- exclude fuel_cost records where we have > 1 record associated with a con_id

                            FROM tcss.fuel_cost

                           WHERE con_id = f.con_id

                             AND cs_id = f.cs_id

                             AND eff_date = ( SELECT MAX(eff_date)

                                                FROM tcss.contract

                                               WHERE con_id = v.con_id

                                                 AND eff_date < v.eff_date ))<2 ) "TCSS GALLONS CHANGE",                     

      /*  Miles  */

              b.cs_ann_miles "TCSS CS MILES",

              ( SELECT cs_ann_miles

                  FROM tcss.cost_basis

                 WHERE con_id = f.con_id

                   AND cs_id = f.cs_id

                   AND eff_date = ( SELECT MAX(eff_date)

                                      FROM tcss.contract

                                     WHERE con_id = v.con_id

                                       AND eff_date < v.eff_date ) ) "PREV TCSS CS MILES",

              b.cs_ann_miles -

              ( SELECT cs_ann_miles

                  FROM tcss.cost_basis

                 WHERE con_id = f.con_id

                   AND cs_id = f.cs_id

                   AND eff_date = ( SELECT MAX(eff_date)

                                      FROM tcss.contract

                                     WHERE con_id = v.con_id

                                       AND eff_date < v.eff_date ) ) "TCSS CS MILES CHANGE",

      /*  Hours  */

              b.cs_ann_hours "TCSS CS HOURS",

              ( SELECT cs_ann_hours

                  FROM tcss.cost_basis

                 WHERE con_id = f.con_id

                   AND cs_id = f.cs_id

                   AND eff_date = ( SELECT MAX(eff_date)

                                      FROM tcss.contract

                                     WHERE con_id = v.con_id

                                       AND eff_date < v.eff_date ) ) "PREV TCSS CS HOURS",

              b.cs_ann_hours -

              ( SELECT cs_ann_hours

                  FROM tcss.cost_basis

                 WHERE con_id = f.con_id

                   AND cs_id = f.cs_id

                   AND eff_date = ( SELECT MAX(eff_date)

                                      FROM tcss.contract

                                     WHERE con_id = v.con_id

                                       AND eff_date < v.eff_date ) ) "TCSS CS HOURS CHANGE",

              ( SELECT cs_cost_net

                  FROM scr.cost_estimates

                 WHERE scr_id = h.scr_id

                   AND cs_id = b.cs_id ) "SCR ESTIMATE",

              ( SELECT svc_desc

                  FROM service_description 

                 WHERE svc_desc_cd = c.svc_desc_cd 

                   AND svc_type_grp_cd = c.svc_type_grp_cd ) "CON SERVICE TYPE", 

              ( SELECT code_desc FROM tcss_lov_code 

                 WHERE text_key = 'RT_TYPE_CD' 

                   AND lov_cd = ( SELECT rt_type_cd

                                    FROM service_type_grouping 

                                   WHERE svc_type_grp_cd = c.svc_type_grp_cd ) ) "ROUTE TYPE",

              CASE h.scr_status_cd   

                  WHEN 'I' THEN 'IN PROGRESS'   

                  WHEN 'P' THEN 'PENDING APPROVAL'   

                  WHEN 'A' THEN 'APPROVED (Sent to TCSS)'   

                  WHEN 'S' THEN 'TCSS ACTIVITY STARTED'  

                  WHEN 'O' THEN 'ORDERED IN'

                  WHEN 'C' THEN 'CANCELLED'

              END "SCR STATUS",

              h.term_ind "CURRENT / RENEWAL",

              j.reason_cd,

              ( SELECT reason_desc

                  FROM scr.reason_code

                 WHERE reason_cd = j.reason_cd ) "REASON DESCRIPTION",

              ( SELECT first_name||' '||last_name

                  FROM scr.user_profile   

                 WHERE user_id = h.created_by ) "REQUESTOR",   

              h.date_created "DATE CREATED", 

              ( SELECT MAX(scr_status_date)

                  FROM scr.scr_status_detail   

                 WHERE scr_id = h.scr_id

                   AND scr_status_cd = 'A' ) "LAST FINAL APPROV",

              round((( SELECT MAX(scr_status_date)

                         FROM scr.scr_status_detail   

                        WHERE scr_id = h.scr_id

                          AND scr_status_cd = 'A' ) - h.date_created),0) "#DAYS: CREATE, LAST APPR",

              ( SELECT MAX(scr_status_date)

                  FROM scr.scr_status_detail   

                 WHERE scr_id = h.scr_id

                   AND scr_status_cd = 'S'  ) "LAST TCSS ACT STARTED",

              ( SELECT round(( SELECT MAX(scr_status_date)

                                 FROM scr.scr_status_detail   

                                WHERE scr_id = h.scr_id

                                  AND scr_status_cd = 'S' ) - ( SELECT MAX(scr_status_date)

                                                                  FROM scr.scr_status_detail   

                                                                 WHERE scr_id = h.scr_id

                                                                   AND scr_status_cd = 'A' ),0)

                 FROM dual

                WHERE ( SELECT MAX(scr_status_date)

                          FROM scr.scr_status_detail   

                         WHERE scr_id = h.scr_id

                           AND scr_status_cd = 'S' ) >=

                           ( SELECT MAX(scr_status_date)

                               FROM scr.scr_status_detail   

                              WHERE scr_id = h.scr_id

                                AND scr_status_cd = 'A' ) ) "#DAYS: LAST APPR, ACT STARTED",

              round(v.order_in_date -

                    ( SELECT MAX(scr_status_date)

                        FROM scr.scr_status_detail   

                       WHERE scr_id = h.scr_id

                         AND scr_status_cd = 'S' ),0) "#DAYS: ACT STARTED, ORDER IN"

        FROM scr.scr_hdr h, scr.contract c, scr.justification j, tcss.contract_activity v,

             tcss.cost_basis b, tcss.cost_data d, tcss.fuel_cost f, tcss.location l, tcss.tcss_user_profile tup

      WHERE h.date_created BETWEEN to_date('05/01/2013', 'mm/dd/yyyy') AND SYSDATE

         AND c.scr_id = h.scr_id

         AND c.scr_id = j.scr_id

         AND v.scr_id(+) = h.scr_id AND v.oa_id(+) NOT IN (36,75,90)

         AND b.con_id(+) = v.con_id AND b.eff_date(+) = v.eff_date

         AND d.con_id(+) = b.con_id AND d.eff_date(+) = b.eff_date AND d.cs_id(+) = b.cs_id AND d.cost_item_cd(+) = '20' AND d.rec_status_cd(+) = 'P'

         AND f.con_id(+) = b.con_id AND f.eff_date(+) = b.eff_date AND f.cs_id(+) = b.cs_id AND f.rec_status_cd(+) = 'P'

         AND h.loc_cd = l.loc_cd

        AND h.loc_cd = '2E'

         AND tup.user_id(+) = v.created_by  

         AND NOT EXISTS ( SELECT 1

                            FROM tcss.contract_activity

                           WHERE con_id = v.con_id

                             AND trunc(eff_date) = trunc(v.eff_date)

                             AND oa_id IN (9,10,11,82) )   

         AND ( SELECT count(1)

                 FROM tcss.fuel_cost

                WHERE con_id = b.con_id

                  AND eff_date = b.eff_date

                  AND cs_id = b.cs_id ) < 2

      UNION

      SELECT DISTINCT SYSDATE "REPORT DATE", 

              h.loc_cd "LOC_CD",

              l.loc_desc TCMT,

              ( SELECT code_desc

                  FROM tcss.tcss_lov_code

                 WHERE text_key = 'LOC_CD'

                   AND lov_cd = ( SELECT substr(approval_process_id,1,2)

                                    FROM scr.scr_hdr

                                   WHERE scr_id = h.scr_id ) ) "OPER AREA",     

              h.con_id "CONTRACT NO",

              h.scr_id "SCR ID", 

              h.scr_status_cd "SCR STATUS CD",

              va.con_act_desc "TCSS ACTIVITY",

              va.eff_date "ACTIVITY EFF DATE",

              va.rec_status_cd "ACTIVITY STATUS",

              va.order_in_date "ORDER IN",

              b.cs_desc "TCSS CS",

              ( SELECT code_desc

                  FROM tcss_lov_code 

                 WHERE text_key = 'PAY_TYPE_CD' 

                   AND lov_cd = b.pay_type_cd ) "TCSS CS PAY TYPE",

              d.ann_cost "TCSS COST (LINE20)",

              tup.user_first_name||' '|| tup.user_last_name "TCSS ACTIVITY CREATOR",        

              NULL "PREV TCSS EFF DATE",

              NULL "PREV TCSS COST",

              NULL "TCSS COST CHANGE",

              ( SELECT code_desc

                  FROM tcss.tcss_lov_code

                 WHERE text_key = 'FUEL_PROGRAM_CD'

                   AND lov_cd = c.fuel_program_ind ) "FUEL PROGRAM",

              f.fuel_cost "TCSS FUEL COST",

              f.ann_fuel_units "TCSS GALLONS",

              NULL "PREV TCSS FUEL COST",

              NULL "TCSS FUEL COST CHANGE",

              NULL "PREV TCSS GALLONS",

              NULL "TCSS GALLONS CHANGE",

              NULL "TCSS CS MILES",

              NULL "PREV TCSS CS MILES",

              NULL "TCSS CS MILES CHANGE",

              NULL "TCSS CS HOURS",

              NULL "PREV TCSS CS HOURS",

              NULL "TCSS CS HOURS CHANGE",

              ( SELECT cs_cost_net

                  FROM scr.cost_estimates

                 WHERE scr_id = h.scr_id

                   AND cs_id = b.cs_id ) "SCR ESTIMATE",

              ( SELECT svc_desc

                  FROM service_description 

                 WHERE svc_desc_cd = c.svc_desc_cd

                   AND svc_type_grp_cd = c.svc_type_grp_cd ) "CON SERVICE TYPE", 

              ( SELECT code_desc

                  FROM tcss_lov_code 

                 WHERE text_key = 'RT_TYPE_CD' 

                   AND lov_cd = ( SELECT rt_type_cd

                                    FROM service_type_grouping 

                                   WHERE svc_type_grp_cd = c.svc_type_grp_cd ) ) "ROUTE TYPE",

              CASE h.scr_status_cd   

                  WHEN 'I' THEN 'IN PROGRESS'   

                  WHEN 'P' THEN 'PENDING APPROVAL'   

                  WHEN 'A' THEN 'APPROVED (Sent to TCSS)'   

                  WHEN 'S' THEN 'TCSS ACTIVITY STARTED'  

                  WHEN 'O' THEN 'ORDERED IN'

                  WHEN 'C' THEN 'CANCELLED'

              END "SCR STATUS",

              h.term_ind "CURRENT / RENEWAL",

              j.reason_cd,

              ( SELECT reason_desc

                  FROM scr.reason_code

                 WHERE reason_cd = j.reason_cd ) "REASON DESCRIPTION",

              ( SELECT first_name||' '||last_name

                  FROM scr.user_profile   

                 WHERE user_id = h.created_by ) "REQUESTOR",   

              h.date_created "DATE CREATED", 

              ( SELECT MAX(scr_status_date)

                  FROM scr.scr_status_detail   

                 WHERE scr_id = h.scr_id

                   AND scr_status_cd = 'A' ) "LAST FINAL APPROV",

              round((( SELECT MAX(scr_status_date)

                         FROM scr.scr_status_detail   

                        WHERE scr_id = h.scr_id

                          AND scr_status_cd = 'A' ) - h.date_created),0) "#DAYS: CREATE, LAST APPR",

              ( SELECT MAX(scr_status_date)

                  FROM scr.scr_status_detail   

                 WHERE scr_id = h.scr_id

                   AND scr_status_cd = 'S'  ) "LAST TCSS ACT STARTED",

              ( SELECT round(( SELECT MAX(scr_status_date)

                                 FROM scr.scr_status_detail   

                                WHERE scr_id = h.scr_id

                                  AND scr_status_cd = 'S' ) - ( SELECT MAX(scr_status_date)

                                                                  FROM scr.scr_status_detail   

                                                                 WHERE scr_id = h.scr_id

                                                                   AND scr_status_cd = 'A' ),0)

                  FROM dual

                 WHERE ( SELECT MAX(scr_status_date)

                           FROM scr.scr_status_detail   

                          WHERE scr_id = h.scr_id

                            AND scr_status_cd = 'S' ) >=

                       ( SELECT MAX(scr_status_date)

                           FROM scr.scr_status_detail   

                          WHERE scr_id = h.scr_id

                            AND scr_status_cd = 'A' ) ) "#DAYS: LAST APPR, ACT STARTED",

              round(va.order_in_date -

                    ( SELECT MAX(scr_status_date)

                        FROM scr.scr_status_detail   

                       WHERE scr_id = h.scr_id

                         AND scr_status_cd = 'S' ),0) "#DAYS: ACT STARTED, ORDER IN"

        FROM  scr.scr_hdr h, scr.contract c, scr.justification j, tcss.contract_activity v,  tcss.contract_activity va,

                 tcss.cost_basis b, tcss.cost_data d, tcss.fuel_cost f, tcss.LOCATION l, tcss.tcss_user_profile tup

      WHERE h.date_created BETWEEN to_date('05/01/2013', 'mm/dd/yyyy') AND SYSDATE

         AND c.scr_id = h.scr_id

         AND c.scr_id = j.scr_id

         AND v.scr_id(+) = h.scr_id AND v.oa_id IN (1,2,3,81)

         AND va.con_id = v.con_id AND trunc(va.eff_date) = trunc(v.eff_date) AND va.oa_id IN (9,10,11,82)          

         AND b.con_id = va.con_id AND b.eff_date = va.eff_date

         AND d.con_id = b.con_id AND d.eff_date = b.eff_date AND d.cost_item_cd = '20' AND d.cs_id = b.cs_id

         AND f.con_id(+) = b.con_id AND f.eff_date(+) = b.eff_date AND f.cs_id(+) = b.cs_id AND f.rec_status_cd(+) = 'P'

         AND h.loc_cd = l.loc_cd 

            AND h.loc_cd = '2E'

         AND tup.user_id(+) = v.created_by  

         AND ( SELECT count(1) FROM tcss.fuel_cost

                WHERE con_id = b.con_id

                  AND eff_date = b.eff_date

                  AND cs_id = b.cs_id ) < 2

      ORDER BY 5,9 DESC,12;