Teradata Stored Procedure

Database

Teradata Stored Procedure

Hi Friends,

Can anyone of you  pls help me here.

Question: I have teradata SP, compiling done successfully but the execution is getting failed. i cannot say the execution failed becuase execution done with zero rows. I am not sure whether the SP correct or not. Could  you please help me friends.

Here is the SP. actually i have to capture the result sets from the variable sql_Statement.

Big Dynamic sql statement individaully giving 1 rrecord where am executing/calling  it is zero records result.

please let me know if you need further details

Thanks

Siva

 

CREATE PROCEDURE DEMO_DYNA_2(

in v_cust_id varchar(1000),

in v_id_type integer,

in v_id_status integer)

DYNAMIC RESULT SETS 2

BEGIN

DECLARE V_STAT_TYPE_MER VARCHAR(100);

DECLARE V_WHE_STATUS_MER VARCHAR(100);

DECLARE V_STAT_TYPE_SPL VARCHAR(100);

DECLARE V_WHE_STATUS_SPL VARCHAR(100);

DECLARE V_JOIN_FLG VARCHAR(1);

DECLARE V_GET_MERGE_FLG VARCHAR(1);

DECLARE V_GET_SPLIT_FLG VARCHAR(1);

DECLARE V_SQL_STATEMENT VARCHAR(5000);

DECLARE SQL_STATEMENT VARCHAR(5000);

declare results1 cursor for s;

--DECLARE sqlstr VARCHAR(300);

--DECLARE V_COUNT INTeger;

IF V_ID_TYPE = 1 THEN

IF V_ID_STATUS = 1 THEN

SET V_GET_MERGE_FLG = 'Y';

SET V_STAT_TYPE_MER = '1 AS ID_STATUS , 1 AS ID_TYPE ';

SET V_WHE_STATUS_MER = 'AND IIMM.PRCS_CYCLE_DT IS NULL ';

ELSEIF V_ID_STATUS = 2 THEN

SET V_GET_MERGE_FLG = 'Y';

SET V_STAT_TYPE_MER = '2 AS ID_STATUS , 1 AS ID_TYPE ';

SET V_WHE_STATUS_MER = 'AND IIMM.CANCEL_DTTM IS NOT NULL ';

ELSEIF V_ID_STATUS = 3 THEN

SET V_GET_MERGE_FLG = 'Y';

SET V_STAT_TYPE_MER = '3 AS ID_STATUS , 1 AS ID_TYPE ';

SET V_WHE_STATUS_MER = 'AND IIMM.PRCS_CYCLE_DT IS NOT NULL ';

ELSE

set sql_Statement = ' SELECT IIP.CUST_UNQ_ID,ISYS.SYS_NM, '

||' IIP.SYS_NUM,IIP.FIRST_NM,IIP.MIDDLE_NM, '

||' IIP.LAST_NM,IIP.BIRTH_DT,IIP.SSN, '

||' IIP.GENDER_KEY,IIP.MBR_NUM,SUBSTR(IIP.CERT_FULL_KEY,1,9), '

||' IIP.GRP_NUM,IIP.BU_NUM,IIP.MIN_EFF_DT, '

||' IIP.MAX_EXPR_DT,IADDR.ADDR_TYPE_KEY, '

||' IADDR.ADDR_LINE_1_TXT, IADDR.ADDR_LINE_2_TXT, '

||' IADDR.CITY_NM,IADDR.STATE_KEY,IADDR.ZIP_CD_KEY, '

||' IPHN.PHONE_NUM, '

||' CASE WHEN IIMM.PRCS_CYCLE_DT IS NULL AND '

||' IIMM.CANCEL_DTTM IS NULL THEN ''1'' '

||' WHEN IIMM.CANCEL_DTTM IS NOT NULL THEN ''2'' '

||' WHEN IIMM.PRCS_CYCLE_DT IS NOT NULL THEN ''3'' ELSE ''4'' END AS ID_STATUS, '

||' ''1'' AS ID_TYPE FROM CN01532.CUST_PRFL IIP LEFT OUTER JOIN '

||' CN01532.ABC_SYS ISYS ON IIP.SRC_SYS_ID = ISYS.SRC_SYS_ID '

||' AND IIP.SYS_NUM = ISYS.SYS_ID LEFT OUTER JOIN '

||' CN01532.CUST_ADDR IADDR ON IIP.CUST_UNQ_ID = IADDR.CUST_UNQ_ID '

||' LEFT OUTER JOIN CN01532.CUST_PHONE IPHN ON IIP.CUST_UNQ_ID = IADDR.CUST_UNQ_ID '

||' LEFT OUTER JOIN CN01532.CUST_MNL_MERGE IIMM ON IIP.SRC_SYS_ID = IIMM.SRC_SYS_ID '

||' AND IIP.SYS_NUM = IIMM.SYS_NUM AND IIP.CUST_UNQ_ID = IIMM.PR_CUST_UNQ_ID '

||' WHERE IIP.CUST_UNQ_ID IN ('||V_CUST_ID||')';

prepare s from Sql_Statement;

open results1;

end if;

IF V_GET_MERGE_FLG = 'Y' THEN

SET SQL_STATEMENT =

' SELECT IIP.CUST_UNQ_ID, '

||' ISYS.SYS_NM,IIP.SYS_NUM, '

||' IIP.FIRST_NM, '

||' IIP.MIDDLE_NM, '

||' IIP.LAST_NM,IIP.BIRTH_DT, '

||' IIP.SSN,IIP.GENDER_KEY, '

||' IIP.MBR_NUM,SUBSTR(IIP.CERT_FULL_KEY,1,9), '

||' IIP.GRP_NUM,IIP.BU_NUM,IIP.MIN_EFF_DT, '

||' IIP.MAX_EXPR_DT,IADDR.ADDR_TYPE_KEY, '

||' IADDR.ADDR_LINE_1_TXT,IADDR.ADDR_LINE_2_TXT, '

||' IADDR.CITY_NM,IADDR.STATE_KEY, '

||' IADDR.ZIP_CD_KEY,IPHN.PHONE_NUM '

|| V_STAT_TYPE_MER

||' FROM CN01532.CUST_PRFL IIP INNER JOIN CN01532.ABC_SYS ISYS '

||' ON IIP.SRC_SYS_ID = ISYS.SRC_SYS_ID AND IIP.SYS_NUM = ISYS.SYS_ID '

||' INNER JOIN CN01532.CUST_ADDR IADDR ON IIP.CUST_UNQ_ID = IADDR.CUST_UNQ_ID '

||' INNER JOIN CN01532.CUST_PHONE IPHN ON IIP.CUST_UNQ_ID = IADDR.CUST_UNQ_ID '

||' INNER JOIN CN01532.CUST_MNL_MERGE IIMM ON IIP.SRC_SYS_ID = IIMM.SRC_SYS_ID '

||' AND IIP.SYS_NUM = IIMM.SYS_NUM AND IIP.CUST_UNQ_ID = IIMM.PR_CUST_UNQ_ID '

||' WHERE IIP.CUST_UNQ_ID IN ( '

|| V_CUST_ID

|| ')';

prepare s from Sql_Statement;

open results1;

END IF;

end if;

end;

1 REPLY
Junior Contributor

Re: Teradata Stored Procedure

You need to check the actual SQL_STATEMENT, either by setting an OUT parameter or inserting it into a logtable.