I have a requirement to create one output file with a header, where inputs are coming from 3 separate files. These 3 input files have millions of record and one common key KKX-ID.
All the numeric fields are binary.
1)The three input file needs to be sorted in order of KKX-ID.
2)All KKX-ID from file1 should be written into output file.
3)Required field from file1 needs in the output file: KKX-ID(1:4), DKD(10:, DKD-DESC(23:30 ),E-BUD-IND(206:1)
4)Required field from file2 needs in the output file: KKX-ID(5:4), KKX-NUMBER(23:30 ),VDI-IND(18:1)
5)Required field from file3 needs in the output file: KKX-ID(1:4),KESK-AVT-AMNT(59:6)
6)The output fields should be in '|'delimited order(input fields are not in delimited order, just for display we have put it)
7)If the KKX-ID is not found in file2 or file3, numeric field needs to be written to output file with zeros & characters with spaces.
8)The output file needs to be sorted by DKD.
Please find below the sample input and output and also the ICETOOL code that i have written which is not working as desired.
Would be glad if you could help me solve this asap as it has really very urgent for me.
Joined: 10 May 2007 Posts: 2455 Location: Hampshire, UK
It may be urgent for you but it is not urgent for anyone else. Here we do things IF we want, WHEN we want, IF we want at all. For urgent you pay someone.
You have also been here long enough to know that you should use code tags to present stuf that needs a fixed pitch font. Nicely presented problems are more likely to be looked at.
Joined: 07 Dec 2007 Posts: 2205 Location: San Jose
frozenblood wrote:
I think we are deviating from the topic here, i am here for help and not to laugh at somebody or pass remarks i guess this forum is also for the same
1. If you are seeking help, please learn to be courteous and NOT rush people to help you out. You canNOT push your deadlines on to the people who help here voluntarily in their free time. Adding a smiley emoticon at the end of sentence doesn't help.
2. Please do NOT send me private messages with the same information for a topic you already posted. If you posted here it is more than enough.
3. Learn to use code tags which retains the spacing so that it is easy to read.
4. You description says "All the numeric fields are binary." but your file3 has KESK-AVT-AMNT where you show amounts with decimal point. Is the amount a packed decimal field?
5. You claim that the job you tried doesn't work. Ofcourse it doesn't work as you seem to invent your own syntax for JOINKEYS where you are passing the format.
If you really need please provide the following details.
a. What is the common key to match ? KKX-ID? If so what is the COBOL declaration of it. ex: KKX-ID PIC S9(09) COMP.
b. Show us the COBOL declaration for ALL the numeric fields.
c. Do you need the final output in readable format? Binary/packed decimal fields are not readable.
d. Do you really need the final output to be sorted on DKD? or is it KKX-ID?
I think we are deviating from the topic here, i am here for help and not to laugh at somebody or pass remarks i guess this forum is also for the same
1. If you are seeking help, please learn to be courteous and NOT rush people to help you out. You canNOT push your deadlines on to the people who help here voluntarily in their free time. Adding a smiley emoticon at the end of sentence doesn't help.
2. Please do NOT send me private messages with the same information for a topic you already posted. If you posted here it is more than enough.
3. Learn to use code tags which retains the spacing so that it is easy to read.
4. You description says "All the numeric fields are binary." but your file3 has KESK-AVT-AMNT where you show amounts with decimal point. Is the amount a packed decimal field?
5. You claim that the job you tried doesn't work. Ofcourse it doesn't work as you seem to invent your own syntax for JOINKEYS where you are passing the format.
If you really need please provide the following details.
a. What is the common key to match ? KKX-ID? If so what is the COBOL declaration of it. ex: KKX-ID PIC S9(09) COMP.
b. Show us the COBOL declaration for ALL the numeric fields.
c. Do you need the final output in readable format? Binary/packed decimal fields are not readable.
d. Do you really need the final output to be sorted on DKD? or is it KKX-ID?
I sincerely apologize for that.
Below are the answers:
a. What is the common key to match ? KKX-ID? If so what is the COBOL declaration of it. ex: KKX-ID PIC S9(09) COMP.
Answer:
Yes KKX-ID PIC S9(09) COMP.
b. Show us the COBOL declaration for ALL the numeric fields.
KKX-ID PIC S9(09) COMP.
DKD PIC S9(08)V COMP-3.
KESK-AVT-AMNT PIC S9(7)V9(3) COMP-3
KKX-NUMBER PIC S9(15)V COMP-3.
c. Do you need the final output in readable format? Binary/packed decimal fields are not readable.
Yes We need it in readable format.
d. Do you really need the final output to be sorted on DKD? or is it KKX-ID?
We need the output sorted on DKD.
We also need the header to be written in the output file:
KKX-ID|KKX-NUMBER |DKD |DKD-DESC |E-BUD-IND |VDI-IND|KESK-AVT-AMNT
Does any of the files have Duplicates on the matching key? If so do you want to create a Cartesian join records as output?
Yes file2 and file3 have duplicate records. We do not want cartesian join records.
We want to have the output for all the KKX-ID of file1. If KKX-ID not found in file2 or file3, the numeric fields will be written with Zeros and characters with spaces.
Joined: 07 Dec 2007 Posts: 2205 Location: San Jose
frozenblood wrote:
b. Show us the COBOL declaration for ALL the numeric fields.
KKX-ID PIC S9(09) COMP.
DKD PIC S9(08)V COMP-3.
KESK-AVT-AMNT PIC S9(7)V9(3) COMP-3
KKX-NUMBER PIC S9(15)V COMP-3.
Yes We need it in readable format.
We also need the header to be written in the output file:
KKX-ID|KKX-NUMBER |DKD |DKD-DESC |E-BUD-IND |VDI-IND|KESK-AVT-AMNT
Thanks for your time.
You aren't consistent with your requirements.
1. Where did this new KKX-NUMBER field come from? Which file has this field? what is the position of this new field?
2. When you say you want the output in readable format , what is the length of each field in the readable format? Do you need the leading zeros for Numeric fields? or should they be suppressed?
3. Do you need the header once at the top? or do you need it on every page?
b. Show us the COBOL declaration for ALL the numeric fields.
KKX-ID PIC S9(09) COMP.
DKD PIC S9(08)V COMP-3.
KESK-AVT-AMNT PIC S9(7)V9(3) COMP-3
KKX-NUMBER PIC S9(15)V COMP-3.
Yes We need it in readable format.
We also need the header to be written in the output file:
KKX-ID|KKX-NUMBER |DKD |DKD-DESC |E-BUD-IND |VDI-IND|KESK-AVT-AMNT
Thanks for your time.
You aren't consistent with your requirements.
1. Where did this new KKX-NUMBER field come from? Which file has this field? what is the position of this new field?
The KKX-NUMBER is present in the file2 as mentioned above.
File2: FB record length 250
2. When you say you want the output in readable format , what is the length of each field in the readable format? Do you need the leading zeros for Numeric fields? or should they be suppressed?
The desired output is:
KKX-ID PIC Z(10).
FILLER PIC X(01)
VALUE '|'.
KKX-NUMBER PIC Z(15).
FILLER PIC X(01)
VALUE '|'.
DKD PIC Z(08).
FILLER PIC X(01)
VALUE '|'.
DKD-DESC PIC X(30).
FILLER PIC X(01)
VALUE '|'.
E-BUD-IND PIC X(01).
FILLER PIC X(01)
VALUE '|'.
VDI-IND PIC X(01).
FILLER PIC X(01)
VALUE '|'.
KESK-AVT-AMNT PIC 9(07).9(03).
if you find something wrong please feel free to correct it.
3. Do you need the header once at the top? or do you need it on every page?
Joined: 09 Mar 2011 Posts: 7309 Location: Inside the Matrix
frozenblood,
You have made so many mistakes with seeking this solution, I don't really know where to begin.
Take note, this, and other forums, are not here for when you make a mess of your planning. The purpose is not to do your work for you. If you don't like being told that, you don't have to post questions in the future.
If you repeat these mistakes, including sending PMs or e-mails, multiple posting, failing to Code and Preview your postings, contesting when the errors of your ways are pointed out to you, etc, etc, you may receive a Warning.
Needless to say, if you fail to thank Kolusu for such impressive work, that will not go down so well with us either.
You claim urgency,
you make A DFSORT developer spend time on Your problem
and at the end You discover that You are using SYNCSORT...
pretty inconsiderate way of acting
Joined: 07 Dec 2007 Posts: 2205 Location: San Jose
frozenblood wrote:
Thanks a lot for your help kolusu.
Just realised that we use SYNCSORT here and not DFSORT so is there any alternative for JKFROM in syncsort as it doesnt support it?
Well after all the effort I put in to help, you just gave me a perfect start for my Friday.
I guess you haven't really paid attention to my signature. I'm a DFSORT developer. DFSORT and Syncsort are competitive products. I'm happy to answer questions on DFSORT and DFSORT's ICETOOL, but I don't answer questions on Syncsort.
