INPUT STRING PARAMETER 1

[cristi22]

Hello all -

DB2 UDB SP.

Need help with passing a string into the SP.
The user could enter V, P or V,P

I got confused with ',' || .... , please help

Here is my code:

CREATE PROCEDURE BASYS.ILGW ( IN CHECK_TYPE varchar (10),
                              IN DATE_FROM DATE,
                              IN DATE_TO DATE )
    DYNAMIC RESULT SETS 1
------------------------------------------------------------------------
-- SQL Stored Procedure
    -- CHECK_TYPE
    -- DATE_FROM
    -- DATE_TO
------------------------------------------------------------------------
P1: BEGIN
    -- Declare cursor

     DECLARE stmt VARCHAR(4000);
     declare v_stmt  statement;


    DECLARE cursor1 CURSOR WITH RETURN FOR  v_stmt;

    set stmt =
'SELECT ILGW_VACATION.CHECK, ILGW_VACATION.EMPLOYER_ID, ILGW_VACATION.EMPLOYER_NAME, ILGW_VACATION.MEMBER_SSN, ILGW_VACATION.MEMBER_NAME,        ILGW_VACATION.CHECK_STATUS,        ILGW_VACATION.ISSUED_DATE,        ILGW_VACATION.AMT,        ILGW_VACATION.WHT_TYPE,         ILGW_VACATION.WHT_AMT,        WH_TYPE_DESC.WH_TYPE_DESC        FROM                ILGW_VACATION,WH_TYPE_DESC'
set stmt = stmt || 'WHERE'
set stmt = stmt || 'ILGW_VACATION.WHT_TYPE = WH_TYPE_DESC.WH_TYPE AND'
set stmt = stmt || 'ISSUED_DATE  >= ' [red]
set stmt = stmt ||  ''' || DATE_FROM  || ''' AND '
set stmt = stmt ||  'ISSUED_DATE  <='
set stmt = stmt ||  DATE_TO
set stmt = stmt || ' AND ILGW_VACATION.CHECK_STATUS IN  (''' ||   LTRIM(RTRIM(CHECK_TYPE))  ||  ''' ) ORDER BY WHT_TYPE ASC ' ; [/red]


    -- Cursor left open for client application

    prepare v_stmt from stmt;
    OPEN cursor1;

END P1

thanks a lot, cristi

 

[MarcLodge]

Cristi,
If you are trying to get a single quote concatenated either side of the DATE_FROM column, then you are one single quote short (I think). The statement:

set stmt = stmt || ''' || DATE_FROM || ''' AND '

should read:

set stmt = stmt || '''' || DATE_FROM || ''' AND '

(if that's not what you are trying to do, please let me know what you are trying to do, and what error you're getting)

Marc

 

[cristi22]

Thanks Marc for you time & help!!!!!

I am trying to accept the dates as input parms,
I was trying a diffirent combination of ''''', but nothing works.

The following code works w/o the dates & only for one check_type, but not for both (should accept V,P)

I've changed my SP a bit,

CREATE PROCEDURE BASYS.ILGW ( IN CHECK_TYPE varchar (10),
                              IN DATE_FROM DATE,
                              IN DATE_TO DATE )
    DYNAMIC RESULT SETS 1
------------------------------------------------------------------------
-- SQL Stored Procedure
    -- CHECK_TYPE
    -- DATE_FROM
    -- DATE_TO
------------------------------------------------------------------------
P1: BEGIN
    -- Declare cursor

     DECLARE stmt VARCHAR(4000);
     declare v_stmt  statement;


    DECLARE cursor1 CURSOR WITH RETURN FOR  v_stmt;

    set stmt =
'SELECT ILGW_VACATION.CHECK, ILGW_VACATION.EMPLOYER_ID,ILGW_VACATION.EMPLOYER_NAME,ILGW_VACATION.MEMBER_SSN, ILGW_VACATION.MEMBER_NAME, ILGW_VACATION.CHECK_STATUS,ILGW_VACATION.ISSUED_DATE, ILGW_VACATION.AMT, ILGW_VACATION.WHT_TYPE, ILGW_VACATION.WHT_AMT, WH_TYPE_DESC.WH_TYPE_DESC FROM ILGW_VACATION,WH_TYPE_DESC WHERE ILGW_VACATION.WHT_TYPE = WH_TYPE_DESC.WH_TYPE  AND ISSUED_DATE  >= DATE_FROM  AND ISSUED_DATE DATE_TO  AND ILGW_VACATION.CHECK_STATUS IN  (''' ||   LTRIM(RTRIM(CHECK_TYPE))  ||  ''' ) ORDER BY WHT_TYPE ASC' ;


    -- Cursor left open for client application

    prepare v_stmt from stmt;
    OPEN cursor1;

END P1

This the ERROR i am getting

A database manager error occurred.[IBM][CLI Driver][DB2/6000] SQL0104N An unexpected token "DATE_TO" was found following "ROM AND ISSUED_DATE". Expected tokens may include: "IN". SQLSTATE=42601

BASYS.ILGW - Roll back completed successfully.
BASYS.ILGW - Run failed.

 

[MarcLodge]

Cristi,
I think that you have two problems here. Firstly you need to find out how to get variables passed to the SP recognised by the SP, and secondly, we have the problem of the IN clause.

If I were doing this, I would take each problem separately and look at the DATE part first. Drop the CHECK_TYPE completely from the built SELECT, and try getting that bit to work. Once again, I have the problem that I don't know the language you are working with, but to relate it back to other languages I have dealt with, you do not need to put the variable in quote, but you do need to get it identified as a variable. In Cobol for instance, the variable would just hold the date value, and would be identfied by a :

For example:
Variable name is WS-DATE-VAR
Variable format is CHAR(10)
Variable value is 10-10-1990

When used in SELECT......
WHERE DATE_COL = :WS-DATE-VAR

I would imagine that the syntax of the language might be similar. Maybe somebody else can help, or you can consult Google or a manual. I'm off home now, but will sign on again later this evening. Let me know how you get on.

Marc

 

[cristi22]

Help.........

Here is how this code should look like, but i am still not getting the whole thing.

Any help is appreciated!!!!!

CREATE PROCEDURE BASYS.ILGW ( IN CHECK_TYPE varchar (10),
                              IN DATE_FROM DATE,
                              IN DATE_TO DATE )
    DYNAMIC RESULT SETS 1
------------------------------------------------------------------------
-- SQL Stored Procedure
    -- CHECK_TYPE
    -- DATE_FROM
    -- DATE_TO
------------------------------------------------------------------------
P1: BEGIN
    -- Declare cursor

     DECLARE stmt VARCHAR(4000);

     DECLARE p_chk_type VARCHAR(10);
     declare p_date_from VARCHAR(40);
     declare p_date_to VARCHAR(40);


     declare v_stmt  statement;


    DECLARE cursor1 CURSOR WITH RETURN FOR  v_stmt;

    DATE_TO

    set p_chk_type =  '(''' ||   LTRIM(RTRIM(CHECK_TYPE))  ||  ''' )';
    set p_date_from =   DATE_FROM  ;
    set p_date_to = DATE_TO ;

    set stmt =
'SELECT ILGW_VACATION.CHECK, ILGW_VACATION.EMPLOYER_ID,ILGW_VACATION.EMPLOYER_NAME,ILGW_VACATION.MEMBER_SSN, ILGW_VACATION.MEMBER_NAME, ILGW_VACATION.CHECK_STATUS,ILGW_VACATION.ISSUED_DATE, ILGW_VACATION.AMT, ILGW_VACATION.WHT_TYPE, ILGW_VACATION.WHT_AMT, WH_TYPE_DESC.WH_TYPE_DESC FROM ILGW_VACATION,WH_TYPE_DESC WHERE ILGW_VACATION.WHT_TYPE = WH_TYPE_DESC.WH_TYPE  AND ILGW_VACATION.CHECK_STATUS IN  (' ||  LTRIM(RTRIM(CHECK_TYPE))  ||  ') AND ISSUE_DATE>=? AND ISSUE_DATE<=?  ORDER BY WHT_TYPE ASC' ;


    -- Cursor left open for client application

    prepare v_stmt USING DATE_FROM, DATE_TO from stmt;
    OPEN cursor1;

END P1

ERROR:
BASYS.ILGW - Build started.
DROP SPECIFIC PROCEDURE BASYS.SQL060119162945790
BASYS.ILGW - Drop stored procedure completed.
Create stored procedure returns -104.
BASYS.ILGW: 38: [IBM][CLI Driver][DB2/6000] SQL0104N An unexpected token "prepare" was found following " ". Expected tokens may include: "OPEN". LINE NUMBER=38. SQLSTATE=42601

BASYS.ILGW - Build failed.
BASYS.ILGW - Roll back completed successfully.

 

[MarcLodge]

Cristi,
Just a thought, it's not case sensitive, is it?
Marc

 

[cristi22]

here is my code, still need to figure few things
CREATE PROCEDURE BASYS.ILGW ( IN CHECK_TYPE varchar (10),
IN DATE_FROM DATE,
IN DATE_TO DATE )
DYNAMIC RESULT SETS 1
------------------------------------------------------------------------
-- SQL Stored Procedure
-- CHECK_TYPE
-- DATE_FROM
-- DATE_TO
------------------------------------------------------------------------
P1: BEGIN
-- Declare cursor

DECLARE stmt VARCHAR(4000);

DECLARE p_chk_type VARCHAR(10);
declare p_date_from VARCHAR(40);
declare p_date_to VARCHAR(40);


declare v_stmt statement;


DECLARE cursor1 CURSOR WITH RETURN FOR v_stmt;

-- AND ISSUED_DATE >= DATE_FROM AND ISSUED_DATE DATE_TO

set p_chk_type = '(''' || LTRIM(RTRIM(CHECK_TYPE)) || ''' )';

set p_date_FROM = '(''' || char(date_to) || ''') ' ;

set stmt =
'SELECT ILGW_VACATION.CHECK, ILGW_VACATION.EMPLOYER_ID,ILGW_VACATION.EMPLOYER_NAME,ILGW_VACATION.MEMBER_SSN, ILGW_VACATION.MEMBER_NAME, ILGW_VACATION.CHECK_STATUS,ILGW_VACATION.ISSUED_DATE, ILGW_VACATION.AMT, ILGW_VACATION.WHT_TYPE, ILGW_VACATION.WHT_AMT, WH_TYPE_DESC.WH_TYPE_DESC FROM ILGW_VACATION,WH_TYPE_DESC WHERE ILGW_VACATION.WHT_TYPE = WH_TYPE_DESC.WH_TYPE AND ILGW_VACATION.CHECK_STATUS IN (' || LTRIM(RTRIM(CHECK_TYPE)) || ' ) AND ISSUED_DATE >= date(''' || char(date_from) || ''') AND ISSUED_DATE <= date(''' || char(date_to) || ''') ORDER BY WHT_TYPE ASC' ;


-- Cursor left open for client application

prepare v_stmt from stmt;

OPEN cursor1;

END P1