Oracle SQL代码产生错误

Question

当我从下面的选择语句中评论FACODE字段时(例如：/*FACODE*/(SQL会产生数据。这种简单的更改会产生数据，即。我没有错误。但是，当我从Fachode字段中删除评论时，我会收到一个错误，上面写着'

单行查询产生多个行

。

我已经尝试评论此代码的不同片段以查看代码将在哪里失败，并且整个代码仅在我不如上所述的facode字段时失败。

有人可以协助吗？我一直在试图弄清楚这一点而没有成功。

SELECT FIRST_DAY, LAST_DAY, SA_TYPE_CD, FA_ID, CRE_DTTM, WORK_DTTM, ADDRESS, 
NAME, COMPLETE_TYPE, /*FACODE,*/ WONUM, HOLIDAYS, 
CASE WHEN TRIM(TYPE) IN ('AB','AS') THEN AMOUNT ELSE 0 END AMOUNT,
CASE WHEN TRIM(TYPE) IN ('CA','CC','CCC','CR','NRR','AB','AS','CL') 
THEN TYPE ELSE 'CO' END FILT_TYPE,
CASE 
WHEN WORK_DTTM IS NULL 
THEN (
   (SELECT COUNT(*)
     FROM DUAL
     WHERE TO_CHAR(CRE_DTTM + LEVEL - 1, 'DY') NOT IN ('SAT', 'SUN')
     CONNECT BY LEVEL <= SYSDATE - CRE_DTTM) - HOLIDAYS + 
(DECODE(TO_CHAR(CRE_DTTM,'DY'),'SAT',1,'SUN',1,0))
)
WHEN WORK_DTTM < CRE_DTTM THEN 0
ELSE (
  (SELECT COUNT(*)
     FROM DUAL
      WHERE TO_CHAR(CRE_DTTM + LEVEL - 1, 'DY') NOT IN ('SAT', 'SUN')
      CONNECT BY LEVEL <= WORK_DTTM - CRE_DTTM + 1) - HOLIDAYS + 
      (DECODE(TO_CHAR(CRE_DTTM,'DY'),'SAT',1,'SUN',1,0) + 
 (DECODE(TO_CHAR(WORK_DTTM,'DY'),'SAT',1,'SUN',1,0)))
 ) END DAYS
 FROM (SELECT :START_DT FIRST_DAY,
   :END_DT LAST_DAY,
   SA.SA_TYPE_CD,
   FA.FA_ID,
   FA.CRE_DTTM,
   FO.WORK_DTTM,
   (SELECT ADDRESS1
    || ' '
    || ADDRESS2
    || ' ' STATE
  FROM CI_PREM
  WHERE PREM_ID = SP.PREM_ID
  ) ADDRESS,
  (SELECT ENTITY_NAME
  FROM CI_PER_NAME
   WHERE PER_ID      = ACCT.PER_ID
  AND NAME_TYPE_FLG = 'PRIM'
   AND PRIM_NAME_SW  = 'Y'
  ) NAME,
  (SELECT SUM(CUR_AMT)
  FROM CI_FT
  WHERE SA_ID      = SA.SA_ID
  AND PARENT_ID    = 'REFDMGCL'
  AND FREEZE_SW    = 'Y'
  AND FREEZE_DTTM >= :START_DT
  ) AMOUNT,
  (SELECT CC_TYPE_CD
  FROM CI_CC
  WHERE PER_ID = PER.PER_ID
  AND CC_DTTM  =
    (SELECT MAX(CC.CC_DTTM)
    FROM CI_CC CC
    WHERE CC.PER_ID    = PER.PER_ID
    AND TRIM(CC.CC_TYPE_CD) IN ('CA','CC','CCC','CO','CR','NRR', 'AS', 'AB', 
 'CL', 'FAREQ', 'FOLREQ', 'GEN')
    AND CC.CC_CL_CD    = 'DAMC'
    )
  ) TYPE,
  (SELECT CC_TYPE_CD
  FROM CI_CC
  WHERE CC_ID     = STEP.CC_ID
  AND CC_TYPE_CD IN ('CA','CC','CCC','CO','CR','NRR', 'AS', 'AB', 'CL', 
'FAREQ', 'FOLREQ', 'GEN')
  AND CC_DTTM     =
    (SELECT MAX(CC_DTTM) FROM CI_CC WHERE CC_ID = STEP.CC_ID
    )
  ) COMPLETE_TYPE,
--RNDB 20151019
(select char_val from ci_fa_char where char_type_cd='FA_CODE' and 
fa_id=fa.fa_id) FACODE,
(select adhoc_char_val from ci_fa_char where char_type_cd='CM-WONUM' and 
fa_id=fa.fa_id) WONUM,
--RNDB 20151019
  (SELECT COUNT(1)
  FROM ci_cal_hol
  WHERE holiday_dt                  >= fa.cre_dttm
  AND holiday_dt                    <= DECODE(fo.work_dttm, NULL, sysdate, 
fo.work_dttm)
   AND TO_CHAR(holiday_dt, 'DY') NOT IN ('SAT', 'SUN')
  ) HOLIDAYS,
  SYSDATE
FROM CI_FO FO,
  CI_FA FA,
  CI_SA SA,
  CI_SA_SP SAP,
  CI_SP SP,
  CI_ACCT_PER ACCT,
  CI_FA_STEP STEP,
  CI_PER PER
WHERE FO.FO_ID     = FA.FO_ID
AND FA.SP_ID       = SP.SP_ID
AND FA.FA_ID       = STEP.FA_ID
AND SAP.SP_ID      = SP.SP_ID
AND SAP.SA_ID      = SA.SA_ID
AND sap.start_dttm =
  (SELECT MAX(t1.start_dttm)
  FROM ci_sa_sp t1,
    ci_sa s
  WHERE t1.sa_id                 = s.sa_id
  AND s.sa_status_flg           <> '70'
  AND t1.sp_id                   = sap.sp_id
  AND NVL(t1.stop_dttm, sysdate) =
    (SELECT MAX(NVL(t2.stop_dttm, sysdate))
    FROM ci_sa_sp t2,
      ci_sa
     WHERE t2.sa_id           = ci_sa.sa_id
     AND ci_sa.sa_status_flg <> '70'
     AND t2.sp_id             = sap.sp_id
    )
  )
 AND NVL(sap.stop_dttm, sysdate) =
  (SELECT MAX(NVL(t2.stop_dttm, sysdate))
  FROM ci_sa_sp t2,
    ci_sa
  WHERE t2.sa_id           = ci_sa.sa_id
  AND ci_sa.sa_status_flg <> '70'
  AND t2.sp_id             = sap.sp_id
  )
AND SA.SA_STATUS_FLG NOT IN ('70')
AND SA.ACCT_ID            = ACCT.ACCT_ID
AND ACCT.MAIN_CUST_SW     = 'Y'
AND ACCT.PER_ID           = PER.PER_ID
AND FA.FA_TYPE_CD         = 'DMG CL'
--RNDB 20151019 - Show Cancelled Orders
--AND FA.FA_STATUS_FLG     <> 'X'
--AND FO.FO_STATUS_FLG     <> 'X'
AND FA.SCHED_DTTM        >= :START_DT
AND FA.SCHED_DTTM        <= :END_DT
ORDER BY SA.SA_TYPE_CD)

Answer 1

此SQL中的catode是指返回多个记录的子查询。

(select char_val from ci_fa_char where char_type_cd='FA_CODE' and 
fa_id=fa.fa_id) FACODE

您将必须通过向其他列中添加更多的违反/条件来限制此查询返回的记录数。