PASSING AN ARRAY TO STORED PROCEDURE FROM CRYSTAL 2

[cristi22]

Hello all –

Using: UDB DB2 V8 & CR 9

Really appreciate your help!

I am trying to set-up an SP with input parameters to accept multiple values from the CR front-end.

The input parm is string.
The user has an option to choose from the list of values (FUND), could select one, two or all.

CREATE PROCEDURE BASYS.WITHD_LIAB ( IN EMPLOYER VARCHAR(9),
                                   [COLOR=red]??? IN INCLUDE_FUND VARCHAR(250),[/color red]
                                    IN FROM_DATE DATE,
                                    IN TO_DATE DATE )
    DYNAMIC RESULT SETS 1
------------------------------------------------------------------------
-- SQL Stored Procedure
    -- EMPLOYER
    -- FUND
------------------------------------------------------------------------
P1: BEGIN
    -- Declare cursor

    DECLARE p_employer   VARCHAR(9);
    DECLARE p_incl_fund  VARCHAR(250);
    DECLARE p_exl_fund   VARCHAR(250);

    DECLARE cursor1 CURSOR WITH RETURN FOR

        SELECT

   	  CONT_DETAIL.EMPLOYER_NUMBER
	,CONTRACTS_FUND_TO_FUND.TO_RATE AS RATE

	,CONT_DETAIL.CONT_DETAIL_KEY

FROM
	CONT_DETAIL,
	CONT_DETAIL_FUND,
WHERE

[COLOR=red]	AND ltrim(RTrim(CONTRACTS_FUND_TO_FUND.TO_FUND)) in
	( 'CTP'
	 'WFP' , ‘LFP’) [/color red]

 

[MarcLodge]

Hi Cristi,

Have you run this code yet? At first view, there looks to me to be a couple fo syntax errors in it:

CONT_DETAIL_FUND seems to be referenced as CONTRACTS_FUND_TO_FUND.
CON_DETAIL_FUND in the FROM part appears to have an unnecessary comma after it.

Sorry if you are aware of these, but just thought I'd point them out.

Also, I'm not sure your code does what you are proposing from your description at the top. I'd be grateful for a bit of clarification in what you are trying to achieve.

Regards,
Marc

 

[cristi22]

Hi Marc -

Thank you for your time!

This is the simplified code, the SP executes with no errors.

On the crystal report front-end the user has an option with a drop down box & number of funds to select (Etc, CLP, CTP, WFP).
The user could select CLP or CTP, CLP, CTP & WFP or nothing.

I am trying to have a variable input array & depending on the parameter build my criteria.

Somthing like that:

IN_FUND [1,3]

In the procedure

SELECT …
FROM …
WHERE
FUND = IN_FUND (could 1,2,3 or NULL)

Thanks,
Cristi

 

[cristi22]

PREPARE SQL Statement help or any other ideas!!
Anyone???

Thanks,
cristi

 

[MarcLodge]

Cristi,

Can you use the IN clause with the variable, formatting the variable correctly for the SQL statement?

I've not used the langauge that your SP is written in so don't know how flexible it is. Can you test the value of the variable easily, and build SQL (either dynamically with the PREPARE statement as you suggest - assuming that you can run dynamic SQL in an SP - or with logic)?

Sorry about not getting back sooner, didn't look much at TT over the weekend.

Marc

 

[BrianTyler]

Is your problem that you are trying to have the list of funds passed as a single variable?

I have not tried it in Stored procedures, but when hosted in Cobol, each of the items in the list must be declared as separate Host variables. It seems that the usage of parameters in DB2 SQL is not as simple as using Placeholders.

I think that your 'front-end' should have a list of separate fields for each possible fund, and the user would clear any not required. The 'IN' list would then contain variables for each fund that will be populated to the fund name or spaces.

I hope I have understood the problem and you understand this rambling.

Good Luck

brian

 

[cristi22]

Thanks all for your time and help!!!

I can use IN clause with the variable. I am using the standard SQL for UDB DB2 8. It should not be a problem to test the value of the var or build dyamic (i've never used it) SQL.

On the CR front-end I have a list of values. Thant's why, I was thinking of passing an array. The user should be able to select one, two, three or none values.

It does not have to be an array, anything that will handle multiple parms will do!

Really appreciate your help!

 

[cristi22]

i've tried to use the var, it did not work...
please help!

 

[MarcLodge]

Cristi,

I'm afraid all the SPs I've written have been in Cobol on a mainframe where you have a lot of flexibility. What is the language you are writing the SP in called?

Marc

 

[cristi22]

Hi Marc -

Thanks for you time!

it's a standard SQL for DB2 UDB

 

[MarcLodge]

Cristi,
I'm sorry to say that I'm not sure I can help you further as I've never written an SP in that way. If If was writing it in Cobol, I would pass the varchar, examine it, and split it into 3 variables. I would then probably use a CASE statement in the SQL to check each of the variables for spaces and include or exclude them as appropriate.

From your SQL in the very first post, it looks as if you can define the 3 variables easy enough, but I'm not sure how you would then split the varchar and assign the values to each of the fields.

Sorry to not be of more help, but this is outside of my scope of experience (sadly for me!).

If anybody else knows a way, please step forward......

Marc

 

[cristi22]

thanks Marc... still working on it