MQSeries.net :: View topic - Internal procedure with SQL statement

mehedi

Coding an internal procedure with an SQL statement - a syntax error was returned.
Tried variuos thing
(1) Tried using OutputRoot.XML.ABC[] - an out of scope error on OutputRoot.
Any suggestions ?

CREATE PROCEDURE MQSI_CROSSWALK
(
IN pHeader CHAR,
IN pMode CHAR,
IN pInSetNumber INTEGER,
IN pVersion CHAR,
IN pValue CHAR,
IN pOutSetNumber INTEGER,
IN pDate CHAR,
OUT pRC CHAR,
OUT pNewValue CHAR,
OUT pMessage CHAR
)
BEGIN

Declare TempChar CHAR;
-----------------------------------------------------------------
-- Access variables for the codeset API
-----------------------------------------------------------------

Declare cwRQST_ID CHAR;
Declare cwRQST_TYPE CHAR;
Declare cwRQST_SET_SEQ_NO CHAR;
Declare cwRQST_CD_VALUE CHAR;
Declare cwRQST_DATE CHAR;
Declare cwXWALK_CD_SEQ_NO CHAR;
Declare cwCOMPLEX_ARGUMENTS CHAR;
Declare cwRESP_STATUS CHAR;
Declare cwRESP_MSG CHAR;
Declare cwRESP_SHORT_DESC CHAR;
Declare cwRESP_LONG_DESC CHAR;
Declare cwRESP_CD_SEQ_NO CHAR;
Declare cwRESP_VALUE_SEQ CHAR;
Declare cwRESP_VALUE CHAR;

Declare SQLState1 CHAR;
Declare SQLErrorText1 CHAR;
Declare SQLCode1 INTEGER;
Declare SQLNativeError1 INTEGER;
Declare w_val_cd CHAR;

Set cwRQST_ID = SUBSTRING(pHeader FROM 1 for 23) || ':' || SUBSTRING(CAST(CURRENT_TIMESTAMP as CHAR) FROM 12 FOR 26);

Set TempChar = CAST(pInSetNumber as CHAR);
SET cwRQST_SET_SEQ_NO = OVERLAY('000000000' PLACING TempChar FROM 10 - LENGTH(TempChar));
SET cwRQST_DATE = pDate;
Set TempChar = CAST(pOutSetNumber as CHAR);
SET cwXWALK_CD_SEQ_NO = OVERLAY('000000000' PLACING TempChar FROM 10 - LENGTH(TempChar));
SET cwRQST_CD_VALUE = pValue;

SET w_val_cd =
(SELECT
-- B.CCS_RSL_SET_SEQ_NO
-- ,B.CCV_RSL_VAL_SEQ_NO
C.CCV_VAL_CD
-- ,D.CCVD_CD_VAL_DSC
-- ,D.CCVD_VAL_EXT_DSC
FROM Database.IBC_CORP_CD_VALUE as A
,Database.IBC_CD_VALUE_MAP as B
,Database.IBC_CORP_CD_VALUE as C
,Database.IBC_CD_VALUE_DSC as D
WHERE A.CCS_CD_SET_SEQ_NO = cwRQST_SET_SEQ_NO
AND A.CCV_VAL_CD = cwRQST_CD_VALUE
AND A.DEL_IND = 'N'
AND A.CCV_VAL_EFF_DT <= cwRQST_DATE
AND
(
A.CCV_VAL_TERM_DT IS NULL OR
A.CCV_VAL_TERM_DT >= cwRQST_DATE
)
AND (
A.CCS_CD_SET_SEQ_NO = B.CCS_DTR_SET_SEQ_NO
AND A.CCV_CD_VAL_SEQ_NO = B.CCV_DTR_VAL_SEQ_NO
AND B.CCC_CW_SEQ_NO = cwRQST_SET_SEQ_NO
AND B.CCVM_MAP_EFF_DT <= cwRQST_DATE
AND
(
B.CCVM_MAP_TERM_DT IS NULL OR
B.CCVM_MAP_TERM_DT >= cwRQST_DATE
)
)
AND
(
C.CCS_CD_SET_SEQ_NO = B.CCS_RSL_SET_SEQ_NO
AND C.CCV_CD_VAL_SEQ_NO = B.CCV_RSL_VAL_SEQ_NO
AND C.CCS_CD_SET_SEQ_NO = D.CCS_CD_SET_SEQ_NO
AND C.CCV_CD_VAL_SEQ_NO = D.CCV_CD_VAL_SEQ_NO
AND D.CCVD_DSC_EFF_DT <= cwRQST_DATE
AND (
D.CCVD_DSC_TERM_DT IS NULL OR
D.CCVD_DSC_TERM_DT >= cwRQST_DATE
)
)
);
SET SQLState1 = SQLSTATE;
SET SQLCode1 = SQLCODE;
SET SQLErrorText1 = SQLERRORTEXT;
SET SQLNativeError1 = SQLNATIVEERROR;

IF SQLCode1 = 0 THEN
SET cwRESP_STATUS = '0000';
SET cwRESP_VALUE_SEQ = w_val_cd; --OutputRoot.XML.Result.SimpleCrossWalkData."CCV_VAL_CD";
ELSE
IF SQLCode1 = 100 THEN
SET cwRESP_STATUS = '0001';
SET cwRESP_SHORT_DESC = ' ';
SET cwRESP_SHORT_DESC = ' ';
SET cwRESP_CD_SEQ_NO = ' ';
SET cwRESP_VALUE_SEQ = ' ';
-- ELSE THROW USER EXEPTION MESSAGE 3124 VALUES
-- ('The SQL State', SQLState1, SQLCode1, SQLNativeError1, SQLErrorText1);
END IF;
END IF;

--}

end if;

SET pRC = cwRESP_STATUS;
SET pMessage = cwRESP_MSG;
SET pNewValue = cwRESP_VALUE;
END;

BIP2524E: (209, 15) : Cannot assign a list to a non-list, nor a non-list to a list.

A list of values cannot be assigned to a scalar variable or single message field, nor can a scalar value be assigned to a list.

Correct the syntax of the expression and redeploy the message flow.