Hello all,

I am posting here as a last resort...I have tried every avenue that I know and now I put myself at your mercy.

I have a fex that passes values to a master file based on a stored procedure. I use an if statement in the fex connecting the proc parameter to an amper variable - the following is obviously single select, and works fine..

 
IF PRC_MULTI_LOCATION_TEST.INPUT.@LOCPARAM EQ &LOCPARAM

The problem occurs when I attempt to use a multiselect OR in this scenario, scripted this way:

  
IF PRC_MULTI_LOCATION_TEST.INPUT.@LOCPARAM EQ &LOCPARAM.(OR(FIND CODE IN VW_USER_LOCATION_MASTER)).LOCPARAM.

When I run the fex with the above multiselect in place I get the following error:

(FOC1631) MORE THAN ONE IF/WHERE TEST FOR FIELD @LOCPARAM

I need to pass multiple values to the stored procedure. What is the best method of doing this? I have attempted JavaScript on the HTML launch page side, various experiments with DEFINE in the fex...no luck. Has anyone out there experienced this, and if so have you conquered it? Any help with this would be appreciated!

This message has been edited. Last edited by: Kerry,

Hi JRenner,

Could you please clarify the following things ?

stored procedure - Is the WF stored procedure or DB stored procedure..

Multi Select - Are you mentioning about the Multi Select List box which passes you the parameter value from an user input screen ?

JRenner,

Just convert so you can use SQL IN syntax:

  
-DEFAULTS &KEY_SORT1 = '', &KEY_SORT2 = '', &SQL_VALUE = ''
-SET &QT = '''';
-*********************************
-* Multiple Incoming Values
-*********************************
-SET &MAIN_SORT = &QT | 'VALUE1' | &QT  | ' OR ' | &QT | 'VALUE2' | &QT | ' OR ' | &QT | 'VALUE3' | &QT | ' OR ' | &QT | 'VALUE4' | &QT ;
-*********************************
-* Single Incoming Value
-*********************************
-*SET &MAIN_SORT = &QT | 'VALUE1' | &QT  ;
-*********************************
-IF  &MAIN_SORT    EQ ''     GOTO SKIP_BYS ELSE
-IF  &MAIN_SORT OMITS ' OR ' GOTO ONE_VALUE;
-*********************************
-SET &SORT_LEN  = &MAIN_SORT.LENGTH;
-SET &KEY_SORT1 = STRREP(&SORT_LEN, &MAIN_SORT, 4, ' OR ', 1, ',', &SORT_LEN, 'A&SORT_LEN.EVAL');
-*********************************
-* Strip Quotes for Numeric Values
-*********************************
-SET &KEY_SORT2 = STRREP(&SORT_LEN, &KEY_SORT1, 1, ''''  ,0,  'X', &SORT_LEN, 'A&SORT_LEN.EVAL');
-TYPE &KEY_SORT2
-*********************************
-SET &WHERE1 = 'WHERE COLUMN_NAME IN (' | &KEY_SORT1 || ');';
-GOTO NO_MORE
-*********************************
-ONE_VALUE
-*********************************
-SET &WHERE1 = 'WHERE COLUMN_NAME IN (' | &MAIN_SORT || ');';
-*********************************
-NO_MORE
-TYPE &WHERE1
-*********************************
-* Pass this to SQL SP
-*********************************
-SET &SQL_VALUE = IF &KEY_SORT1 EQ '' THEN '(' || &MAIN_SORT || ')' ELSE '(' || &KEY_SORT1 || ')';
-SKIP_BYS
-EXIT

hth

Thanks to both of you for your inquiries - sorry it took so long to get back on here, with projects pulling me around...well, you guys know how that is.

@Tom: your solution, coupled with another I found here on the forums, solved my issue. Thanks so much for that. Now all I need to do is get this report (a compound report) outputting properly given the breakout of the multiple locations, I'd be set - I get the first portion of the report to output split by location, but the two other pieces still bundle at the bottom of the page (spreadsheet actually). It'll take some doing, but I will figure it out.

Thanks again!