I have a stored procedure as pasted here. This takes divNo as a parameter which is comma separated string like (81,75,68).
I need to parse this string and get the individual values 81,75 etc in a temporary table through a user defined function and call this function in a stored procedure and fire a select query like s.DIVISION_CD IN (divNo).
Since s.DIVISION_CD is integer column hence I cannot use directly s.DIVISION_CD IN (divNo) .
It gives datatype incompatible problem.
Also this same function will be called in the place of (SELECT NumericRows FROM TABLE (StringToNumber(divNo) ) t ) in the stored procedure which takes a left outer join with the above query.
Please note that I need this function and its usage in Stored Procedure strictly in DB2 and not oracle.
--------------------------
--------
CREATE PROCEDURE "AHRTLP"."GET_COMPANIES" ( IN stateCd CHARACTER(2),
IN coverageCd CHARACTER(3),
IN admittedIn CHARACTER(1),
IN productCd CHARACTER(5) ,
IN divNo varchar(2000),
IN effectiveDt DATE )
DYNAMIC RESULT SETS 1
--------------------------
----------
----------
----------
----------
------
-- SQL Stored Procedure
-- stateCd
-- coverageCd
-- admittedIn
-- productCd
-- divNo
-- effectiveDt
--------------------------
----------
----------
----------
----------
------
P1: BEGIN
-- Declare cursor
DECLARE cursor1 CURSOR WITH RETURN FOR
select a.PRODUCT_CD,a.ADMITTED_IN
,a.COMPANY
_NO,a.COMP
ANY_NM,ISS
UING_CO_NO
,a.ORDER_S
QN,(case when SBSTT_MAJOR_CLASS_CD is null then a.MAJOR_CLASS_CD else SBSTT_MAJOR_CLASS_CD END )MAJOR_CLASS_CD,a.NumericR
ows from (
select i.PRODUCT_CD,i.ADMITTED_IN
,i.COMPANY
_NO,c.COMP
ANY_NM,c.I
SSUING_CO_
NO,i.ORDER
_SQN,m.MAJ
OR_CLASS_C
D MAJOR_CLASS_CD,m.STATE_CD,
d.NumericR
ows
from AHRTLP.TPPS_PRODUCT_MCC m,AHRTLP.TPROD_ISSUING_CO i,AHRTLP.TANNUAL_STMT_CO c,
(SELECT NumericRows FROM TABLE ( "AHRTLP"."StringToNumber"(
divNo) ) t ) d
where m.PPS_PRODUCT_CD = coalesce(productCd,m.PPS_P
RODUCT_CD)
and
m.STATE_CD = coalesce(stateCd,m.STATE_C
D)
and m.EFFECTIVE_DT <= effectiveDt
and m.EXPIRATION_DT >= effectiveDt
and m.COVERAGE_TYPE_CD = coalesce(coverageCd,m.COVE
RAGE_TYPE_
CD)
and i.PRODUCT_CD = m.PPS_PRODUCT_CD
and i.STATE_CD = m.STATE_CD
and i.ADMITTED_IN= coalesce(admittedIn,i.ADMI
TTED_IN)
and i.COMPANY_NO = c.company_no
) a
left outer join AHRTLP.TSUBSTITUTE_MJC s
on a.COMPANY_NO = s.COMPANY_NO
and a.STATE_CD = s.STATE_CD
and s.EFFECTIVE_DT <= effectiveDt
and s.EXPIRATION_DT >= effectiveDt
and s.DIVISION_CD IN (divNo)
and s.MAJOR_CLASS_CD = a.MAJOR_CLASS_CD
order by a.PRODUCT_CD,a.ADMITTED_IN
,a.COMPANY
_NO;
-- Cursor left open for client application
OPEN cursor1;
END P1
Start Free Trial