Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] STRREP issue
Go
New
Search
Notify
Tools
Reply
  
[CLOSED] STRREP issue
 Login/Join
 
Gold member
posted
Issue Summary:

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.
 
Posts: 77 | Registered: December 29, 2009Reply With QuoteReport This Post
Expert
posted Hide Post
What was your original STRREP code ?


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.05OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Know The Code

 
Posts: 6104 | Location: Land of the Darug people, Terra Australis Incognita | Registered: October 31, 2006Reply With QuoteReport This Post
Gold member
posted Hide Post
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.
 
Posts: 77 | Registered: December 29, 2009Reply With QuoteReport This Post
Platinum Member
posted Hide Post
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
 
Posts: 109 | Registered: October 31, 2006Reply With QuoteReport This Post
Gold member
posted Hide Post
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.
 
Posts: 77 | Registered: December 29, 2009Reply With QuoteReport This Post
Gold member
posted Hide Post
CODE:
-SET &ECHO = ALL;
-DEFAULT &SUMLIST = '100100+100200+100300+100400'
-SET &SUMOBJ1 = STRREP(&SUMLIST.LENGTH, &SUMLIST, 1, '+', 4, ' OR ', &SUMLIST.LENGTH, A&SUMLIST.LENGTH);


OUTPUT:

-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.
 
Posts: 77 | Registered: December 29, 2009Reply With QuoteReport This Post
Expert
posted Hide Post
The + char does cause a problem.

Try this.
-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 * 4) ;
-SET &SUMOBJ1 = STRREP(&SUMLIST.LENGTH,&SUMLIST,1,HEXBYT(43,'A1'),4,' OR ',&NEWLEN,'A&NEWLEN.EVAL');
 
-TYPE &SUMOBJ1


The HEXBYT converts a numeric to a char.


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.05OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Know The Code

 
Posts: 6104 | Location: Land of the Darug people, Terra Australis Incognita | Registered: October 31, 2006Reply With QuoteReport This Post
Platinum Member
posted Hide Post
Building on Waz's nice example, this handles the case where user enters a single value. Also puts ticks around each value.

-SET &ECHO = 'ALL';
-DEFAULT &SUMLIST = '100100+222222+333333';
-SET &SUMLIST = '''' | TRUNCATE(&SUMLIST) | '''';
-SET &PLUSCNT = STRIP(&SUMLIST.LENGTH,&SUMLIST,'+','A&SUMLIST.LENGTH') ;
-SET &PLUSCNT = TRUNCATE(&PLUSCNT) ;
-SET &CHARS   = &SUMLIST.LENGTH - &PLUSCNT.LENGTH ;
-SET &NEWLEN  = &PLUSCNT.LENGTH + (&CHARS * 6) ;
-SET &SUMOBJ1 = STRREP(&SUMLIST.LENGTH,&SUMLIST,1,HEXBYT(43,'A1'),6,''' OR ''',&NEWLEN,'A&NEWLEN.EVAL');
-TYPE &SUMOBJ1


WF 8.2.06
Win10 / IE11
AHTML EXL2K PDF
 
Posts: 136 | Registered: October 19, 2010Reply With QuoteReport This Post
<JG>
posted
quote:
WHERE VNDN EQ


Alternativly do it as a 1 liner and use WHERE IN


 -SET &SUMOBJ1 = '('''|| STRREP(&SUMLIST.LENGTH,&SUMLIST,1,HEXBYT(43,'A1'),3,''',''',1024,'A1024')|| ''')';

The WHERE becomes

WHERE VNDN IN &SUMOBJ1.EVAL 
 
Reply With QuoteReport This Post
Platinum Member
posted Hide Post
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


WF 8.2.06
Win10 / IE11
AHTML EXL2K PDF
 
Posts: 136 | Registered: October 19, 2010Reply With QuoteReport This Post
Gold member
posted Hide Post
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.
 
Posts: 77 | Registered: December 29, 2009Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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 7 - IE11.
in Focus since 1988
 
Posts: 1960 | Location: Netherlands | Registered: September 25, 2007Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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 7 - IE11.
in Focus since 1988
 
Posts: 1960 | Location: Netherlands | Registered: September 25, 2007Reply With QuoteReport This Post
Gold member
posted Hide Post
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.
 
Posts: 77 | Registered: December 29, 2009Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Reply With QuoteReport This Post
Platinum Member
posted Hide Post
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.


WF 8.2.06
Win10 / IE11
AHTML EXL2K PDF
 
Posts: 136 | Registered: October 19, 2010Reply With QuoteReport This Post
Gold member
posted Hide Post
Thanks David, i'll be anxiously awaiting.


version 8202M
Reporting Server on
Windows Server using DB2 Connect to access data from iseries.
 
Posts: 77 | Registered: December 29, 2009Reply With QuoteReport This Post
Virtuoso
posted Hide Post
quote:
The EDIT makes for code that is a little more readable.


...and portable (as between ASCII and EBCDIC)
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Reply With QuoteReport This Post
Expert
posted Hide Post
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.04Upgrade:WebFOCUS 8.2.05OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Know The Code

 
Posts: 6104 | Location: Land of the Darug people, Terra Australis Incognita | Registered: October 31, 2006Reply With QuoteReport This Post
<JG>
posted
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

 
-DEFAULT &SUMLIST = '1';
-SET &SUMOBJ1 = '('''|| STRREP(&SUMLIST.LENGTH,'&SUMLIST.EVAL X',1,HEXBYT(43,'A1'),3,''',''',1024,'A1024')|| ''')';
-TYPE &SUMOBJ1
 
 
Reply With QuoteReport This Post
Virtuoso
posted Hide Post
Regarding GETTOK and the right-justified values, this might work:

-SET &USER_INPUT = '100100+500+700+8000000';
-*
-REPEAT :ENDREPEAT1 FOR &I FROM 1 TO 50
-SET &TOKEN = GETTOK(&USER_INPUT,&USER_INPUT.LENGTH,&I,'+',10,'A10');
-IF (&TOKEN EQ '') GOTO :EXITREPEAT1 ; 
-SET &INPUT.&I = RJUST(10,&TOKEN,'A10');
-:ENDREPEAT1
-:EXITREPEAT1
-*
-SET &COUNT_INPUTS = &I - 1 ;
-SET &J = 1 ;
-SET &WHEREIN = '''' | &INPUT.&J | '''';
-*
-REPEAT :ENDREPEAT2 FOR &J FROM 2 TO &COUNT_INPUTS
-SET &WHEREIN = &WHEREIN | ',''' | &INPUT.&J | '''';
-:ENDREPEAT2

Since I used a comma instead of ' OR ', your WHERE clause becomes:

WHERE <fieldname> IN (&WHEREIN);


WebFOCUS 7.7.05
 
Posts: 1213 | Location: Seattle, Washington - USA | Registered: October 22, 2007Reply With QuoteReport This Post
<JG>
posted
A variation

 
-DEFAULT &SUMLIST = '1+12+1234+12345+123456+1234567+12345678+123456789+1234567890';
-SET &WOPLUS= STRIP(&SUMLIST.LENGTH, '&SUMLIST.EVAL', '+', 'A&SUMLIST.LENGTH');
-SET &NEWVAR= TRUNCATE(&WOPLUS.EVAL);
-SET &NOPLUS= &SUMLIST.LENGTH - &NEWVAR.LENGTH;
-SET &INSTRNG = RJUST(10,GETTOK('&SUMLIST.EVAL', &SUMLIST.LENGTH, 1, '+', 10, 'A10'),'A10');
-SET &CNTR=1;
-REPEAT ENDREPEAT &NOPLUS TIMES
-SET &CNTR=&CNTR+1;
-SET &ELEMENT = GETTOK('&SUMLIST.EVAL', &SUMLIST.LENGTH, &CNTR, '+', 10, 'A10');
-SET &INSTRNG= '&INSTRNG.EVAL' | ',' | RJUST(10,'&ELEMENT.EVAL','A10');
-ENDREPEAT
-SET &SUMOBJ1 = '('''|| STRREP(&INSTRNG.LENGTH,'&INSTRNG.EVAL X',1,',',3,''',''',1024,'A1024')|| ''')'; 
 
Reply With QuoteReport This Post
Master
posted Hide Post
All,

since it begining to be huge...

Is this an option:

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
 
Posts: 669 | Location: Veghel, The Netherlands | Registered: February 16, 2010Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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


- Jack Gross
WF through 8.1.05
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] STRREP issue

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.