Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     passing multiselect to sql

Read-Only Read-Only Topic
Go
Search
Notify
Tools
passing multiselect to sql
 Login/Join
 
Member
posted
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
 
Posts: 4 | Location: San Antonio,Tx | Registered: May 05, 2006Report This Post
Expert
posted Hide Post
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
 
Posts: 2723 | Location: Ann Arbor, MI | Registered: April 05, 2006Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     passing multiselect to sql

Copyright © 1996-2020 Information Builders