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     [CLOSED] Allow user to type in comma-separated list

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[CLOSED] Allow user to type in comma-separated list
 Login/Join
 
Silver Member
posted
We are trying to emulate an existing report that was developed with Java, using WebFocus. The report allows the user to type in a value at a prompt (the list of possible values is prohibitively long), or enter a comma-separated list of values. Is there a way to do this using Resource Layout Painter or some other method using the FOCUS language?

This message has been edited. Last edited by: Kerry,
 
Posts: 44 | Location: St. Louis | Registered: September 17, 2004Report This Post
Guru
posted Hide Post
I've done it with Dialog Manager. You use ARGLEN to find out how long the string is, use GETTOK inside a loop to to break apart the values (at the commas), and place them inside the same kind of &variable array a multi-select list box would create.
 
Posts: 391 | Location: California | Registered: April 14, 2003Report This Post
Silver Member
posted Hide Post
Here is a dialog manager code sample I have that changes 1,2,3,4 to ('1','
2','3','4') for IN() lists for SQL Passthrough. If you change the part that builds the variable to create &CURTOK.&CNTR then you would build an array (&CNTR is not in this example - would be easy to add) or you could have it rebuild the data any way you wanted.


-* ============================================= />-* System : WebFOCUS utility to add "s around text lists *
-* Program : ALPHLIST - Alpha List for SQL WHERE IN clause *
-* Purpose : Convert comma delimited list to " enclosed delimited list *
-* *
-* Author : drew *
-* Written : 09/2004 *
-* *
-* Required input: &&INLIST - the input list of data with comma delimiters *
-* *
-* Produced output: &&OUTLIST - the output list with commas and quotes *
-*
-*----------------------------------------------------------------- />-* M A I N T E N A N C E *
-* Date Changed By DESCRIPTION *
-* 09/21/2004 drew New Code requested by budget *
-* 11/19/2004 drew changed " to ' to qualify list elements *
-* 11/22/2004 drew Vish requested that preceding or trailing spaces *
-* be eliminated from list items in case users *
-* enter them in HTML forms. *
-* ======================================================= />
-* account list variables
-DEFAULTS &&INLIST = ''
-SET &&OUTLIST = '(';
-SET &TEMPLIST = '';
-SET &LISTFORM = '';
-* first and last loop flags
-SET &FIRST = 1;
-SET &LAST = 0;

-* move the incoming accounts to a variable we can cut up
-SET &LISTFORM = &&INLIST ;

-* start the loop
-LOOPTOP

-* findout how big the incoming string is, if empty - leave
-* -SET &LISTLEN = ARGLEN(100,&LISTFORM,'I3');
-IF &LISTFORM.LENGTH EQ 0 GOTO ENDLIST ;

-* find the delimiter
-SET &ANYCOM = POSIT(&LISTFORM,&LISTFORM.LENGTH,',',1,'I3');

-* if it's the first loop through - no delimeter at the front of the out string
-SET &NDELIM = IF &FIRST EQ 1 THEN '' ELSE ',';
-* reset first loop flag to false
-SET &FIRST = 0;

-* if we are looking at the final record, use the string length to parse
-SET &LAST = IF &ANYCOM EQ 0 THEN 1 ELSE 0;
-SET &ANYCOM = IF &ANYCOM EQ 0 THEN &LISTFORM.LENGTH +1 ELSE &ANYCOM;
-SET &LSTCHAR = IF &ANYCOM EQ 0 THEN &LISTFORM.LENGTH +1 ELSE &ANYCOM -1;

-* in case they entered an extra comma in the list
-IF &LSTCHAR EQ 0 GOTO NULLITEM ;

-* put the current token into a variable
-SET &CURTOK = SUBSTR(&LISTFORM.LENGTH,&LISTFORM,1,&ANYCOM -1,&LSTCHAR,'A140') ;
-IF &CURTOK.LENGTH EQ 0 GOTO BLNKITEM ;

-* left justify the token in case it has preceding spaces
-* trailing spaces will be elimited by the hard concat
-IF EDIT(&CURTOK,'9') NE ' ' GOTO NOSPACE ;
-* -SET &TOKLEN = ARGLEN(100,&CURTOK,'I3');
-* in case they entered all spaces ...
-SET &OUTFMT = 'A' || EDIT(&CURTOK.LENGTH) ;
-SET &CURTOK = LJUST(&CURTOK.LENGTH,&CURTOK,&OUTFMT) ;
-NOSPACE

-* build the output string appending new list items
-SET &&OUTLIST = &&OUTLIST || &NDELIM || '''' || &CURTOK || '''';
-* this is where you have the &CURTOK to work
-* with - you can do anything you want with it
-* here.


-* if we have no more delimters, we are done
-IF &LAST EQ 1 GOTO ENDLIST ;

-* remove the part of the list we just used for the next loop
-BLNKITEM
-SET &TEMPLIST = &LISTFORM ;
-SET &LISTFORM =
- SUBSTR(&LISTFORM.LENGTH,&TEMPLIST,&ANYCOM +1,&LISTFORM.LENGTH,&LISTFORM.LENGTH - &ANYCOM +1,'A100');

-* loop
-GOTO LOOPTOP

-* if they entered an extra comma just remove it and loop
-NULLITEM
-SET &TEMPLIST = &LISTFORM ;
-SET &LISTFORM =
- SUBSTR(&LISTFORM.LENGTH,&TEMPLIST,2,&LISTFORM.LENGTH,
&LISTFORM.LENGTH -1,'A100');
-GOTO LOOPTOP

-ENDLIST
-SET &&OUTLIST = &&OUTLIST || ')' ;

hth,

drew

This message has been edited. Last edited by: <Mabel>,
 
Posts: 46 | Location: San Francisco, California | Registered: April 14, 2003Report This Post
Member
posted Hide Post
Here is vbscript that allows you to validate before running the report..change the submit button to run the script...The input box is a textarea so we clean up line breaks, comma's,tabs, spaces and semi-colons. This allows our users to paste a list from Outlook/Excel/Text file or just type in a bunch of stuff.

<scr ipt type="text/vbscript">
Sub Validate()

// Remove tailing/leading spaces and add comma to end
tranList = trim(document.form1.TranList.value) & ","

// Change all line breaks to commas
tranList = replace(tranList,vbCRLF,",")

// Change all spaces to commas
tranList = replace(tranList," ",",")

// change all ; to commas
tranList = replace(tranList,";",",")

// change all tabs to commas
tranList = replace(tranList,vbTAB,",")

// Loop until all duplicate commas are single
do until Instr(tranList,",,") = 0
tranList = replace(tranList,",,",",")
loop

// Remove the trailing comma from string
tranList = mid(tranList,1,(len(tranList)-1))

// Make it into an array
aTransits = split(tranList,",")

// Loop thru each value and validate
for each Transit in aTransits
// Validate lenght is not greater than five...remove if not required
if len(Transit) > 5 Then
MsgBox "The value for " & Transit & "is to long"
Exit Sub
end if

// remove duplicates from list
if instr(cleanTransits,Transit) = 0 Then
// Add to a clean list of parms
cleanTransits = cleanTransits & Transit & ", "

end if

next

// Remove the last comma from the string
cleanTransits = Left(trim(cleanTransits),(len(trim(cleanTransits))-1))

//Update the textarea value
document.form1.TranList.value = cleanTransits
// run the focexec...comment out well testing
document.form1.submit()

End Sub


</scr ipt>

Note: Fix script tags by removing space in the word scr ipt.

Jayem Nolan
RBC
 
Posts: 10 | Location: Montreal | Registered: May 25, 2004Report This Post
Silver Member
posted Hide Post
Thank you both for your help.
 
Posts: 44 | Location: St. Louis | Registered: September 17, 2004Report This Post
Silver Member
posted Hide Post
Thank you all for your help!
 
Posts: 44 | Location: St. Louis | Registered: September 17, 2004Report This Post
Member
posted Hide Post
Good afternoon

I've used the above code many times allowing the user to enter a string of accounts in an html page and retrieve details and it has worked great. Thus far all my procedures have been sql passthroughs using Teradata, however I am now needing to use DB2 tables in the passthrough. When I run the procedure I receive the following message:

(FOC1400) SQLCODE IS -401 (HEX: FFFFFE6F)
: [42818] [IBM][CLI Driver][DB2] SQL0401N The data types of the operands
: for the operation "" are not compatible. SQLSTATE=42818
L (FOC1405) SQL PREPARE ERROR.
(FOC1822) WARNING. INVALID SYMBOL:
BYPASSING TO END OF COMMAND

Any ideas on why this code would work with Teradata but not DB2? The field type is CHAR10 in both databases.

Thanks for your suggestions
 
Posts: 9 | Location: Texas | Registered: June 09, 2008Report This Post
Guru
posted Hide Post
I use this method but it's not SQL pass thru.


  
-IF &myJOB_ID CONTAINS ',' THEN MULTIJOB ELSE SINGLEJOB;

-MULTIJOB
-SET &VALS = &myJOB_ID;
-SET &VALS2 = &VALS.LENGTH + 1365;
-SET &VALS3 = 'A' | &VALS2;
-SET &VALSX = STRREP(&VALS.LENGTH,&VALS,1,',',6,''' OR ''',&VALS2,'&VALS3');
-SET &VALSX = TRUNCATE(&VALSX);
-SET &myJOB_ID =  '''' | &VALSX  | '''';
-SINGLEJOB



Now I can use &myJOB_ID in WHERE clause.

Thanks,
Sayed


WF 8.x and 7.7.x Win/UNIX/AS400, MRE/Portal/Self-Service, IIS/Tomcat, WebSphere, IWA, Realmdriver, Active Directory, Oracle, SQLServer, DB2, MySQL, JD Edwards, E-BIZ, SAP BW, R/3, ECC, ESSBASE
 
Posts: 285 | Location: Texas | Registered: June 27, 2006Report This Post
Virtuoso
posted Hide Post
Make sure to add
-SET &ECHO=ALL;

before your SQL passthru command and analyze exactly how your &variables are being resolved. That'll allow you to spot any invalid values in the final SQL command that will be sent to your database.



Prod/Dev: WF Server 8008/Win 2008 - WF Client 8008/Win 2008 - Dev. Studio: 8008/Windows 7 - DBMS: Oracle 11g Rel 2
Test: Dev. Studio 8008 /Windows 7 (Local) Output:HTML, EXL2K.
 
Posts: 1533 | Registered: August 12, 2005Report This Post
Member
posted Hide Post
Thanks for your responses.

It turns out to be a syntax issue. After using t
-SET &ECHO=ALL, I realized that the code was not including the single quotation marks around the account numbers as I had thought. I didn't have any issues before because Teradata didn't need the single quotes, however DB2 does.

Any ideas why the single quote marks would not be being inserted in the procedure?

Thanks
 
Posts: 9 | Location: Texas | Registered: June 09, 2008Report This Post
Expert
posted Hide Post
quote:
Any ideas why the single quote marks would not be being inserted in the procedure?
Not without seeing some code.


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Member
posted Hide Post
Here's the code I'm using. It's basically the same as presented at the beginning of post.

-DEFAULTS &&INLIST = ''
-SET &&OUTLIST = '(';
-SET &TEMPLIST = '';
-SET &LISTFORM = '';
-* first and last loop flags
-SET &FIRST = 1;
-SET &LAST = 0;

-* move the incoming accounts to a variable we can cut up
-SET &LISTFORM = &&INLIST ;

-* start the loop
-LOOPTOP

-* findout how big the incoming string is, if empty - leave
-SET &LISTLEN = ARGLEN(160,&LISTFORM,'I3');
-IF &LISTFORM.LENGTH EQ 0 GOTO ENDLIST ;

-* find the delimiter
-SET &ANYCOM = POSIT(&LISTFORM,&LISTFORM.LENGTH,',',1,'I3');

-* if it's the first loop through - no delimeter at the front of the out string
-SET &NDELIM = IF &FIRST EQ 1 THEN '' ELSE ',';
-* reset first loop flag to false
-SET &FIRST = 0;

-* if we are looking at the final record, use the string length to parse
-SET &LAST = IF &ANYCOM EQ 0 THEN 1 ELSE 0;
-SET &ANYCOM = IF &ANYCOM EQ 0 THEN &LISTFORM.LENGTH +1 ELSE &ANYCOM;
-SET &LSTCHAR = IF &ANYCOM EQ 0 THEN &LISTFORM.LENGTH +1 ELSE &ANYCOM -1;

-* in case they entered an extra comma in the list
-IF &LSTCHAR EQ 0 GOTO NULLITEM ;

-* put the current token into a variable
-SET &CURTOK = SUBSTR(&LISTFORM.LENGTH,&LISTFORM,1,&ANYCOM -1,&LSTCHAR,'A140') ;
-IF &CURTOK.LENGTH EQ 0 GOTO BLNKITEM ;

-* left justify the token in case it has preceding spaces
-* trailing spaces will be elimited by the hard concat
-IF EDIT(&CURTOK,'9') NE ' ' GOTO NOSPACE ;
-SET &TOKLEN = ARGLEN(100,&CURTOK,'I3');
-* in case they entered all spaces ...
-SET &OUTFMT = 'A' || EDIT(&CURTOK.LENGTH) ;
-SET &CURTOK = LJUST(&CURTOK.LENGTH,&CURTOK,&OUTFMT) ;
-NOSPACE

-* build the output string appending new list items
-SET &&OUTLIST = &&OUTLIST || &NDELIM || '''' || &CURTOK || '''';
-* this is where you have the &CURTOK to work
-* with - you can do anything you want with it
-* here.


-* if we have no more delimters, we are done
-IF &LAST EQ 1 GOTO ENDLIST ;

-* remove the part of the list we just used for the next loop
-BLNKITEM
-SET &TEMPLIST = &LISTFORM ;
-SET &LISTFORM =
- SUBSTR(&LISTFORM.LENGTH,&TEMPLIST,&ANYCOM +1,&LISTFORM.LENGTH,&LISTFORM.LENGTH - &ANYCOM +1,'A100');

-* loop
-GOTO LOOPTOP

-* if they entered an extra comma just remove it and loop
-NULLITEM
-SET &TEMPLIST = &LISTFORM ;
-SET &LISTFORM =
- SUBSTR(&LISTFORM.LENGTH,&TEMPLIST,2,&LISTFORM.LENGTH,
&LISTFORM.LENGTH -1,'A100');
-GOTO LOOPTOP

-ENDLIST
-SET &&PTRN = &&OUTLIST || ')' ;


Thanks for your help
 
Posts: 9 | Location: Texas | Registered: June 09, 2008Report This Post
Virtuoso
posted Hide Post
What do you get if you add the following line at the very end of your loop:

-TYPE &&OUTLIST


Also, since you did not post the actual code you are running in SQL passthru it's hard to guess which &acp;variables you are currently using.



Prod/Dev: WF Server 8008/Win 2008 - WF Client 8008/Win 2008 - Dev. Studio: 8008/Windows 7 - DBMS: Oracle 11g Rel 2
Test: Dev. Studio 8008 /Windows 7 (Local) Output:HTML, EXL2K.
 
Posts: 1533 | Registered: August 12, 2005Report This Post
Virtuoso
posted Hide Post
In addition, please read this for an indication as to how code should be posted. Smiler



Prod/Dev: WF Server 8008/Win 2008 - WF Client 8008/Win 2008 - Dev. Studio: 8008/Windows 7 - DBMS: Oracle 11g Rel 2
Test: Dev. Studio 8008 /Windows 7 (Local) Output:HTML, EXL2K.
 
Posts: 1533 | Registered: August 12, 2005Report This Post
Member
posted Hide Post
The variable I'm using in my SQL WHERE Statement is:

C.PTRN_CD IN (&PTRN))

When I run -TYPE &&OUTLIST I get '('
When I run -TYPE &&PTRN I retrieve my account numbers separated by commas
 
Posts: 9 | Location: Texas | Registered: June 09, 2008Report 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     [CLOSED] Allow user to type in comma-separated list

Copyright © 1996-2020 Information Builders