STRING functionality to fetch the few columns of a record

gomu_mm · New User Joined: 05 Dec 2006 Posts: 19 Location: Chennai

Hi,

I have to fetch the few columns of a record from a table and write it to a file seperating each field by commas.
For e.g. I have a table as follows,

EmpNo ACC Role Amount Description
------------------------------------------------------------------
0123 00234 SE 00100.00 Software
0456 00456 WC 00200.00 worker

I want to write the data to a file as follows,

123,234,SE,100.00,Software
456,456,WC,200.00,worker

Some fields are packed decimals so I have moved those fields to numeric and then to alphanumeric and I have used STRING function to create these records, but I'm not able to remove the zeroes before the fields.

Please let me know is there any way that I could trim the leading zeroes after using STRING function and before writing to the file?

Thanks,
Gomathi.

dick scherrer · Posted: Wed Oct 01, 2008 8:11 pm

Hello,

Ajay Baghel · Posted: Wed Oct 01, 2008 8:25 pm

dbzTHEdinosauer · Posted: Wed Oct 01, 2008 9:21 pm

gomu_mm · New User Joined: 05 Dec 2006 Posts: 19 Location: Chennai

Hi,

thanks for the suggestions.

as for as i know, Z is used only for displaying purpose and it does not remove the leading zeroes. in this case i wil not display the values straight away but move to alphanumeric to perform STRING function.

i just thout of picking the values as soon as i pick it from table and trim them before moving to the alphanumeric field.

thanks,
gomathi.

dick scherrer · Posted: Fri Oct 03, 2008 9:36 am

Hello,

gomu_mm · New User Joined: 05 Dec 2006 Posts: 19 Location: Chennai

Hi,

I was able to remove the leading zeroes but not the spaces,which was not accepatable in the CSV file. Hence I used the following technique,

V1 - PIC 9(5) ------> Actual variable
V2 - PIC ++++9
V3 - PIC x(5)
V4 - PIC x(5)
V5 - PIC x(5)

MOVE V1 TO V2 -----> 00200 TO ( +200)
MOVE V2 TO V3 -----> ( +200) TO ( +200)
STRING V3 DELIMITED BY '+'
INTO V4, V5 -----> ( +200) TO ( +), (200)

Then I used the variable V5 in my STRING function to create the file. This worked fine.

Arun Raj · Posted: Wed Dec 03, 2008 10:08 am

gomu_mm,

Your initial variable was unsigned. Then what's the point in inserting a + sign while editing and then removing the + sign later.

dick scherrer · Posted: Wed Dec 03, 2008 10:28 am

Hello gomathi,

Good to hear you have it working the way you need

As Arun mentioned, unstring seems more likely - i suspect a typo.

Thanks for letting us know,

d

gomu_mm · New User Joined: 05 Dec 2006 Posts: 19 Location: Chennai

Hi,

The only point in inserting the + sign is to use the STRING function to seperate the spaces before the actual value. I have only come across declaration +++9 and ---9 and hence used one of them.

When we move a value 00200 variable to ++++9 variable, there would be one space and +200. Then we use STRING function to split the space and + (delimiting by +) into one variable and the actual value into another variable.

When I use the actual value in the CSV file convertion, I give delimited by space.

Thanks,
Gomathi.

gomu_mm · New User Joined: 05 Dec 2006 Posts: 19 Location: Chennai

Hi dick,

Yes its s typo. It should be UNSTRING.

Arun Raj · Posted: Wed Dec 03, 2008 12:38 pm

gomu_mm,

I still believe you could have done the same without an UNSTRING.

gomu_mm · New User Joined: 05 Dec 2006 Posts: 19 Location: Chennai

Yes Arun. I accept Z replaces the zeroes with spaces. But that was not the task. I should create a CSV file with the data. when I attempted to create a CSV file, the value was prefixed with spaces,

0123 00234 SE 00100.00 Software

would look like (prefixed with spaces)

123, 234,SE, 100.00,Software

So I had to edit the number(remove the spaces) before preparing the record as comma seperated.

Thanks,
Gomathi.

Arun Raj · Posted: Wed Dec 03, 2008 2:43 pm

gomu_mm,

I am not saying that moving to edited field will solve all your problems. What I tried to point out was that, moving to ++++9 , doing an UNSTRING and then finally getting the output(with leading spaces) in X(5) could be just achieved by moving into a Z(5). Why unnecessary overhead of doing an UNSTRING?

gomu_mm · New User Joined: 05 Dec 2006 Posts: 19 Location: Chennai

Ok but how will you remove the space in STRING function?
If it is at last then we could give DELIMITED BY SPACES.

Arun Raj · Posted: Wed Dec 03, 2008 3:33 pm

gomu_mm,

How about counting the leading ZEROS using an INSPECT and then using reference modification to do a MOVE from the first non-zero digit?

gomu_mm · New User Joined: 05 Dec 2006 Posts: 19 Location: Chennai

I thought about this before but it was becoming very complex and hence found this way the easier one.