Improving SQL Code
- by jeremib
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
