Question

I m using Pervasive SQL. I have the following UNION of mulitple SQL statements. Is there a way to clean this up, especially the Pay Date an the Loc No fields that are selected in each statement. Is there a way to pull this out and have only one place to need to change those two fields?

(
    SELECT 
     23400  as Gl_Number,
        y.Plan as Description,
        0 as Hours,
        ROUND(SUM(Ee_Curr),2) as Debit,
        0 as Credit
    FROM "PR_YLOC" y
    LEFT JOIN PR_SUMM s ON (s.Summ_No = y.Summ_No)
    WHERE y.Loc_No = 1041
    AND s.Pay_Date =  2010-04-02 
    AND y.Code IN (100, 105, 110)
    AND y.Type = 3
    GROUP BY y.Plan
) UNION (
    SELECT 
     72000  as Gl_Number,
        y.Plan,
        0,
        ROUND(SUM(Er_Curr),2),
        0
    FROM "PR_YLOC" y
    LEFT JOIN PR_SUMM s ON (s.Summ_No = y.Summ_No)
    WHERE y.Loc_No = 1041
    AND s.Pay_Date =  2010-04-02 
    AND y.Code IN (100, 105, 110)
    AND y.Type = 3
    GROUP BY y.Plan
) UNION (
SELECT  24800 ,
       c.Plan,
       0,
       ROUND(SUM(Ee_Amt),2),
       0
    FROM "PR_CDED" c WHERE Pay_Date =  2010-04-02  AND Loc_No = 1041 AND Code = 100
    GROUP BY c.Plan
) UNION (
SELECT  24800 ,
       c.Plan,
       0,
       0,
       ROUND(SUM(Ee_Amt),2)
    FROM "PR_CDED" c WHERE Pay_Date =  2010-04-02  AND Loc_No = 1041 AND Code = 115
    GROUP BY c.Plan
) UNION (
SELECT  24150 ,
       c.Plan,
       0,
       0,
       ROUND(SUM(Ee_Amt),2)    
    FROM "PR_CDED" c WHERE Pay_Date =  2010-04-02  AND Loc_No = 1041 AND Code = 241
    GROUP BY c.Plan
) UNION (
SELECT  24150 ,
       c.Plan,
       0,
       ROUND(SUM(Ee_Amt),2),
       0
    FROM "PR_CDED" c WHERE Pay_Date =  2010-04-02  AND Loc_No = 1041 AND Code = 239
    GROUP BY c.Plan
) UNION (
SELECT  24120 ,
       c.Plan,
       0,
       ROUND(SUM(Ee_Amt),2),
       0
    FROM "PR_CDED" c WHERE Pay_Date =  2010-04-02  AND Loc_No = 1041 AND Code = 230
    GROUP BY c.Plan
) UNION (
SELECT  24100 ,
       c.Plan,
       0,
       ROUND(SUM(Ee_Amt),2),
       0
    FROM "PR_CDED" c WHERE Pay_Date =  2010-04-02  AND Loc_No = 1041 AND Code = 225
    GROUP BY c.Plan
) UNION (
SELECT  23800 ,
       c.Plan,
       0,
       ROUND(SUM(Ee_Amt),2),
       0
    FROM "PR_CDED" c WHERE Pay_Date =  2010-04-02  AND Loc_No = 1041 AND Code = 245
    GROUP BY c.Plan
) UNION (
select m.Def_Dept as Gl_Number, t.Short_Desc,
    (SELECT SUM(Hours) FROM pr_earn en WHERE en.Loc_No = e.Loc_No AND en.Emp_No = e.Emp_No AND en.Pay_Date = e.Pay_Date AND en.Pay_Code = e.Pay_Code) as Hours,
    (SELECT SUM(Pay_Amt) FROM pr_earn en WHERE en.Loc_No = e.Loc_No AND en.Emp_No = e.Emp_No AND en.Pay_Date = e.Pay_Date AND en.Pay_Code = e.Pay_Code) as Debit,
    0
from pr_earn e
left join pr_mast m on (e.Loc_No = m.Loc_No and e.Emp_No = m.Emp_No)
left join pr_ptype t ON (t.Code = e.Pay_Code)
where e.loc_no = 1041 and e.pay_date =  2010-04-02 
group by m.Def_Dept, t.Short_Desc
)

Thanks

Answer 1

Could you replace the middle set of SELECT statements with something like this?

) UNION (
SELECT CASE Code
         WHEN 100 THEN  24800 
         WHEN 115 THEN  24800 ,
         WHEN 241 THEN  24150 
         WHEN 239 THEN  24150 ,
         WHEN 230 THEN  24120 ,
         WHEN 225 THEN  24100 
         WHEN 245 THEN  23800 ,
       END,
       c.Plan,
       0,
       ROUND(SUM(Ee_Amt),2),
       0
    FROM "PR_CDED" c WHERE Pay_Date =  2010-04-02  AND Loc_No = 1041 AND Code IN (100, 115, 241, 239, 230, 225, 245)
    GROUP BY c.Plan
) UNION (

That s a T-SQL CASE statement, but I m sure Oracle PL/SQL has something similar.

Answer 2

Instead of many selects with almost the same filter criteria (difference at "AND Code = ..."), you could replace with only one select with filter criteria AND Code IN (..., ..., ...).

Answer 3

Seems like you re trying to have more maintainability without affecting performance. I don t know this platform, but I would guess that you could move one where clause to the end, with your Unions as a subquery, and that the query optimzer might be able to figure out to apply the where conditions in each unioned set.

Answer 4

You can use a case statement for this. E.g.:

SELECT  
 23400  as Gl_Number, 
    y.Plan as Description, 
    0 as Hours, 
    ROUND(SUM(Ee_Curr),2) as Debit, 
    0 as Credit 
FROM "PR_YLOC" y 
LEFT JOIN PR_SUMM s ON (s.Summ_No = y.Summ_No) 
WHERE y.Loc_No = 1041 
AND s.Pay_Date =  2010-04-02  
AND y.Code IN (100, 105, 110) 
AND y.Type = 3 
GROUP BY y.Plan 
UNION
SELECT  
 72000  as Gl_Number, 
    y.Plan, 
    0, 
    ROUND(SUM(Er_Curr),2), 
    0 
FROM "PR_YLOC" y 
LEFT JOIN PR_SUMM s ON (s.Summ_No = y.Summ_No) 
WHERE y.Loc_No = 1041 
AND s.Pay_Date =  2010-04-02  
AND y.Code IN (100, 105, 110) 
AND y.Type = 3 
GROUP BY y.Plan 
UNION
SELECT case Code 
            when Code in (100,115) then 24800 
            when Code in (241, 239) then 24150 
            when Code = 230 then 24120 
            when Code = 225 then 24100 
            when Code = 245 then 23800 
        end, 
       c.Plan, 
       0, 
       ROUND(SUM(Ee_Amt),2), 
       0 
    FROM "PR_CDED" c WHERE Pay_Date =  2010-04-02  AND Loc_No = 1041 AND Code in (100,115,241,239,230,225,245)
    GROUP BY c.Plan 
select m.Def_Dept as Gl_Number, t.Short_Desc, 
    (SELECT SUM(Hours) FROM pr_earn en WHERE en.Loc_No = e.Loc_No AND en.Emp_No = e.Emp_No AND en.Pay_Date = e.Pay_Date AND en.Pay_Code = e.Pay_Code) as Hours, 
    (SELECT SUM(Pay_Amt) FROM pr_earn en WHERE en.Loc_No = e.Loc_No AND en.Emp_No = e.Emp_No AND en.Pay_Date = e.Pay_Date AND en.Pay_Code = e.Pay_Code) as Debit, 
    0 
from pr_earn e 
left join pr_mast m on (e.Loc_No = m.Loc_No and e.Emp_No = m.Emp_No) 
left join pr_ptype t ON (t.Code = e.Pay_Code) 
where e.loc_no = 1041 and e.pay_date =  2010-04-02  
group by m.Def_Dept, t.Short_Desc

友情链接