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,

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.

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>,

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

Thank you both for your help.

Thank you all for your help!

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

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

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.

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

quote:

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

Not without seeing some code.

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

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.

In addition, please read this for an indication as to how code should be posted.

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