Facing Problem to pass multiple values using 'IN' clause in static sql in Teradata store procedure

General

Facing Problem to pass multiple values using 'IN' clause in static sql in Teradata store procedure

Hello All,

I'm new to static store procedure's in teradata.

Am facing problem problem to pass multiple values through object

(sp.CHANNEL_TYPE IN (:vchannel) OR (:vchannel_all) = 'A')

for example vchannel object having values like 'P','R','H'

Am able to pass this values using dynamic values

(sp.CHANNEL_TYPE IN ('||vchannel||') OR ('''||vchannel_all||''') = ''A'' 

my code like this

REPLACE PROCEDURE DB.Rc_Exec_Summary_test (

IN Report_Type CHAR(3),

IN Report_Brand CHAR(1),

IN Report_Division VARCHAR(20),

IN Report_Region VARCHAR(20),

IN Report_District VARCHAR(4),

IN Report_Supercorp VARCHAR(5),

IN Report_Channel VARCHAR(30),

IN Report_Customer VARCHAR(10),

IN Report_Month BYTEINT,

IN Report_Year SMALLINT,

IN userid VARCHAR(30)

)DYNAMIC RESULT SETS 1 --Parameter Declerations

BEGIN

DECLARE vReportType CHAR(1); --Report Type, it will be either Daily or Monthly

DECLARE vReportType2 CHAR(1);

DECLARE vReportType3 CHAR(1);

DECLARE vReport_Month BYTEINT; --Month of Year passed from Crystal

DECLARE vReport_Year SMALLINT; --Year Passed from Crystal

DECLARE vDivision VARCHAR(20); --Customer DIVISION Passed from Crystal

DECLARE vRegion VARCHAR(20); --Customer REGION Passed from Crystal

DECLARE vDistrict VARCHAR(4); --Customer DISTRICT Passed from Crystal

DECLARE vCustomer VARCHAR(10); --Customer Number Passed from Crystal

DECLARE vSelect_Month BYTEINT; --Month of Year passed from Crystal

DECLARE vSelect_Year SMALLINT; --Year Passed from Crystal

DECLARE VMonth_Name VARCHAR(9); --Month Name

DECLARE vDATE_KEY INTEGER;

DECLARE vSalesType CHAR(1);

DECLARE vCurrency_Rate DECIMAL(8,5);

DECLARE vCurrency_Rate2 DECIMAL(8,5);

DECLARE vCurrency_Rate3 DECIMAL(8,5);

DECLARE vSupercorp VARCHAR(5);

DECLARE VSuperName VARCHAR(30); --Supercorp Name

DECLARE vchannel VARCHAR(30);

DECLARE vchannel_all VARCHAR(1);

DECLARE S_VALUE2 DECIMAL(20,9);

DECLARE S_QUANTITY DECIMAL(20,9);

DECLARE S_YTD_VALUE DECIMAL(20,9);

DECLARE S_YTD_QTY DECIMAL(20,9);

DECLARE S_LY_YTD_VALUE DECIMAL(20,9);

DECLARE S_LY_YTD_QTY DECIMAL(20,9);

DECLARE S_LLY_YTD_VALUE DECIMAL(20,9);

DECLARE S_LLY_YTD_QTY DECIMAL(20,9);

DECLARE T_VALUE2 DECIMAL(20,9);

DECLARE T_QUANTITY DECIMAL(20,9);

DECLARE T_YTD_VALUE DECIMAL(20,9);

DECLARE T_YTD_QTY DECIMAL(20,9);

DECLARE T_LY_YTD_VALUE DECIMAL(20,9);

DECLARE T_LY_YTD_QTY DECIMAL(20,9);

DECLARE T_LLY_YTD_VALUE DECIMAL(20,9);

DECLARE T_LLY_YTD_QTY DECIMAL(20,9);

SET vReport_Month = Report_Month; --Set Report Month = to Month passed from Crystal

SET vReport_Year = Report_Year; --Year = to Year sent from Crystal

SET vDivision = TRIM(Report_Division);

SET vRegion = TRIM(Report_Region);

SET vDistrict = TRIM(Report_District);

SET vCustomer = TRIM(Report_Customer);

SET vSalesType = UPPER(REPORT_BRAND);

SET vSupercorp = TRIM(Report_Supercorp); --Set the Supercorp =2 the Parameter Passed

SET vSuperName = TRIM(Report_Supercorp);

SET vchannel = SUBSTR(report_channel,1,1);

SET vchannel_all = TRIM(report_channel);

CALL T_AMK_sp.StringReplace(Report_Channel, ',' ,''',''', vchannel);

IF vReport_Month = 99.00 OR vReport_Year = 9999.00 THEN --IF Statement #1

SELECT THE_DATE.month_of_year, THE_DATE.YR

INTO vSelect_Month, vSelect_Year

FROM THE_DATE

WHERE THE_DATE.THE_DATE =

CAST(SUBSTR((CURRENT_DATE-1)(FORMAT 'YYYY/MM/DD'),1,8)||'01' AS DATE FORMAT 'yyyy/mm/dd');

IF vReportType = 'D' OR vReportType = 'd' THEN --IF Statement #2

--

SET vReport_Month = vSelect_Month; --Set Month to Selected Month

SET vReport_Year = vSelect_Year; --Set Year to Selected Year

ELSEIF vReportType = 'M' OR vReportType = 'm' THEN --ELSIF #2

IF vSelect_Month = 1.00 THEN --if Statement #3

--

SET vReport_Month = 12.00; --Set Month to December

SET vReport_Year = vSelect_Year - 1; --Set year to Prior Year

--

ELSE --ELSE #3

--

SET vReport_Month = vSelect_Month - 1; --Set to Prior Month

SET vReport_Year = vSelect_Year; --Set Year to Selected Year

--

END IF; --End if #3

ELSE --ELSE 2

SET vReport_Month = vSelect_Month; --Set month to Selected Month

SET vReport_Year = vSelect_Year; --Set Year to Selected Year

END IF; --End IF #2

END IF; --End IF #1

SELECT date_key, month_name INTO vdate_key, vMonth_name

FROM THE_DATE

WHERE month_of_year = vReport_Month AND

YR = vReport_Year AND

DAY_OF_MONTH = 01;

IF UPPER(vSuperName) <> 'ALL' THEN

SELECT SUPERCORP_NAMES.name

INTO vSuperName

FROM SUPERCORP_NAMES

WHERE SUPERCORP_NAMES.supercorp_id=UPPER(vSupercorp);

END IF;

-- Main SQL converted from dynamic to normal SWL

BEGIN

DECLARE result_set CURSOR WITH RETURN ONLY FOR

SELECT

:vchannel as channel,

SUM(VALUE1) VALUE2,

SUM(QUANTITY) QUANTITY,

SUM(YTD_VALUE) YTD_VALUE,

SUM(YTD_QTY) YTD_QTY

FROM

(SELECT

CAST(SUM(Y.NET_VALUE_USD / Y.EXCHANGE_RATE) AS DECIMAL(20,9)) VALUE1,

CAST(SUM(Y.QUANTITY) AS DECIMAL(20,9)) QUANTITY,

CAST(SUM(Y.YTD_NET_VALUE / Y.EXCHANGE_RATE) AS DECIMAL(20,9)) YTD_VALUE,

CAST(SUM(Y.YTD_QUANTITY) AS DECIMAL(20,9)) YTD_QTY,

FROM YTD_PRODUCT_CODE_SALES Y LEFT OUTER JOIN PRODUCT_CODES P

ON Y.PRODUCT_CODE = P.MATERIAL_PRODUCT_CODE

INNER JOIN CUSTOMER_dist C

ON Y.CUSTOMER_KEY = C.CUSTOMER_KEY

LEFT OUTER JOIN supercorp_names sp

ON c.customer_supercorp=sp.SUPERCORP_ID

WHERE Y.DATE_KEY = :VDATE_KEY AND

userid = :userid AND

P.PRODUCT_CODE_LINE = 'Strut' AND

((:vSalesType = 'N' AND COALESCE(P.BRAND_INDICATOR,'?') NOT IN ( 'E','?')) OR

(:vSalesType = 'R' AND COALESCE(P.BRAND_INDICATOR,'?')='R') OR

(:vSalesType = 'N' AND COALESCE(P.BRAND_INDICATOR,'?') = '?' AND c.customer_sap_flag='N')) AND

( UPPER(:vDivision) = 'ALL' OR UPPER(:vDivision) = C.CUSTOMER_DIVISION) AND

( C.CUSTOMER_REGION = UPPER(:vRegion) OR UPPER(:vRegion) = 'ALL' ) AND

( UPPER(:vDistrict) = 'ALL' OR UPPER(:vDistrict) = C.CUSTOMER_DISTRICT) AND

( ((SUBSTR(('0000000000'),1,10 - CHARACTER_LENGTH(TRIM (:vCustomer)))) || TRIM(:vCustomer)) = '0000000ALL'

OR ((SUBSTR(('0000000000'),1,10 - CHARACTER_LENGTH(TRIM (:vCustomer)))) || TRIM(:vCustomer)) = C.CUSTOMER ) AND

( ( UPPER(:vSupercorp) = 'ALL' OR UPPER(:vSupercorp) = C.CUSTOMER_supercorp) OR

UPPER(:vSupercorp)=COALESCE(sp.COMBINED_SUPERCORP_ID,'*****'))

AND (sp.CHANNEL_TYPE IN (:vchannel) OR (:vchannel_all) = 'A')

UNION ALL

SELECT

SUM(Y.NET_VALUE_USD / Y.EXCHANGE_RATE*-1) VALUE1,

SUM(Y.QUANTITY*-1) QUANTITY,

SUM(Y.YTD_NET_VALUE / Y.EXCHANGE_RATE*-1) YTD_VALUE,

SUM(Y.YTD_QUANTITY*-1) YTD_QTY

FROM YTD_RETURNS Y LEFT OUTER JOIN PRODUCT_CODES P

ON Y.PRODUCT_CODE = P.MATERIAL_PRODUCT_CODE

INNER JOIN CUSTOMER_dist C

ON Y.CUSTOMER_KEY = C.CUSTOMER_KEY

LEFT OUTER JOIN supercorp_names sp

ON c.customer_supercorp=sp.SUPERCORP_ID

INNER JOIN BILLING_NET BN

ON Y.BILLING_ORDER_REASON = BN.ORDER_REASON_CODE

WHERE :vReportType2 = 'F' AND

Y.DATE_KEY = :VDATE_KEY AND

userid = :userid AND

--Y.BILLING_ORDER_REASON IN ('100', '104', 'AGA', 'AIR', '106','200', '201', '292', '209', '210', '211', '217', 'WTY', 'DFT', 'EXC', '101','250','MMO','C90','221','328') AND

BN.ORDER_REASON_CODE_TYPE IN ('W','R') AND

P.PRODUCT_CODE_LINE = 'Strut' AND

((:vSalesType = 'N' AND COALESCE(P.BRAND_INDICATOR,'?') NOT IN ( 'E','?')) OR

(:vSalesType = 'R' AND COALESCE(P.BRAND_INDICATOR,'?')='R') OR

(:vSalesType = 'N' AND COALESCE(P.BRAND_INDICATOR,'?') = '?' AND c.customer_sap_flag='N')) AND

( UPPER(:vDivision) = 'ALL' OR UPPER(:vDivision) = C.CUSTOMER_DIVISION) AND

( C.CUSTOMER_REGION = UPPER(:vRegion) OR UPPER(:vRegion) = 'ALL') AND

( UPPER(:vDistrict) = 'ALL' OR UPPER(:vDistrict) = C.CUSTOMER_DISTRICT) AND

( ((SUBSTR(('0000000000'),1,10 - CHARACTER_LENGTH(TRIM (:vCustomer)))) || TRIM(:vCustomer)) = '0000000ALL'

OR ((SUBSTR(('0000000000'),1,10 - CHARACTER_LENGTH(TRIM (:vCustomer)))) || TRIM(:vCustomer)) = C.CUSTOMER ) AND

( ( UPPER(:vSupercorp) = 'ALL' OR UPPER(:vSupercorp) = C.CUSTOMER_supercorp) OR

UPPER(:vSupercorp)=COALESCE(sp.COMBINED_SUPERCORP_ID,'*****'))

AND (sp.CHANNEL_TYPE IN (:vchannel) OR (:vchannel_all) = 'A')

UNION ALL

SELECT

SUM(Y.NET_VALUE_USD / Y.EXCHANGE_RATE*-1) VALUE1,

SUM(Y.QUANTITY*-1) QUANTITY,

SUM(Y.YTD_NET_VALUE / Y.EXCHANGE_RATE*-1) YTD_VALUE,

SUM(Y.YTD_QUANTITY*-1) YTD_QTY

FROM YTD_RETURNS Y LEFT OUTER JOIN PRODUCT_CODES P

ON Y.PRODUCT_CODE = P.MATERIAL_PRODUCT_CODE

INNER JOIN CUSTOMER_dist C

ON Y.CUSTOMER_KEY = C.CUSTOMER_KEY

LEFT OUTER JOIN supercorp_names sp

ON c.customer_supercorp=sp.SUPERCORP_ID

WHERE :vReportType2 = 'I' AND

Y.DATE_KEY = :VDATE_KEY AND

userid = :userid AND

P.PRODUCT_CODE_LINE = 'Strut' AND

((:vSalesType = 'N' AND COALESCE(P.BRAND_INDICATOR,'?') NOT IN ( 'E','?')) OR

(:vSalesType = 'R' AND COALESCE(P.BRAND_INDICATOR,'?')='R') OR

(:vSalesType = 'N' AND COALESCE(P.BRAND_INDICATOR,'?') = '?' AND c.customer_sap_flag='N')) AND

( UPPER(:vDivision) = 'ALL' OR UPPER(:vDivision) = C.CUSTOMER_DIVISION) AND

( C.CUSTOMER_REGION = UPPER(:vRegion) OR UPPER(:vRegion) = 'ALL') AND

( UPPER(:vDistrict) = 'ALL' OR UPPER(:vDistrict) = C.CUSTOMER_DISTRICT) AND

( ((SUBSTR(('0000000000'),1,10 - CHARACTER_LENGTH(TRIM (:vCustomer)))) || TRIM(:vCustomer)) = '0000000ALL'

OR ((SUBSTR(('0000000000'),1,10 - CHARACTER_LENGTH(TRIM (:vCustomer)))) || TRIM(:vCustomer)) = C.CUSTOMER ) AND

( ( UPPER(:vSupercorp) = 'ALL' OR UPPER(:vSupercorp) = C.CUSTOMER_supercorp) OR

UPPER(:vSupercorp)=COALESCE(sp.COMBINED_SUPERCORP_ID,'*****'))

AND (sp.CHANNEL_TYPE IN (:vchannel) OR (:vchannel_all) = 'A')

)TB

GROUP BY 1

QUALIFY ROW_NUMBER() OVER (ORDER BY 'A') = 1;

OPEN result_set;

END;

END ;

Kindly help me in this Issue, thanks in advance.

1 REPLY
Senior Apprentice

Re: Facing Problem to pass multiple values using 'IN' clause in static sql in Teradata store procedure

You can't pass multiple values to an IN using a single parameter without Dynamic SQL.

This should work:

WHERE  ',' || :vchannel || ',' LIKE '%,' || CHANNEL_TYPE || ',%' 

Dieter