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     CLOB formats and creating new lines.

Read-Only Read-Only Topic
Go
Search
Notify
Tools
CLOB formats and creating new lines.
 Login/Join
 
Platinum Member
posted
I have a CLOB formatted field that I am able to pull back by editing the Acutal and Usage values in the master file to A4096.

When it comes back it looks like this:

Person|FirstName|LastName|MiddleInitial~Location|City|State|Zip~Ins|Plans|Amounts~

I am trying to get this information to look like this:

Person|FirstName|LastName|MiddleInitial
Location|City|State|Zip
Ins|Plans|Amounts

I have used CTRAN to translate the ~ decimal value (127) to a new line (10) and even tried a carriage return (13), but this is not working?

Can I not edit a field that way? How can i parse this information so that it will appear on multiple lines/rows?

Can anyone help?

I was thinking I would need to loop through the string, but I am not sure how I would do that and still be able to get them on different lines.

This message has been edited. Last edited by: slfmr,


Dev, SIT, UAT, Production:7.6.6
Dev Sandbox:7.6.11

Dev Studio - 7.6.6
 
Posts: 178 | Registered: May 11, 2005Report This Post
Platinum Member
posted Hide Post
This could also be along the lines of dividing a long string into multiple fields.

I have seen the posts on POSIT and GETTOK and SUBSTR but I can't seem to figure it out.

Person|FirstName|LastName|MiddleInitial~Location~City|State|Zip~Ins|Plans|Amounts

My delimiter is ~:

DELIM/I5 = POSIT(CNTNR_CNTNT_STRNG_X, 4096, '~', 1, 'I5');
D_SUBSTR/A500 = SUBSTR(4096, CNTNR_CNTNT_STRNG_X, 1, DELIM, 500, D_SUBSTR);

This is pulling the first 10 characters and that's it.

How can I pull the first string part up until the ~ Then the next string part from the last ~ to the next ~.

Any help is much appreciated. Frowner


Dev, SIT, UAT, Production:7.6.6
Dev Sandbox:7.6.11

Dev Studio - 7.6.6
 
Posts: 178 | Registered: May 11, 2005Report This Post
Virtuoso
posted Hide Post
Here's how we do name rearrange, it is stored as something like CROSS, LEAH DAWN
TMP_NAME/A32= GETTOK ( STU_NAME , 32 , 2 , ',' , 32 , TMP_NAME );
SFX_NAME/A5= GETTOK ( STU_NAME , 32 , 3 , ',' , 5 , SFX_NAME );
FMN_NAME/A32= LJUST ( 32 , TMP_NAME , FMN_NAME );
FST_NAME/A32= GETTOK ( FMN_NAME , 32 , 1 , ' ' , 32 , FST_NAME );
MDL_NAME/A32= GETTOK ( FMN_NAME , 32 , 2 , ' ' , 32 , MDL_NAME );
LST_NAME/A32= GETTOK ( STU_NAME , 32 , 1 , ',' , 32 , LST_NAME );
EXPNDNME/A72= FMN_NAME || ( ' ' | LST_NAME | ' ' ) || SFX_NAME || '*' ;
CMPRSNME/A32= GETTOK ( EXPNDNME , 66 , 1 , '*' , 32 , CMPRSNME );
FUL_NAME/A32= LJUST ( 32 , CMPRSNME , FUL_NAME );

And an address line:

MAIL_CSZ/A50 = IF E09 EQ 'US' OR ' ' THEN
(E06 || (', ' | E07 | ' ' | ZIP))
ELSE (E06 || (' ' | E07));

E09 is country
E06 is City
E07 is state

The above on a mailing line from really old code to concatenate fields.

The name routine is our standard expand then I have a really fancy one that uses the lower case word function and overlay to get things like Macaffee to me MacAffee.


Leah
 
Posts: 1317 | Location: Council Bluffs, IA | Registered: May 24, 2004Report This Post
Platinum Member
posted Hide Post
Hey Leah,

Thanks for the response. Is there anyway you can show me what your original string looked like and what it looks like now? OR what the values in all of the defined fields are.

I am trying to picture what you have done.

Cause my string looks like this:

Person|FirstName|LastName|MiddleInitial~Location|City|State|Zip~Ins|Plans|Amounts

Is that what you did?

So I want:

Field1 = Person|FirstName|LastName|MiddleInitial
Field2 = Location|City|State|Zip
Field3 = Ins|Plans|Amounts

Then I would do some more edits etc... I don't know how long any string will be so it's hard to say what to extract. Also a person might not have any Ins so that row might not be there. All I know is that the ~ will separate a new line.

I will be looking into your post and trying it out.

Thank you!

This message has been edited. Last edited by: slfmr,


Dev, SIT, UAT, Production:7.6.6
Dev Sandbox:7.6.11

Dev Studio - 7.6.6
 
Posts: 178 | Registered: May 11, 2005Report This Post
Virtuoso
posted Hide Post
Here is an example, I usually put to a spreadsheet, so the '/' indicates the 'cell'
Of course I had to change real data.

DESIREABLE TRISTEN BACHMAN/2600 ANYSTREET RD/APT 2-B/ANY CITY, IA 51599

The following are fields in our data base

BACHMAN, DESIREABLE TRISTAN - name
2600 ANYSTREET ROAD - street line 1
APT 2-B - street line 2
ANY CITY - city
IA - state code
51599 - zip (we can store international and correctly formatted Canadian as well here.)

In this case country would be blank


Leah
 
Posts: 1317 | Location: Council Bluffs, IA | Registered: May 24, 2004Report This Post
Expert
posted Hide Post
slfmr,

Here's an example to set you off. All it does is takes your example layout (as I have no data for it Smiler) and creates a temporary master file with three defines to identify the required fields within your CLOB. You could just place these in your master and be able to access them from any report without having to bother with copying the define each time or using an include etc.

APP FI CLOBMAS DISK CLOBTEST.MAS
-RUN
-WRITE CLOBMAS
-WRITE CLOBMAS FILE=CLOBTEST,SUFFIX=FIX
-WRITE CLOBMAS SEGNAME=SEG1
-WRITE CLOBMAS FIELD=CLOB_FIELD,,A4096,A4096,$
-WRITE CLOBMAS DEFINE FLD1/A2000 = GETTOK(CLOB_FIELD, 4096, 1, '~', 2000, 'A2000');
-WRITE CLOBMAS DEFINE FLD2/A2000 = GETTOK(CLOB_FIELD, 4096, 2, '~', 2000, 'A2000');
-WRITE CLOBMAS DEFINE FLD3/A2000 = GETTOK(CLOB_FIELD, 4096, 3, '~', 2000, 'A2000');
-RUN
FILEDEF CLOBTEST DISK ./CLOBTEST.TXT (LRECL 4096 RECFM F
-RUN

-WRITE CLOBTEST Person|FirstName|LastName|MiddleInitial~Location|City|State|Zip~Ins|Plans|Amounts~
-RUN

TABLE FILE CLOBTEST
PRINT FLD1 FLD2 FLD3
END


Good luck

T



In FOCUS
since 1986
WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2  
WebFOCUS App Studio 8.2.06 standalone on Windows 10 
 
Posts: 5694 | Location: United Kingdom | Registered: April 08, 2004Report This Post
Platinum Member
posted Hide Post
Thanks Leah and Tony... I am trying out both.

Tony,

I understand that a temporary master file is created, but I have a few questions:

"You could just place these in your master..."

Would I place the new define fields in the master file? Also I thought the master file was temporary so I'm not sure where to place what. I guess I don't know what "these" refers to Smiler. "These" as in those lines of code placed in the original master file for the table that contains the CLOB or "these" as in just the defined fields...

Also what is this? (LRECL 4096 RECFM F
This comes after the FILEDEF... I have used FILEDEF but the only thing I put after that is an APPEND.

And your CLOBTEST name does that refer to my master file with the CLOB format or is that just a new name we are creating?

Thank you sooo much for responding. There aren't many posts on CLOB's so I was afraid I was going to totally be in the dark. Thank you for all your clarifications!

This message has been edited. Last edited by: slfmr,


Dev, SIT, UAT, Production:7.6.6
Dev Sandbox:7.6.11

Dev Studio - 7.6.6
 
Posts: 178 | Registered: May 11, 2005Report This Post
Expert
posted Hide Post
slfmr,

By these I mean the actual DEFINE lines, just append them to the end of your master file description to be able to have instant access to them in any fex that accesses the .mas -
DEFINE FLD1/A2000 = GETTOK(CLOB_FIELD, 4096, 1, '~', 2000, 'A2000');
DEFINE FLD2/A2000 = GETTOK(CLOB_FIELD, 4096, 2, '~', 2000, 'A2000');
DEFINE FLD3/A2000 = GETTOK(CLOB_FIELD, 4096, 3, '~', 2000, 'A2000');


The only reason that my example showed a -WRITE using them is that I was trying to supply a possible solution without leaving around residual code that I no longer require! Also to give an entirely funtional example without any changes. As I do not have the .mas that you use I tend to use temporary .mas files to build the temporary files required to mimic your requirement, in this case I called it CLOBTEST for want of a better name! That is why we will often ask for an example using one of the sample files from IB (CAR, GGSALES etc.) so that we know we are using the same data as you.

I hope you find your solution.

T



In FOCUS
since 1986
WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2  
WebFOCUS App Studio 8.2.06 standalone on Windows 10 
 
Posts: 5694 | Location: United Kingdom | Registered: April 08, 2004Report This Post
Expert
posted Hide Post
If you want more specific assistance then PM me and I'll reply when I can. I have added you to my buddies so that you can use PM to me. Sorry about that, but I had to restrict my PM as I was getting some directly from members instead of them raising a new topic!

T



In FOCUS
since 1986
WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2  
WebFOCUS App Studio 8.2.06 standalone on Windows 10 
 
Posts: 5694 | Location: United Kingdom | Registered: April 08, 2004Report This Post
Platinum Member
posted Hide Post
Great thanks I will take you up on that.. I am currently editing my Master file as well to see what I have and find.

I also had not seen your double post. Your explanation helped me, thank you for your time.

I will be in touch through PM if I have any more questions and if at that point I do have questions, I will do my best to have an example for you.

Thank you!

This message has been edited. Last edited by: slfmr,


Dev, SIT, UAT, Production:7.6.6
Dev Sandbox:7.6.11

Dev Studio - 7.6.6
 
Posts: 178 | Registered: May 11, 2005Report This Post
Platinum Member
posted Hide Post
Hi Tony and Leah, thanks so much for all your help. I went ahead and took the route suggested by both. I used GETTOK suggested by both parties and went ahead and also placed my defines in my master file, per Tony's suggestion.

This has been an extremly helpful post and I thank you both. Everything is working as I need it.

Again, thank you!


Dev, SIT, UAT, Production:7.6.6
Dev Sandbox:7.6.11

Dev Studio - 7.6.6
 
Posts: 178 | Registered: May 11, 2005Report This Post
Virtuoso
posted Hide Post
You're most welcome. Glad to help.


Leah
 
Posts: 1317 | Location: Council Bluffs, IA | Registered: May 24, 2004Report 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     CLOB formats and creating new lines.

Copyright © 1996-2020 Information Builders