时间戳

首页 > 解决方案 > 如何在此查询中使用 with 语句？

plsql - 如何在此查询中使用 with 语句？

问题描述

在这个查询中，我想使用 with 语句。我有一个计算 A union all B 的子查询，我想将它与 with-statement 一起使用。但是当我使用 with-statement 时，我会遇到“表或视图不存在”的错误。令我惊讶的是，当我用 with-statement 替换第一部分时，它可以正常工作。但是当我更换第二部分时，我遇到了这个错误！！

select 

 deposit.BRNCH_COD||'-'||deposit.DP_TYPE_COD||''||deposit.CUSTOMER_NUM||'- 
 '||deposit.DEPOSIT_SERIAL  AS DEPOSIT_NUMBER,
      deposit.IBAN              AS IBAN,
      deposit.CURRENCY_DESC     AS DEPOSIT_CURRCOD,
      deposit.BRNCH_COD         AS BRNCH_COD,
      MAIN_7.Still_Days                                                                           
AS Still_Lenght,
      to_char(MAIN_7.Startdate, 'yyyy/mm/dd' ,'nls_calendar=persian') AS 
 START_DATE,
      MAIN_7.AMOUNT                                                                               
  AS TOTAL_AMOUNT,
      MAIN_7.TRN_Count                                                                            
AS TRN_Count

      from
      (

      select  Trans_Table.DEPOSIT_KEY                     AS DEPOSIT_KEY,
      Trans_Table.TRN_Start_DATE                  AS Startdate,
      MAX(Active_Time_Table.EFFECTIVE_DATE)       AS Lastdate,
      H.PASSIVE_DAYS                    AS Still_Days,
      SUM(Active_Time_Table.AMOUNT)               AS AMOUNT,
      Count(Active_Time_Table.AMOUNT)             AS TRN_Count
      from
      (
      Select F.DEPOSIT_KEY,
      SUM (F.AMOUNT)         AS TRN_AMOUNT,
      MIN (F.EFFECTIVE_DATE) AS TRN_Start_DATE
      from
      (
      A
      union all
      B
      )F
      Group by (F.DEPOSIT_KEY)
      Having ( SUM (F.AMOUNT) >10000000000)
      )Trans_Table


      inner join
     H
      on (Trans_Table.DEPOSIT_KEY = H.DEPOSIT_KEY and 
       Trans_Table.TRN_Start_DATE-1 = H.EFFECTIVE_DATE)

      inner join
      (

      A
      union all
      B
      )Active_Time_Table
      on (Trans_Table.DEPOSIT_KEY = Active_Time_Table.DEPOSIT_KEY and 
      Active_Time_Table.EFFECTIVE_DATE - Trans_Table.TRN_Start_DATE< 4 and 
      Active_Time_Table.EFFECTIVE_DATE - Trans_Table.TRN_Start_DATE>=0)

      group by ( Trans_Table.DEPOSIT_KEY , 
      Trans_Table.TRN_Start_DATE,H.PASSIVE_DAYS)
      Having  (SUM(Active_Time_Table.AMOUNT)) > 10000000000
      )MAIN_7


      inner join dimamldeposit deposit
      on deposit.DEPOSIT_KEY = MAIN_7.DEPOSIT_KEY



       ***********************************************************


   with rep as
  (A union all B)
    select

      deposit.BRNCH_COD||'-'||deposit.DP_TYPE_COD||'- 
    '||deposit.CUSTOMER_NUM||'-'||deposit.DEPOSIT_SERIAL  AS DEPOSIT_NUMBER,
      deposit.IBAN              AS IBAN,
      deposit.CURRENCY_DESC     AS DEPOSIT_CURRCOD,
      deposit.BRNCH_COD         AS BRNCH_COD,
      MAIN_7.Still_Days                                                                           AS Still_Lenght,
      to_char(MAIN_7.Startdate, 'yyyy/mm/dd' ,'nls_calendar=persian') AS START_DATE,
      MAIN_7.AMOUNT                                                                               AS TOTAL_AMOUNT,
      MAIN_7.TRN_Count                                                                            AS TRN_Count
      from
      (
      select  Trans_Table.DEPOSIT_KEY                     AS DEPOSIT_KEY,
      Trans_Table.TRN_Start_DATE                  AS Startdate,
      MAX(rep.EFFECTIVE_DATE)       AS Lastdate,
      H.PASSIVE_DAYS                    AS Still_Days,
      SUM(rep.AMOUNT)               AS AMOUNT,
      Count(rep.AMOUNT)             AS TRN_Count
      from
      (
      Select rep.DEPOSIT_KEY,
      SUM (rep.AMOUNT)         AS TRN_AMOUNT,
      MIN (rep.EFFECTIVE_DATE) AS TRN_Start_DATE
      from
     rep
      Group by (rep.DEPOSIT_KEY)
      Having ( SUM (rep.AMOUNT) >10000000000)
      )Trans_Table
      inner join
     H
      on (Trans_Table.DEPOSIT_KEY = H.DEPOSIT_KEY and Trans_Table.TRN_Start_DATE-1 = H.EFFECTIVE_DATE)

      inner join
      rep rep
      on (Trans_Table.DEPOSIT_KEY = rep.DEPOSIT_KEY and rep.EFFECTIVE_DATE - Trans_Table.TRN_Start_DATE< 4 and rep.EFFECTIVE_DATE - Trans_Table.TRN_Start_DATE>=0)

      group by ( Trans_Table.DEPOSIT_KEY , Trans_Table.TRN_Start_DATE,H.PASSIVE_DAYS)
      Having  (SUM(rep.AMOUNT)) > 10000000000
      )MAIN_7


      inner join dimamldeposit deposit
      on deposit.DEPOSIT_KEY = MAIN_7.DEPOSIT_KEY

标签： plsqlwith-statement

解决方案

这是很多代码，但是 - 为了简单起见，我建议您使用WITHfactoring 子句作为第一个命令，将您使用的所有表包含在其中，然后 - 作为最后一个SELECT- 从所有这些 CTE 中获取数据。像这样的东西：

with 
a as (select ... from ...),
b as (select ... from ...),
f as (select ... from ...),
...
select a.col1, b.col2, f.col3
from a join b on a.id = b.id
left join f on f.id = b.id
where ...

推荐阅读