Advertisement

05.26.2008 at 11:04PM PDT, ID: 23433965
[x]
Attachment Details

User defined function which can be called in Stored Procedure

Asked by chivas007 in DB2 Database

Tags: DB2, SQL DB2

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.COMPANY_NM,ISSUING_CO_NO,a.ORDER_SQN,(case when SBSTT_MAJOR_CLASS_CD  is null then a.MAJOR_CLASS_CD else SBSTT_MAJOR_CLASS_CD END )MAJOR_CLASS_CD,a.NumericRows from (

   select i.PRODUCT_CD,i.ADMITTED_IN,i.COMPANY_NO,c.COMPANY_NM,c.ISSUING_CO_NO,i.ORDER_SQN,m.MAJOR_CLASS_CD MAJOR_CLASS_CD,m.STATE_CD,d.NumericRows
   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_PRODUCT_CD)
 and
 m.STATE_CD = coalesce(stateCd,m.STATE_CD)
   and m.EFFECTIVE_DT <= effectiveDt
   and m.EXPIRATION_DT >= effectiveDt
   and m.COVERAGE_TYPE_CD = coalesce(coverageCd,m.COVERAGE_TYPE_CD)
   and i.PRODUCT_CD = m.PPS_PRODUCT_CD
   and i.STATE_CD = m.STATE_CD
   and i.ADMITTED_IN=  coalesce(admittedIn,i.ADMITTED_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 P1Start Free Trial
[+][-]05.26.2008 at 11:22PM PDT, ID: 21649710

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 7-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]05.26.2008 at 11:26PM PDT, ID: 21649733

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 7-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]05.26.2008 at 11:28PM PDT, ID: 21649740

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 7-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]05.27.2008 at 02:56AM PDT, ID: 21650594

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 7-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]05.27.2008 at 03:07AM PDT, ID: 21650629

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 7-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]05.27.2008 at 03:45AM PDT, ID: 21650755

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 7-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]05.27.2008 at 04:15AM PDT, ID: 21650860

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 7-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]05.27.2008 at 05:18AM PDT, ID: 21651161

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 7-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]05.27.2008 at 05:27AM PDT, ID: 21651212

View this solution now by starting your 7-day free trial. Setting up your free trial is quick, easy, and secure. We will return you to this solution, unlocked, when you're done.

 

About this solution

Zone: DB2 Database
Tags: DB2, SQL DB2
Sign Up Now!
Solution Provided By: momi_sabag
Participating Experts: 1
Solution Grade: B
 
 
[+][-]05.27.2008 at 05:48AM PDT, ID: 21651372

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 7-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]05.27.2008 at 05:55AM PDT, ID: 21651417

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 7-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]05.27.2008 at 06:08AM PDT, ID: 21651519

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 7-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]05.27.2008 at 06:11AM PDT, ID: 21651546

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 7-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]05.27.2008 at 06:24AM PDT, ID: 21651612

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 7-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]05.27.2008 at 06:36AM PDT, ID: 21651711

Assisted solutions are selected by the member who asked the question as a comment that contributed to their question's solution.

Start your 7-day free trial to view this Assisted Solution or ask the Experts your question.

 
[+][-]05.27.2008 at 07:25AM PDT, ID: 21652135

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 7-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]05.27.2008 at 11:25PM PDT, ID: 21658113

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 7-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]05.28.2008 at 03:38AM PDT, ID: 21659170

Assisted solutions are selected by the member who asked the question as a comment that contributed to their question's solution.

Start your 7-day free trial to view this Assisted Solution or ask the Experts your question.

 
[+][-]05.28.2008 at 03:57AM PDT, ID: 21659249

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 7-day free trial to view this Author Comment or ask the Experts your question.

 
 
Loading Advertisement...
20080716-EE-VQP-32 / EE_QW_2_20070628