Input String: '1111+2222+33333+4444444+5555555' Desired Output: '1111' OR '2222' OR '33333' OR '4444444' OR '5555555'
Read on if you need more info:
I currently have a text field that I ask the user to enter a + sign between values to enter multiple values and then use a GETTOK to separate the values. The issue is this generates a lot of code and a limit on the number of values unless we add more code to the procedure.
I attempted to use a STRREP command to replace the + with an OR to build a dynamic WHERE statement but the system did not like the + as a replace value.
I'm probably missing some quotes, help please.
Current Method: -*--------------------------------------------------------------- -*ADD ABILITY TO ENTER UP TO 10 VENDOR NUMBERS -DEFAULT &VNDN = ''; -SET &DL = '+'; -SET &VNDN = &VNDN | &DL ; -SET &VNDN1 = GETTOK(&VNDN,&VNDN.LENGTH,1,&DL,10,'A10'); -SET &VNDN2 = GETTOK(&VNDN,&VNDN.LENGTH,2,&DL,10,'A10'); -SET &VNDN3 = GETTOK(&VNDN,&VNDN.LENGTH,3,&DL,10,'A10'); -SET &VNDN4 = GETTOK(&VNDN,&VNDN.LENGTH,4,&DL,10,'A10'); -SET &VNDN5 = GETTOK(&VNDN,&VNDN.LENGTH,5,&DL,10,'A10'); -SET &VNDN6 = GETTOK(&VNDN,&VNDN.LENGTH,6,&DL,10,'A10'); -SET &VNDN7 = GETTOK(&VNDN,&VNDN.LENGTH,7,&DL,10,'A10'); -SET &VNDN8 = GETTOK(&VNDN,&VNDN.LENGTH,8,&DL,10,'A10'); -SET &VNDN9 = GETTOK(&VNDN,&VNDN.LENGTH,9,&DL,10,'A10'); -SET &VNDN10 = GETTOK(&VNDN,&VNDN.LENGTH,10,&DL,10,'A10'); -SET &VNDN1 = IF &VNDN1 EQ '' THEN 'FOC_NONE' ELSE RJUST(10,&VNDN1 | ' ','A10'); -SET &VNDN2 = IF &VNDN2 EQ '' THEN 'XXXXXXXXXX' ELSE RJUST(10,&VNDN2 | ' ','A10'); -SET &VNDN3 = IF &VNDN3 EQ '' THEN 'XXXXXXXXXX' ELSE RJUST(10,&VNDN3 | ' ','A10'); -SET &VNDN4 = IF &VNDN4 EQ '' THEN 'XXXXXXXXXX' ELSE RJUST(10,&VNDN4 | ' ','A10'); -SET &VNDN5 = IF &VNDN5 EQ '' THEN 'XXXXXXXXXX' ELSE RJUST(10,&VNDN5 | ' ','A10'); -SET &VNDN6 = IF &VNDN6 EQ '' THEN 'XXXXXXXXXX' ELSE RJUST(10,&VNDN6 | ' ','A10'); -SET &VNDN7 = IF &VNDN7 EQ '' THEN 'XXXXXXXXXX' ELSE RJUST(10,&VNDN7 | ' ','A10'); -SET &VNDN8 = IF &VNDN8 EQ '' THEN 'XXXXXXXXXX' ELSE RJUST(10,&VNDN8 | ' ','A10'); -SET &VNDN9 = IF &VNDN9 EQ '' THEN 'XXXXXXXXXX' ELSE RJUST(10,&VNDN9 | ' ','A10'); -SET &VNDN10 = IF &VNDN10 EQ '' THEN 'XXXXXXXXXX' ELSE RJUST(10,&VNDN10 | ' ','A10');
-*--------------------------------------------------------------- WHERE VNDN EQ '&VNDN1' OR '&VNDN2' OR '&VNDN3' OR '&VNDN4' OR '&VNDN5' OR '&VNDN6' OR '&VNDN7' OR '&VNDN8' OR '&VNDN9' OR '&VNDN10';This message has been edited. Last edited by: Kerry,
version 8202M Reporting Server on Windows Server using DB2 Connect to access data from iseries.
December 06, 2010, 05:30 PM
Waz
What was your original STRREP code ?
Waz...
Prod:
WebFOCUS 7.6.10/8.1.04
Upgrade:
WebFOCUS 8.2.07
OS:
Linux
Outputs:
HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!
December 06, 2010, 05:54 PM
Joe Beydoun
I scrapped all that code to publish this report since it was needed. I'll try to regenerate it with the error message and post.
version 8202M Reporting Server on Windows Server using DB2 Connect to access data from iseries.
December 06, 2010, 06:06 PM
gweller
Maybe I am missing something, but could you not just ask your user to change the delimiter to a comma or | or anything?
WebFOCUS 8201M/Windows Platform
December 06, 2010, 06:24 PM
Joe Beydoun
The + is on the number keypad and helps when inputting a bunch of numbers, plus we're on an AS400 so they are very used to the field exit button being the +
version 8202M Reporting Server on Windows Server using DB2 Connect to access data from iseries.
-DEFAULT &SUMLIST = '100100+100200+100300+100400' -SET &SUMOBJ1 = STRREP(27, 100100+100200+100300+100400, 1, '+', 4, ' OR ', 27, A27); 0 ERROR AT OR NEAR LINE 5 IN PROCEDURE mrheaderFOCEXEC * (FOC36355) INVALID TYPE OF ARGUMENT #4 FOR USER FUNCTION STRREP
version 8202M Reporting Server on Windows Server using DB2 Connect to access data from iseries.
-SET &SUMOBJ1 = '('''|| STRREP(&SUMLIST.LENGTH,&SUMLIST,1,HEXBYT(43,'A1'),3,''',''',1024,'A1024')|| ''')';
The WHERE becomes
WHERE VNDN IN &SUMOBJ1.EVAL
December 07, 2010, 09:35 AM
dbeagan
JG, I like the idea of keeping it simple with a 1-liner. But it gives an error for single NUMERIC values:
-DEFAULT &SUMLIST='100100';
You can get around this by branching around the STRREP statement or inserting characters into &SUMLIST to make DM treat it as non-numeric.
Also note, in situations where the string you are replacing (argument #4) is longer than the original string (argument #2), the result is blank, not the original string as you might expect. This could happen if you are attempting to convert an OR delimited list into a comma delimited list:
-DEFAULT &SUMLIST = '1A';
-SET &SUMOBJ1 = STRREP(&SUMLIST.LENGTH,&SUMLIST,4,' OR ',1,',',1024,'A1024');
-* &SUMOBJ1 is set to blank
WebFOCUS 8.2.06
December 07, 2010, 09:55 AM
Joe Beydoun
Thanks everyone for some great code and it works real nice, so i would like to push the functionality a bit more on some other fields.
I would like to use the same routine, but some fields in our database are right justified but we don't require the user to right justify their input, with the GETTOK i can do this by using the RJUST after breaking the test values, other than creating a dynamic GETTOK command, i don't know how to accomplish this with a STRREP.
User Input:
'100100+500+700+8000000'
Desired Output:
' 100100' OR ' 500' OR ' 700' OR ' 8000000'
P.S. Mr. Beagan, you need to talk to someone to change your status to Virtuoso, that Silver Member is very deceiving.
This message has been edited. Last edited by: Joe Beydoun,
version 8202M Reporting Server on Windows Server using DB2 Connect to access data from iseries.
December 07, 2010, 10:07 AM
GamP
Same here. I Don't think there is any procedure or function that you can use to do this. So it's entirely up to your creativity to do this with dynamic gettok commands - as you call it. STRREP certainly can't do it.
GamP
- Using AS 8.2.01 on Windows 10 - IE11.
in Focus since 1988
December 07, 2010, 10:10 AM
GamP
quote:
Desired Output: ***The preceding spaces don't display so i replaced them with b. 'bbbb100100' OR 'bbbbbbb500' OR 'bbbbbbb700' OR 'bbb8000000'
if you use the red < / > button when entering data it will display 'as-is':
' 100100' OR ' 500' OR ' 700' OR ' 8000000'
GamP
- Using AS 8.2.01 on Windows 10 - IE11.
in Focus since 1988
December 07, 2010, 10:18 AM
Joe Beydoun
Thanks for the red button advice Gamp, there is much to learn on this forum.
version 8202M Reporting Server on Windows Server using DB2 Connect to access data from iseries.
December 07, 2010, 10:32 AM
j.gross
Instead of resorting to BYTVAL(), I'd use
EDIT('+','9')
to force the + to be treated as a character-string.
- Jack Gross WF through 8.1.05
December 07, 2010, 12:02 PM
dbeagan
j.gross, I like it. The EDIT makes for code that is a little more readable.
Joe, I confronted a situation with the right justified values a few years ago -- let me see if I can dig up the code tonight.
WebFOCUS 8.2.06
December 07, 2010, 01:15 PM
Joe Beydoun
Thanks David, i'll be anxiously awaiting.
version 8202M Reporting Server on Windows Server using DB2 Connect to access data from iseries.
December 07, 2010, 03:52 PM
j.gross
quote:
The EDIT makes for code that is a little more readable.
...and portable (as between ASCII and EBCDIC)
December 07, 2010, 05:12 PM
Waz
Although its long winded and horrible, here is a way to do the right justified test.
-SET &ECHO=ALL;
-DEFAULT &SUMLIST = '100100+100200+100300+100400' ;
-SET &PLUSCNT = STRIP(&SUMLIST.LENGTH,&SUMLIST,'+','A&SUMLIST.LENGTH') ;
-SET &PLUSCNT = TRUNCATE(&PLUSCNT) ;
-SET &CHARS = &SUMLIST.LENGTH - &PLUSCNT.LENGTH ;
-SET &NEWLEN = &PLUSCNT.LENGTH + (&CHARS * 2) ;
-SET &SUMLINES= STRREP(&SUMLIST.LENGTH,&SUMLIST,1,EDIT('+','9'),2,HEXBYT(13,'A1')|HEXBYT(10,'A1'),&NEWLEN,'A&NEWLEN.EVAL');
FILEDEF PARMS DISK parms.ftm
-RUN
-WRITE PARMS &SUMLINES
-* Write out a master to read the BSB_ACC list
EX -LINES 4 EDAPUT MASTER,PARMS,CV,FILE
FILENAME=PARMS, SUFFIX=FIX,$
SEGNAME=PARMS, $
FIELD=VALUE ,ALIAS= ,A10,A10,$
-RUN
DEFINE FILE PARMS
CNTR/I9 = LAST CNTR + 1 ;
VAL_VAR/A200V = IF CNTR EQ 1
THEN '''' | RJUST(10,VALUE,'A10') | ''''
ELSE LAST VAL_VAR || (' OR ''' | RJUST(10,VALUE,'A10') | '''') ;
VALLIST/A200 = VAL_VAR ;
END
TABLE FILE PARMS
SUM COMPUTE Where/A15 = 'WHERE FIELD EQ ' ;
LST.VALLIST
ON TABLE HOLD AS FEX_CODE
END
-RUN
-INCLUDE FEX_CODE
Waz...
Prod:
WebFOCUS 7.6.10/8.1.04
Upgrade:
WebFOCUS 8.2.07
OS:
Linux
Outputs:
HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!
December 08, 2010, 03:22 AM
<JG>
quote:
I like the idea of keeping it simple with a 1-liner. But it gives an error for single NUMERIC values:
That is because of a bug in the subroutine but is very easily avoided
Have the user enter the desired list of vendors in text-field in an HTML page. Have Jscript do the replace and padding and put the output in another text-field (hidden). And link -that- hidden field to the parameter?
just a thought...
_____________________ WF: 8.0.0.9 > going 8.2.0.5
December 09, 2010, 11:44 AM
j.gross
I prefer to avoid extensive Dialog Manager manipulation, if the goal can be reached with straight Focus code.
In the fex below, I use Modify to load a Focus file with the codes; the Master includes a define for the right-justified code. The latter values, SAVE'd, can be used directly in the screening phrase, as illustrated in the final TABLE.
-DEFAULT &CODES = '1+12+1234+12345+123456+1234567+12345678+123456789+1234567890';
EX -LINES 5 EDAPUT MASTER,PARMS,CV,FILE
FILENAME=PARMS,SUFFIX=FOC,$
SEGNAME=ONLY,SEGTYPE=S1,$
FIELD=CODE,,A10,$
DEFINE RJCODE/A10=RJUST(10,CODE,'A10');,$
CREATE FILE PARMS
MODIFY FILE PARMS
COMPUTE TXT/A100=; I/I3=;
FREEFORM TXT
REPEAT 25 TIMES NOHOLD
COMPUTE I=I+1; CODE=GETTOK(TXT,100,I,EDIT('+','9'),10,CODE);
IF CODE EQ '' GOTO ENDREPEAT;
MATCH CODE
ON NOMATCH INCLUDE
ON MATCH/NOMATCH GOTO ENDREPEAT
ENDREPEAT
DATA
&CODES
END
TABLE FILE PARMS
PRINT RJCODE AND SAVE
END
TABLE FILE PARMS
LIST CODE RJCODE
WHERE RJCODE IN FILE 'SAVE';
ON TABLE PCHOLD FORMAT PDF
END