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     [SOLVED] String replace and modify

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] String replace and modify
 Login/Join
 
Gold member
posted
Hello all,

i'm looking to take a string that looks like this:

'AAABBBCCCDDD' OR 'EEEFFFGGGHHH' OR 'IIIJJJ***LLL'

Where the 12 characters in the quotes is static (always 12) but the number of segments of 12 characters varies.
I'm trying to build a dynamic where statement that will use the first 3 characters of each segement then the next 3 then the next 3 etc.

So i'm looking to create 4 variables from the above input(*do not add the OR when a single segment is entered):
VARIABLE1 = 'AAA' OR 'EEE' OR 'III';
VARIABLE2 = 'BBB' OR 'FFF' OR 'JJJ';
VARIABLE3 = 'CCC' OR 'GGG' OR '***';
VARIABLE4 = 'DDD' OR 'HHH' OR 'LLL';

Use the created variables in a where statement:
WHERE FIELD1 EQ &VARIABLE1;
WHERE FIELD2 EQ &VARIABLE2;
WHERE FIELD3 EQ &VARIABLE3;
WHERE FIELD4 EQ &VARIABLE4;


Thanks ahead!!

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: 78 | Registered: December 29, 2009Report This Post
Virtuoso
posted Hide Post
You can use GETTOK to tokenize your string based on ' OR '.

By looping through each token, you can then get rid of the enclosing quotes (TRIM can help you there) and then use EDIT to get the first 3 characters.

Just use each resulting variable in a WHILE statement on each iteraction of the loop .. as FIELD1, FIELD2, etc. is also a "variable" field name just use a different set of &variable to make that up (as you're already looping it will be very simple to use something like:

-SET &I = 0;

TABLE FILE CAR
PRINT ....
BY ....

-REPEAT :DOSTUFF ...
-SET &I = &I + 1;
-SET &TOKEN.&I = EDIT/TRIM/GETTOK(....)
...
WHILE FIELD&I EQ &TOKEN.&I ...
...
-:DOSTUFF
ON TABLE ...
END


There is great documentation about each of those functions both with the product and at this forum so you'll likely find useful ideas.



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
Oh, I did not pay attention to your "groups of 3" in the initial post.

That means you'll need a slightly more elaborated version of the logic implementing a "group" variable that would increase for every 3 tokens. Your WHERE statement won't use each token directly but perhaps a "token group" variable ... sounds horrible to describe but the code should be relatively simple.



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
Expert
posted Hide Post
This will pull apart the string.

-SET &CNTR = 0 ;

-SET &STRING = '''AAABBBCCCDDD'' OR ''EEEFFFGGGHHH'' OR ''IIIJJJ***LLL''' ;

-SET &NO_QUOTE = STRIP(&STRING.LENGTH,&STRING,'''','A&STRING.LENGTH') ;

-SET &NO_OR    = STRREP (&NO_QUOTE.LENGTH,&NO_QUOTE,4,' OR ',1,',',&NO_QUOTE.LENGTH,'A&NO_QUOTE.LENGTH') ;

-TYPE &STRING
-TYPE &NO_QUOTE
-TYPE &NO_OR

-REPEAT GET_TOK FOR &CNTR FROM 1 TO 100 ;

-SET &ITEM = GETTOK(&NO_OR,&NO_OR.LENGTH,&CNTR,',',12,'A12') ;

-SET &CNTR = IF &ITEM EQ ' ' THEN 101 ELSE &CNTR ;
-IF &CNTR EQ 101 THEN GOTO GET_TOK ;

-SET &ITEM_1_.&CNTR = EDIT(&ITEM,'999$$$$$$$$$') ;
-SET &ITEM_2_.&CNTR = EDIT(&ITEM,'$$$999$$$$$$') ;
-SET &ITEM_3_.&CNTR = EDIT(&ITEM,'$$$$$$999$$$') ;
-SET &ITEM_4_.&CNTR = EDIT(&ITEM,'$$$$$$$$$999') ;

-GET_TOK

-? &ITEM


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

 
Posts: 6347 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report This Post
Gold member
posted Hide Post
Thanks Waz!

This may have solved another issue for me.
As for this one, one more step of creating the Where statements using those variables.

WHERE FIELD1 EQ '&ITEM_1_1' OR '&ITEM_1_2' OR '&ITEM_1_3';
WHERE FIELD2 EQ '&ITEM_2_1' OR '&ITEM_2_2' OR '&ITEM_2_3';
WHERE FIELD3 EQ '&ITEM_3_1' OR '&ITEM_3_2' OR '&ITEM_3_3';
WHERE FIELD4 EQ '&ITEM_4_1' OR '&ITEM_4_2' OR '&ITEM_4_3';


version 8202M
Reporting Server on
Windows Server using DB2 Connect to access data from iseries.
 
Posts: 78 | Registered: December 29, 2009Report This Post
Expert
posted Hide Post
quote:
one more step of creating the Where statements using those variables


The WHERE Clauses will be the easy bit.


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

 
Posts: 6347 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report This Post
Platinum Member
posted Hide Post
Here's another version of the example Waz started. This creates the where statements based on the input string.

-*
-SET &STRING = '''AAA***CCC***'' OR ''EEEFFF******'' OR ''IIIJJJ******''' ;
-*
-SET &NO_QUOTE = STRIP(&STRING.LENGTH,&STRING,'''','A&STRING.LENGTH') ;
-SET &NO_OR = STRREP (&NO_QUOTE.LENGTH,&NO_QUOTE,4,' OR ',1,',',&NO_QUOTE.LENGTH,'A&NO_QUOTE.LENGTH') ;
-*
-TYPE ----------------------------------------------------------------------------
-TYPE STRING ------- &STRING
-TYPE NO_QUOTE ----- &NO_QUOTE
-TYPE NO_OR -------- &NO_OR
-TYPE ----------------------------------------------------------------------------
-*
-SET &CNTR = 0;
-SET &P1_CNT = 0;
-SET &P1_TXT = 'ALL';
-SET &P2_CNT = 0;
-SET &P2_TXT = 'ALL';
-SET &P3_CNT = 0;
-SET &P3_TXT = 'ALL';
-SET &P4_CNT = 0;
-SET &P4_TXT = 'ALL';
-*
-TYPE ---------- LOOP BEGIN ----------
-:LOOP_BEG
-*
-SET &CNTR = &CNTR + 1;
-SET &ITEM = GETTOK(&NO_OR,&NO_OR.LENGTH,&CNTR,',',12,'A12');
-*
-IF &ITEM EQ ' ' THEN GOTO :LOOP_END;
-*
-SET &P1_VAL = EDIT(&ITEM,'999$$$$$$$$$');
-SET &P2_VAL = EDIT(&ITEM,'$$$999$$$$$$');
-SET &P3_VAL = EDIT(&ITEM,'$$$$$$999$$$');
-SET &P4_VAL = EDIT(&ITEM,'$$$$$$$$$999');
-*
-TYPE CNTR -------- &CNTR
-TYPE ITEM -------- &ITEM
-TYPE P1_VAL ------ &P1_VAL
-TYPE P2_VAL ------ &P2_VAL
-TYPE P3_VAL ------ &P3_VAL
-TYPE P4_VAL ------ &P4_VAL
-*
-SET &P1_CNT = IF &P1_VAL EQ '***' THEN &P1_CNT ELSE &P1_CNT + 1;
-SET &P1_TXT = IF &P1_VAL EQ '***' THEN &P1_TXT ELSE IF &P1_CNT EQ 1 THEN '''' | &P1_VAL | '''' ELSE &P1_TXT | ',' | '''' | &P1_VAL | '''';
-*
-SET &P2_CNT = IF &P2_VAL EQ '***' THEN &P2_CNT ELSE &P2_CNT + 1;
-SET &P2_TXT = IF &P2_VAL EQ '***' THEN &P2_TXT ELSE IF &P2_CNT EQ 1 THEN '''' | &P2_VAL | '''' ELSE &P2_TXT | ',' | '''' | &P2_VAL | '''';
-*
-SET &P3_CNT = IF &P3_VAL EQ '***' THEN &P3_CNT ELSE &P3_CNT + 1;
-SET &P3_TXT = IF &P3_VAL EQ '***' THEN &P3_TXT ELSE IF &P3_CNT EQ 1 THEN '''' | &P3_VAL | '''' ELSE &P3_TXT | ',' | '''' | &P3_VAL | '''';
-*
-SET &P4_CNT = IF &P4_VAL EQ '***' THEN &P4_CNT ELSE &P4_CNT + 1;
-SET &P4_TXT = IF &P4_VAL EQ '***' THEN &P4_TXT ELSE IF &P4_CNT EQ 1 THEN '''' | &P4_VAL | '''' ELSE &P4_TXT | ',' | '''' | &P4_VAL | '''';
-*
-GOTO :LOOP_BEG
-:LOOP_END
-TYPE ---------- LOOP END ------------
-*
-SET &P1_WHE = IF &P1_CNT EQ 0 THEN '' ELSE IF &P1_CNT EQ 1 THEN 'WHERE FIELD1 EQ ' | &P1_TXT ELSE 'WHERE FIELD1 IN (' | &P1_TXT | ')';
-SET &P2_WHE = IF &P2_CNT EQ 0 THEN '' ELSE IF &P2_CNT EQ 1 THEN 'WHERE FIELD2 EQ ' | &P2_TXT ELSE 'WHERE FIELD2 IN (' | &P2_TXT | ')';
-SET &P3_WHE = IF &P3_CNT EQ 0 THEN '' ELSE IF &P3_CNT EQ 1 THEN 'WHERE FIELD3 EQ ' | &P3_TXT ELSE 'WHERE FIELD3 IN (' | &P3_TXT | ')';
-SET &P4_WHE = IF &P4_CNT EQ 0 THEN '' ELSE IF &P4_CNT EQ 1 THEN 'WHERE FIELD4 EQ ' | &P4_TXT ELSE 'WHERE FIELD4 IN (' | &P4_TXT | ')';
-*
-TYPE ----------------------------------------------------------------------------
-TYPE P1_CNT ------ &P1_CNT
-TYPE P1_TXT ------ &P1_TXT
-TYPE P2_CNT ------ &P2_CNT
-TYPE P2_TXT ------ &P2_TXT
-TYPE P3_CNT ------ &P3_CNT
-TYPE P3_TXT ------ &P3_TXT
-TYPE P4_CNT ------ &P4_CNT
-TYPE P4_TXT ------ &P4_TXT
-TYPE ------------------------ GENERATED WHERE STATEMENTS ------------------------
-TYPE P1_WHE ------ &P1_WHE
-TYPE P2_WHE ------ &P2_WHE
-TYPE P3_WHE ------ &P3_WHE
-TYPE P4_WHE ------ &P4_WHE
-TYPE ----------------------------------------------------------------------------

Maybe this will help!
~Jim


WebFocus 8.201M, Windows, App Studio
 
Posts: 227 | Location: Lincoln Nebraska | Registered: August 12, 2008Report This Post
Gold member
posted Hide Post
Thanks All, I was able to accomplish what i needed!


version 8202M
Reporting Server on
Windows Server using DB2 Connect to access data from iseries.
 
Posts: 78 | Registered: December 29, 2009Report 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     [SOLVED] String replace and modify

Copyright © 1996-2020 Information Builders