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,

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.

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.

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,

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

slfmr,

Here's an example to set you off. All it does is takes your example layout (as I have no data for it ) 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

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

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

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

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,

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!

You're most welcome. Glad to help.