Focal Point
passing multiselect to sql

This topic can be found at:
https://forums.informationbuilders.com/eve/forums/a/tpc/f/7971057331/m/5961074882

June 19, 2008, 11:43 AM
davmayf
passing multiselect to sql
sql needs to use 'in' to do a multiselect, our html returns an 'or' multiselect. here is a way to convert the 'or' string to a ',' string for a sql multiselect.
to convert company code 'or' string to ',' string...

DEFINE FILE SAP_CODES
COMPANYCODE1/A200 = &COMPANYCODE.QUOTEDSTRING;
COMPANYCODE2/A200 = STRREP(200,COMPANYCODE1,2,'OR',1,',',200,COMPANYCODE2);
END
TABLE FILE SAP_CODES
PRINT COMPANYCODE1 COMPANYCODE2
BY CompanyCode NOPRINT
WHERE RECORDLIMIT EQ 1
ON TABLE HOLD AS HLD_COMPANY FORMAT ALPHA
END
-RUN
-*EXIT
-READ HLD_COMPANY &COMPANYCODE1.A200 &COMPANYCODE2.A200
-TYPE &COMPANYCODE1
-TYPE &COMPANYCODE2
-RUN
June 19, 2008, 02:43 PM
GinnyJakes
Here is runnable code that you could alter to suit your purpose:
-* File ar_where_loop.fex
-SET &DIVISION = &DIV.(OR(<AM,AM>,<CHE,CHE>,<CHW,CHW>,<CP,CP>,<CT,CT>,<CV,CV>,<FOR,FOR>,<GB,GB>,<GC,GC>,<GU,GU>,<IR,IR>,<KI,KI>,<KSE,KSE>,<KSW,KSW>,<KY,KY>,<LS,LS>,<MR,MR>,<MTN,MTN>,<MTS,MTS>,<NC,NC>,<NE,NE>,<NEE,NEE>,<NEW,NEW>,<NK,NK>,<NWE,NWE>,<NWN,NWN>,<NWS,NWS>,<NWW,NWW>,<OT,OT>,<PRN,PRN>,<PRS,PRS>,<PRW,PRW>,<RR,RR>,<SC,SC>,<SFE,SFE>,<SFW,SFW>,<SO,SO>,<SWE,SWE>,<SWW,SWW>,<TCE,TCE>,<TCS,TCS>,<TCW,TCW>,<TX,TX>,<UP,UP>)).Select one or more DIVISIONS. ;

-*-TYPE &DIV

-***** EXAMPLE of selection ****
-* DIV = 'AM' OR 'CHW' OR 'CP' OR 'FOR'

-*-EXIT


-*  Loop thru this list & add single quotes and commas where necessary around options - to go in the SQL IN statement
-*  NEED TO TAKE THE "OR" OUT OF THE STRING PASSED FROM THE WEB SERVER

-SET &IVTGTYP=&DIV;
-SET &LENGTH_LEFT  = &IVTGTYP.LENGTH;
-SET &WHR = '  ';

-DIVLOOP
-SET &SP_POS=POSIT(&IVTGTYP,&LENGTH_LEFT,' ',1,'I2');
-* IF THERE ARE NO MORE ORS, YOU HAVE THE LAST ONE AND CAN EXIT THE LOOP.
-IF &SP_POS NE 0 GOTO DIVGET;
-SET &DIVTOK=&IVTGTYP;
-GOTO DIVEND
-DIVGET
-SET &TOKEND=&SP_POS-1;
-SET &TOKLEN=&SP_POS-1;
-SET &TOKFMT='A'|&TOKLEN;
-*-TYPE SP_POS=&SP_POS TOKEND=&TOKEND TOKLEN=&TOKLEN
-* GET THE FIRST DIVISION IN THE STRING
-SET &DIVTOK  = SUBSTR(&LENGTH_LEFT, &IVTGTYP, 1, &TOKLEN, &TOKLEN, '&TOKFMT');
-*-TYPE DIVTOK=&DIVTOK
-* MOVE THE STARTING POSITION PAST THE EXTRACTED DIVISION AND THE ' OR ' AND GET THE REST OF THE STRING.
-SET &STR_START=&TOKLEN+5;
-SET &STR_LENGTH=&LENGTH_LEFT;
-SET &LENGTH_LEFT=&LENGTH_LEFT-&TOKLEN-4;
-*-TYPE LENGTH LEFT=&LENGTH_LEFT STR_START=&STR_START STR_LENGTH=&STR_LENGTH
-SET &STRFMT='A'|&LENGTH_LEFT;
-*-TYPE STRFMT=&STRFMT
-SET &IVTGNEW=SUBSTR(&STR_LENGTH,&IVTGTYP,&STR_START,&STR_LENGTH,&LENGTH_LEFT,'&STRFMT');
-*-TYPE &IVTGNEW
-SET &IVTGTYP=&IVTGNEW;
-SET &WHR  =  &WHR || &DIVTOK || ',';
-GOTO DIVLOOP
-DIVEND
-SET &WHR  =  &WHR || &DIVTOK;
-* Parameter is set to pass to SQL select statement
-*-SET &FWHR  =  &WHR ;
-TYPE &WHR

Just paste it into a fex and do a multi-select and see what you get.

Also please update your profile signature to include your product suite, releases, and platforms so that we may better help you.


Ginny
---------------------------------
Prod: WF 7.7.01 Dev: WF 7.6.9-11
Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google