如何将我的查询合并到单个查询(或者可以是存储的过程..) [英] How to merge my queries to a single query (or may be a stored proc..)

问题描述

下面是我要为四个属性计算值的查询.

Below are the queries in which I am calculating values for four attributes..

1.
Here I am calculating the values for chrg_orig


    with ep as
      (select emp_cd,
        emp_num,
        to_char(pay_dt,'yyyymm') as pay_month,
        max(code),
        max(bill) as bill,
        max(chrg)  as charge,
        sum( nvl(pay_1,0)) sum_pay1,
        sum(nvl(pay_2, 0)) sum_pay2,
        (chrg_mon*22)+ (chrg_week*5)+ chrg_day as days,
      from emp_payments
      where emp_cd in ('HP','2000')
      and code     in ('X','Y','Z')
      group by emp_cd,
        emp_num,
        to_char(pay_dt,'yyyymm'),
        code
      )
    select emp_cd,
      emp_num,
      pay_month,
      max(code),
      sum(bill)
      case when sum(days)=22 then sum(chrg) else  round((round(sum(chrg)/sum(days),4)*22),2) end as chrg_orig
    from ep
    where chrg <>0
    group by 
    emp_cd,
    emp_num,
    paymonth

----------------------------------------------------------------------------------------
2.
Here I am calculating the values for rate_chrg

     with ep as
      (select emp_cd,
        emp_num,
        to_char(pay_dt,'yyyymm') as pay_month,
        code,
        max(bill) as bill,
        max(chrg)  as charge,
        sum( nvl(pay_1,0)) sum_pay1,
        sum(nvl(pay_2, 0)) sum_pay2,
        (chrg_mon*22)+ (chrg_week*5)+ chrg_day as days,
      from emp_payments
      where emp_cd in ('HP','2000')
      and code     in ('X','Y','Z')
      group by emp_cd,
        emp_num,
        to_char(pay_dt,'yyyymm'),
        code
      )
    (
    select a.emp_cd,a.emp_num,a.key,b.rate as rate_chrg from 
        (select emp_cd,emp_num,to_char(pay_dt,'yyyymm') as key,max(invc_dt) as invc_dt from ep
            where code in ('X','Y') and rate <> 0
            group by emp_cd,emp_num,to_char(invc_dt,'yyyymm')) a,

        (select emp_cd,emp_num,to_char(pay_dt,'yyyymm') as key,invc_dt,rate from ep
            where code in ('X','Y') and rate <> 0) b
    where a.emp_cd = b.emp_cd
    and a.emp_num = b.emp_num
    and a.key = b.key
    and a.invc_dt = b.invc_dt
    )



-----------------------------------------------------------------------------------------
3.
Here I am calculating the values for bonus_chrg




    with ep as
      (select emp_cd,
        emp_num,
        to_char(pay_dt,'yyyymm') as pay_month,
        code,
        max(bill) as bill,
        max(chrg)  as charge,
        sum( nvl(pay_1,0)) sum_pay1,
        sum(nvl(pay_2, 0)) sum_pay2,
        (chrg_mon*22)+ (chrg_week*5)+ chrg_day as days,
      from emp_payments
      where emp_cd in ('HP','2000')
      and code     in ('X','Y','Z')
      group by emp_cd,
        emp_num,
        to_char(pay_dt,'yyyymm'),
        code
      )
    (
    select a.emp_cd,a.emp_num,a.key,b.rate as bonus_chrg from 
        (select emp_cd,emp_num,to_char(pay_dt,'yyyymm') as key,max(invc_dt) as invc_dt from ep
            where code in ('Z') and rate <> 0
            group by emp_cd,emp_num,to_char(invc_dt,'yyyymm')) a,

        (select emp_cd,emp_num,to_char(pay_dt,'yyyymm') as key,invc_dt,rate from ep
            where code in ('Z') and rate <> 0) b
    where a.emp_cd = b.emp_cd
    and a.emp_num = b.emp_num
    and a.key = b.key
    and a.invc_dt = b.invc_dt
    )

------------------------------------------------------------------------------------------
4.
Here I am calculating the values for comp_days



    with ep as
      (select emp_cd,
        emp_num,
        to_char(pay_dt,'yyyymm') as pay_month,
        code,
        max(bill) as bill,
        max(chrg)  as charge,
        sum( nvl(pay_1,0)) sum_pay1,
        sum(nvl(pay_2, 0)) sum_pay2,
        (chrg_mon*22)+ (chrg_week*5)+ chrg_day as days,
      from emp_payments
      where emp_cd in ('HP','2000')
      and code     in ('X','Y','Z')
      group by emp_cd,
        emp_num,
        to_char(pay_dt,'yyyymm'),
        code
      )
    (
    select emp_cd,emp_num,paymonth as key,sum(days) as comp_days from ep
    where code in ('X','Y')
    group by emp_cd,emp_num,key
    )

--------------------------------------------------

到目前为止，我已经将所有这些单独的查询带入ETL工具中，并使用chrg_orig作为驱动表进行了左外部联接，并为不匹配的列分配了零. 但是我想我需要有一个完整的外部联接(我无法在工具中实现该联接)，并为所有不匹配的值分配零.

What I have done so far is, I have taken all these separate queries into ETL tool and did a left outer join with chrg_orig as the driving table and assigned zeros for non-matching columns. But I think I need to have a full outer join(which I was not able to implement it in the tool) and assign zeros for all non matching values.

我想将这些查询合并为一个查询..什么是解决它的最佳方法? 所有的输入和建议都很有价值.谢谢.

I want to club these queries into a single query..what is the best approach to solve it? All inputs and suggestions are valuable.Thanks..

推荐答案

您可以创建多个一个带有CTE的虚拟表，方法是用逗号分隔CTE定义.此外，CTE可以引用其他CTE.

You can create more than one virtual table with a CTE by separating the CTE definitions with commas. Futhermore, CTEs can refer to other CTEs.

假设所有这些查询中的ep相同，则可以执行以下操作:

Assuming ep is the same across all these queries, you can do something like this:

 with ep as
    (select emp_cd,
      emp_num,
      to_char(pay_dt,'yyyymm') as pay_month,
      max(code),
      max(bill) as bill,
      max(chrg)  as charge,
      sum( nvl(pay_1,0)) sum_pay1,
      sum(nvl(pay_2, 0)) sum_pay2,
      (chrg_mon*22)+ (chrg_week*5)+ chrg_day as days,
    from emp_payments
    where emp_cd in ('HP','2000')
    and code     in ('X','Y','Z')
    group by emp_cd,
      emp_num,
      to_char(pay_dt,'yyyymm'),
      code
    ),
 chrg_orig (<field names here>) as (
  select emp_cd,
    emp_num,
    pay_month,
    max(code),
    sum(bill)
    case when sum(days)=22 then sum(chrg) else  round((round(sum(chrg)/sum(days),4)*22),2) end as chrg_orig
  from ep
  where chrg <>0
  group by 
  emp_cd,
  emp_num,
  paymonth
),
rate_chrg (<field names here>) as (
  select a.emp_cd,a.emp_num,a.key,b.rate as rate_chrg from 
      (select emp_cd,emp_num,to_char(pay_dt,'yyyymm') as key,max(invc_dt) as invc_dt from ep
          where code in ('X','Y') and rate <> 0
          group by emp_cd,emp_num,to_char(invc_dt,'yyyymm')) a,

      (select emp_cd,emp_num,to_char(pay_dt,'yyyymm') as key,invc_dt,rate from ep
          where code in ('X','Y') and rate <> 0) b
  where a.emp_cd = b.emp_cd
  and a.emp_num = b.emp_num
  and a.key = b.key
  and a.invc_dt = b.invc_dt
  ),
bonus_chrg (<field names here>) as (
  select a.emp_cd,a.emp_num,a.key,b.rate as bonus_chrg from 
      (select emp_cd,emp_num,to_char(pay_dt,'yyyymm') as key,max(invc_dt) as invc_dt from ep
          where code in ('Z') and rate <> 0
          group by emp_cd,emp_num,to_char(invc_dt,'yyyymm')) a,

      (select emp_cd,emp_num,to_char(pay_dt,'yyyymm') as key,invc_dt,rate from ep
          where code in ('Z') and rate <> 0) b
  where a.emp_cd = b.emp_cd
  and a.emp_num = b.emp_num
  and a.key = b.key
  and a.invc_dt = b.invc_dt
  ),
comp_days (<field names here>) as (
  select emp_cd,emp_num,paymonth as key,sum(days) as comp_days from ep
  where code in ('X','Y')
  group by emp_cd,emp_num,key
  )
SELECT *
FROM ep
LEFT OUTER JOIN chrg_orig
  ON <JOIN CONDITION>
LEFT OUTER JOIN rate_chrg
  ON <JOIN CONDITION>
LEFT OUTER JOIN bonus_chrg
  ON <JOIN CONDITION>
LEFT OUTER JOIN comp_days
  ON <JOIN CONDITION>

这篇关于如何将我的查询合并到单个查询(或者可以是存储的过程..)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！