|
안녕하세요 아직 초보입니다.
오라클 쿼리를 pgsql로 변환을 해야하는데 도움이 필요합니다.
sqlplus -s "CONTROLM"/"PASSWORD"@"CTRLM" << EOF_STMT > ${MSG_COMM_FILE}
WHENEVER SQLERROR EXIT 99
WHENEVER OSERROR EXIT 98
SET FEEDBACK OFF
SET SERVEROUTPUT ON
SET LINESIZE 2000
DECLARE
v1_CONDNAME varchar2(50);
v1_STATE varchar2(2);
v1_CNT number;
BEGIN
IF SUBSTR('${USR_NO}',1,1) != 'K' OR '${USR_NO}' = 'K0004000' THEN
BEGIN
SELECT COUNT(*)
INTO v1_CNT
FROM CMR_AJF
WHERE ODATE = '${ODATE}'
AND (APPLIC = '${CTM_APPL_NAME}' OR APPLIC = '${CTM_APPL_NAME_NEW}')
AND APPLGROUP LIKE '${CTM_GRP_NAME}'
;
dbms_output.put_line( 'CTM_SCH='||v1_CNT );
EXCEPTION WHEN NO_DATA_FOUND THEN
dbms_output.put_line( 'CTM_SCH='||'Not Scheduled' );
WHEN others THEN
dbms_output.put_line( 'RET_SQLCODE='||sqlcode );
END;
ELSE
dbms_output.put_line( 'CTM_SCH='||1 );
END IF;
BEGIN
SELECT CONDNAME
INTO v1_CONDNAME
FROM CMR_CONTAB
WHERE TRIM(CONDDATE) = SUBSTR('${ODATE}', 5, 4)
AND CONDNAME = '${CTM_COND_NAME}'
;
dbms_output.put_line( 'CONDNAME='||v1_CONDNAME );
dbms_output.put_line( 'RET_SQLCODE=0' );
EXCEPTION WHEN NO_DATA_FOUND THEN
dbms_output.put_line( 'RET_SQLCODE=0' );
WHEN others THEN
dbms_output.put_line( 'RET_SQLCODE='||sqlcode );
END;
BEGIN
SELECT DISTINCT STATE
INTO v1_STATE
FROM CMR_AJF
WHERE ODATE = '${ODATE}'
AND (APPLIC = '${CTM_APPL_NAME}' OR APPLIC = '${CTM_APPL_NAME_NEW}')
AND APPLGROUP LIKE '${CTM_GRP_NAME}'
AND STATE NOT IN ('C','1','B') -- '1':Order And Confirm, '8':Normal End, 'C' : Order
AND ROWNUM = 1
;
dbms_output.put_line( 'CTM_STATE='||v1_STATE );
dbms_output.put_line( 'RET_1SQLCODE=0' );
EXCEPTION WHEN NO_DATA_FOUND THEN
dbms_output.put_line( 'RET_1SQLCODE=0' );
WHEN others THEN
dbms_output.put_line( 'RET_1SQLCODE='||sqlcode );
END;
END;
/
EOF_STMT
|
