All Forums Database
sivadanam 1 post Joined 11/14
11 Nov 2014
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;

dnoeth 4628 posts Joined 11/04
11 Nov 2014

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

Dieter

You must sign in to leave a comment.