如何将WITH语句嵌套进现有SELECT并传递表字段日期参数

我明白你现在的困境——你已经找到了计算两个日期之间排除节假日工作日的逻辑，但没法把现有查询里的t1.ATTRIBUTE_DATE1和t1.check_date作为参数传入，而且直接嵌入WITH语句还报参数过多的错误。咱们一步步来解决这个问题。

问题根源分析 #

你之前尝试把WITH语句直接嵌套在SELECT字段列表里，这种写法在Oracle中会因为上下文关联问题报错。另外，原来的WITH逻辑是针对固定日期生成范围，现在要适配主查询的每一行数据，需要调整逻辑的关联方式。

解决方案1：计算每行的总工作日数（最常用场景） #

如果你的需求是获取t1.ATTRIBUTE_DATE1到t1.check_date之间的总工作日数量，可以把计算逻辑改成标量子查询，直接嵌入到主查询的SELECT字段列表中。这样就能针对每行数据独立计算：

SELECT DISTINCT 
  t1.invoice_date,
  t1.creation_date,
  t1.INVOICE_RECEIVED_DATE,
  (t1.check_Date - t1.INVOICE_RECEIVED_DATE),
  ((t1.check_Date - t2.REPORT_SUBMIT_DATE)+3),
  ((t1.check_Date - t1.invoice_date)+3),
  t1.ATTRIBUTE_DATE1,
  -- 新增：计算两个日期之间的总工作日数
  CASE WHEN t1.ATTRIBUTE_DATE1 IS NOT NULL THEN
    (SELECT COUNT(*)
     FROM (SELECT t1.ATTRIBUTE_DATE1 + LEVEL - 1 AS week_day
           FROM dual
           CONNECT BY t1.ATTRIBUTE_DATE1 + LEVEL - 1 <= t1.check_date) all_dates
     WHERE TO_CHAR(week_day, 'FMDAY', 'NLS_DATE_LANGUAGE=ENGLISH') NOT IN ('SATURDAY','SUNDAY')
       AND TO_CHAR(week_day, 'DD/MM/YYYY') NOT IN ('01/01/2019', '25/12/2019', '26/12/2019', '26/08/2019', '19/04/2019', '22/04/2019', '06/05/2019', '27/05/2019'))
  ELSE NULL END AS total_workdays,
  t1.invoice_num,
  t1.payment_number,
  t1.check_date,
  t1.vendor_type_lookup_code,
  t1.source,
  t1.PAY_GROUP_LOOKUP_CODE,
  t1.Batch_Name,
  t1.Description,
  t1.Vendor_Name,
  t1.Amount_Paid,
  t1.Invoice_ID,
  t2.REPORT_SUBMIT_DATE,
  t2.FINAL_APPROVAL_DATE 
FROM (
  SELECT DISTINCT 
    APA.INVOICE_ID,
    APA.INVOICE_DATE,
    APA.CREATION_DATE,
    APA.ATTRIBUTE_DATE1,
    APA.INVOICE_NUM,
    ACA.CHECK_NUMBER as PAYMENT_NUMBER,
    ACA.CHECK_DATE,
    APA.INVOICE_RECEIVED_DATE,
    APA.CREATION_DATE,
    SUP.VENDOR_TYPE_LOOKUP_CODE,
    APA.SOURCE,
    APA.PAY_GROUP_LOOKUP_CODE,
    BAT.BATCH_NAME,
    APA.DESCRIPTION,
    APA.AMOUNT_PAID,
    ACA.VENDOR_NAME 
  FROM AP_INVOICES_ALL APA 
  LEFT JOIN AP_INVOICE_LINES_ALL AIL ON APA.INVOICE_ID= AIL.INVOICE_ID 
  LEFT JOIN AP_INVOICE_DISTRIBUTIONS_ALL AID ON APA.INVOICE_ID = AID.INVOICE_ID AND AIL.LINE_NUMBER = AID.INVOICE_LINE_NUMBER 
  JOIN AP_INVOICE_PAYMENTS_ALL AIP ON APA.INVOICE_ID = AIP.INVOICE_ID 
  JOIN AP_CHECKS_ALL ACA ON AIP.CHECK_ID = ACA.CHECK_ID 
  LEFT JOIN AP_BATCHES_ALL BAT ON APA.BATCH_ID = BAT.BATCH_ID 
  LEFT JOIN POZ_SUPPLIERS_V SUP ON APA.PARTY_ID = SUP.PARTY_ID 
  WHERE AID.LINE_TYPE_LOOKUP_CODE = 'ITEM' 
    AND APA.SOURCE NOT IN ('INVOICE GATEWAY' , 'B2B XML INVOICE') 
    AND ACA.STATUS_LOOKUP_CODE<> 'VOIDED' 
    AND APA.INVOICE_TYPE_LOOKUP_CODE NOT IN ('CREDIT' , 'PREPAYMENT') 
    AND ACA.CHECK_DATE BETWEEN :Start_Date AND :End_Date 
    AND BAT.BATCH_NAME IS NOT NULL
) t1 
LEFT JOIN (
  Select EXPENSE_REPORT_NUM ,REPORT_SUBMIT_DATE ,FINAL_APPROVAL_DATE ,EXPENSE_REPORT_TOTAL 
  FROM EXM_EXPENSE_REPORTS
)t2 ON t1.INVOICE_NUM =t2.EXPENSE_REPORT_NUM 
ORDER BY t1.CHECK_DATE ASC

关键调整点：

1. 处理空值：用CASE WHEN判断t1.ATTRIBUTE_DATE1是否为空，避免空值导致的计算错误。
2. 去掉冗余转换：如果t1.ATTRIBUTE_DATE1和t1.check_date本身是DATE类型，不需要再用TO_DATE转换（如果是字符串类型，再补充对应的格式转换）。
3. 标量子查询适配每行：子查询会针对主查询的每一行t1数据生成日期范围，独立计算工作日数。

解决方案2：按月份分组统计工作日（Oracle 12c+） #

如果你的需求是保留按月份分组的统计结果，可以用Oracle的LATERAL JOIN（12c及以上版本支持），把计算逻辑作为子查询关联到主查询的每一行：

SELECT DISTINCT 
  t1.invoice_date,
  t1.creation_date,
  t1.INVOICE_RECEIVED_DATE,
  (t1.check_Date - t1.INVOICE_RECEIVED_DATE),
  ((t1.check_Date - t2.REPORT_SUBMIT_DATE)+3),
  ((t1.check_Date - t1.invoice_date)+3),
  t1.ATTRIBUTE_DATE1,
  wd.month_abbr,
  wd.day_count,
  t1.invoice_num,
  t1.payment_number,
  t1.check_date,
  t1.vendor_type_lookup_code,
  t1.source,
  t1.PAY_GROUP_LOOKUP_CODE,
  t1.Batch_Name,
  t1.Description,
  t1.Vendor_Name,
  t1.Amount_Paid,
  t1.Invoice_ID,
  t2.REPORT_SUBMIT_DATE,
  t2.FINAL_APPROVAL_DATE 
FROM (
  SELECT DISTINCT 
    APA.INVOICE_ID,
    APA.INVOICE_DATE,
    APA.CREATION_DATE,
    APA.ATTRIBUTE_DATE1,
    APA.INVOICE_NUM,
    ACA.CHECK_NUMBER as PAYMENT_NUMBER,
    ACA.CHECK_DATE,
    APA.INVOICE_RECEIVED_DATE,
    APA.CREATION_DATE,
    SUP.VENDOR_TYPE_LOOKUP_CODE,
    APA.SOURCE,
    APA.PAY_GROUP_LOOKUP_CODE,
    BAT.BATCH_NAME,
    APA.DESCRIPTION,
    APA.AMOUNT_PAID,
    ACA.VENDOR_NAME 
  FROM AP_INVOICES_ALL APA 
  LEFT JOIN AP_INVOICE_LINES_ALL AIL ON APA.INVOICE_ID= AIL.INVOICE_ID 
  LEFT JOIN AP_INVOICE_DISTRIBUTIONS_ALL AID ON APA.INVOICE_ID = AID.INVOICE_ID AND AIL.LINE_NUMBER = AID.INVOICE_LINE_NUMBER 
  JOIN AP_INVOICE_PAYMENTS_ALL AIP ON APA.INVOICE_ID = AIP.INVOICE_ID 
  JOIN AP_CHECKS_ALL ACA ON AIP.CHECK_ID = ACA.CHECK_ID 
  LEFT JOIN AP_BATCHES_ALL BAT ON APA.BATCH_ID = BAT.BATCH_ID 
  LEFT JOIN POZ_SUPPLIERS_V SUP ON APA.PARTY_ID = SUP.PARTY_ID 
  WHERE AID.LINE_TYPE_LOOKUP_CODE = 'ITEM' 
    AND APA.SOURCE NOT IN ('INVOICE GATEWAY' , 'B2B XML INVOICE') 
    AND ACA.STATUS_LOOKUP_CODE<> 'VOIDED' 
    AND APA.INVOICE_TYPE_LOOKUP_CODE NOT IN ('CREDIT' , 'PREPAYMENT') 
    AND ACA.CHECK_DATE BETWEEN :Start_Date AND :End_Date 
    AND BAT.BATCH_NAME IS NOT NULL
) t1 
LEFT JOIN (
  Select EXPENSE_REPORT_NUM ,REPORT_SUBMIT_DATE ,FINAL_APPROVAL_DATE ,EXPENSE_REPORT_TOTAL 
  FROM EXM_EXPENSE_REPORTS
)t2 ON t1.INVOICE_NUM =t2.EXPENSE_REPORT_NUM 
-- 新增：横向关联工作日分组统计
LEFT JOIN LATERAL (
  WITH all_dates AS (
    SELECT t1.ATTRIBUTE_DATE1 + LEVEL - 1 AS week_day
    FROM dual
    WHERE t1.ATTRIBUTE_DATE1 IS NOT NULL
    CONNECT BY t1.ATTRIBUTE_DATE1 + LEVEL - 1 <= t1.check_date
  )
  SELECT TO_CHAR(week_day, 'MON') AS month_abbr, COUNT(*) AS day_count
  FROM all_dates
  WHERE TO_CHAR(week_day, 'FMDAY', 'NLS_DATE_LANGUAGE=ENGLISH') NOT IN ('SATURDAY','SUNDAY')
    AND TO_CHAR(week_day, 'DD/MM/YYYY') NOT IN ('01/01/2019', '25/12/2019', '26/12/2019', '26/08/2019', '19/04/2019', '22/04/2019', '06/05/2019', '27/05/2019')
  GROUP BY TO_CHAR(week_day, 'MON')
) wd ON 1=1
ORDER BY t1.CHECK_DATE ASC

这种写法会让每行t1的数据关联到对应的月份工作日统计结果，适合需要按月份拆分数据的场景。

额外建议 #

• 建议把节假日列表放到一个单独的表中，比如HOLIDAYS，然后用NOT EXISTS或者LEFT JOIN来排除，这样维护起来更方便，不用每次修改SQL。
• 如果你的Oracle版本低于12c，没法用LATERAL JOIN，可以考虑用CONNECT BY结合主表的主键来生成每行的日期范围，避免笛卡尔积。

内容的提问来源于stack exchange，提问作者Leighholling