데이터베이스 질문과 답변
EXEC SQL
DECLARE lsscursor_l CURSOR FOR
WITH T1 AS (SELECT * FROM RSK_BASLOSRT_N3 WHERE AS_OF_DATE = :gstr_ErrLog.cAs_Of_Date AND BASYM <= :gstr_ErrLog.cAs_Of_Date AND CD_GPROD = '00' AND CD_CVRG = '00'),
T2 AS ( SELECT * FROM RSK_BASLOSRT_N3 WHERE AS_OF_DATE = :gstr_ErrLog.cAs_Of_Date AND BASYM <= :gstr_ErrLog.cAs_Of_Date ORDER BY 1, 2, 3, 4, 5)
SELECT
AS_OF_DATE,
CD_GVFSS,
CD_GPROD,
CD_CVRG,
ERNDYY,
BASYM,
CASE
WHEN
BRSKPRM > 5000000 AND BLOSRT >= 0 THEN BLOSRT/ 100.0
WHEN
BRSKPRM <= 5000000 OR BLOSRT < 0 THEN ALOSRT/ 100.0
ELSE
BRSKPRM
END LOSRT
FROM
(
SELECT
T2.AS_OF_DATE AS_OF_DATE ,
T2.BASYM BASYM ,
T2.CD_GVFSS CD_GVFSS ,
T2.CD_GPROD CD_GPROD ,
T2.CD_CVRG CD_CVRG ,
T2.ERNDYY ERNDYY ,
T2.BASYM BASYM ,
T1.RSKPRM ARSKPRM ,
T2.RSKPRM BRSKPRM ,
T1.LOSRT ALOSRT,
T2.LOSRT BLOSRT
FROM
T1 , T2
WHERE T2.CD_GVFSS = T1.CD_GVFSS
AND T2.ERNDYY = T1.ERNDYY
AND T2.BASYM = T1.BASYM
ORDER BY T2.CD_GPROD , T2.CD_CVRG , T2.ERNDYY
)
ORDER BY CD_GPROD ,CD_CVRG , ERNDYY , BASYM;
이 커서문을 넣고 컴파일을 했는데요
-------------------------------------------------------------------------------------------------------------------
ERROR MESSAGE입니다.
Syntax error at line 130, column 7, file ./RSK_L_S_DSTLOSRT_N3.pc:
Error at line 130, column 7 in file ./RSK_L_S_DSTLOSRT_N3.pc
WITH T1 AS (SELECT * FROM RSK_BASLOSRT_N3 WHERE AS_OF_DATE = :gstr_ErrLog.cAs_Of_Date AND BASYM <= :gstr_ErrLog.cAs_Of_Date AND CD_GPROD = '00' AND CD_CVRG = '00'),......1
PCC-S-02201, Encountered the symbol "T1" when expecting one of the following:
;
Syntax error at line 0, column 0, file ./RSK_L_S_DSTLOSRT_N3.pc:
Error at line 0, column 0 in file ./RSK_L_S_DSTLOSRT_N3.pc
PCC-S-02201, Encountered the symbol "<eof>" when expecting one of the following:
; : an identifier, end-exec, random_terminal
Error at line 0, column 0 in file ./RSK_L_S_DSTLOSRT_N3.pc
PCC-F-02102, Fatal error while doing C preprocessing
검색해보고 서적 찾아보고 했는데 못찾겠어서요ㅠ
부탁 드리겠습니다 선배님들!
-- ProC 에러는 모르겠고... 복잡한 비효율 쿼리를 단순하게 바꿔드릴께요.
SELECT as_of_date
, cd_gvfss
, cd_gprod
, cd_cvrg
, erndyy
, basym
, CASE WHEN rskprm > 5000000 AND losrt >= 0 THEN losrt
ELSE MIN(CASE WHEN cd_gprod = '00' AND cd_cvrg = '00' THEN losrt END)
OVER(PARTITION BY cd_gvfss, erndyy, basym)
END / 100 AS losrt
FROM rsk_baslosrt_n3
WHERE as_of_date = :gstr_ErrLog.cAs_Of_Date
AND basym <= :gstr_ErrLog.cAs_Of_Date
ORDER BY cd_gprod
, cd_cvrg
, erndyy
, basym
;
아직 초보라서 쿼리에 대해서 너무 모르는게 많은데
많이 배웠습니다.!!
) 버튼을 클릭하여 작성 하시면 됩니다.