create an extract
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 16
- Joined: Fri Apr 28, 2006 1:01 pm
create an extract
Hi,
I need a help in creating extract from the database. the problem is for each value i need to create trailer and header in the same file. for eg :
EMP EMPNAME SAL
111 PAT 100
111 ABC 100
222 JON 200
222 JON1 200
333 JJJJ 300
333 JJJJ 300
I need to create an extract with the following from the above table.
HDR 111
DTL 111 PAT 100
TRL 111 2 200 (HERE 2 is record count (111,111) and (200 is sal sum).
HDR 222
DTL 222 PAT 200
TRL 222 2 400
Please let me know how to create hdr and trailer for ever empno change in a loop. Appreciate any help.
Thanks
Patrick
I need a help in creating extract from the database. the problem is for each value i need to create trailer and header in the same file. for eg :
EMP EMPNAME SAL
111 PAT 100
111 ABC 100
222 JON 200
222 JON1 200
333 JJJJ 300
333 JJJJ 300
I need to create an extract with the following from the above table.
HDR 111
DTL 111 PAT 100
TRL 111 2 200 (HERE 2 is record count (111,111) and (200 is sal sum).
HDR 222
DTL 222 PAT 200
TRL 222 2 400
Please let me know how to create hdr and trailer for ever empno change in a loop. Appreciate any help.
Thanks
Patrick
Re: create an extract
Do you need help with the sql for extraction
or with a job to do this after extraction?
or with a job to do this after extraction?
Well... I've done this in the past, but not recently so bear with me.
An aggregator will get you the pieces that you need for the header and trailer records per grouping. Your example of the extract output seems a little odd to me, shouldn't it more properly be:
Or perhaps the last DTL record should be a sum, that's something you can handle if need be.
I wrote out each 'set' of records to three flat files - one for all headers, one for all details, etc. They were then concatenated together and a Sort stage using a custom collating map put them into the correct order.
How? Each record was 'keyed' by type as the first field and then each complete record was just there as a second string field. In this case, if you did something like H<emp> for all headers, D<emp> for all detail and T<emp> for all trailers, then tell the sort stage to sort H then D then T. Or make it a little more obtuse but easier to sort by using A,B,C. After sorting, the key is simply pitched before writing out the final file.
That should get you on your way, unless someone has a better way. Be happy to hear it! :D
An aggregator will get you the pieces that you need for the header and trailer records per grouping. Your example of the extract output seems a little odd to me, shouldn't it more properly be:
Code: Select all
HDR 111
DTL 111 PAT 100
DTL 111 ABC 100
TRL 111 2 200
HDR 222
DTL 222 JON 200
DTL 222 JON1 200
TRL 222 2 400
HDR 333
DTL 333 JJJ 300
DTL 333 JJJ 300
TRL 333 2 600
I wrote out each 'set' of records to three flat files - one for all headers, one for all details, etc. They were then concatenated together and a Sort stage using a custom collating map put them into the correct order.
How? Each record was 'keyed' by type as the first field and then each complete record was just there as a second string field. In this case, if you did something like H<emp> for all headers, D<emp> for all detail and T<emp> for all trailers, then tell the sort stage to sort H then D then T. Or make it a little more obtuse but easier to sort by using A,B,C. After sorting, the key is simply pitched before writing out the final file.
That should get you on your way, unless someone has a better way. Be happy to hear it! :D
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 16
- Joined: Fri Apr 28, 2006 1:01 pm
Hi,
thanks for replying, this is what i was looking for. Can you give little more details , my requirement is same as you mentioned below. Instead of creating 3 seq files, Is there any way we can create in one file. Give more details please.
HDR 111
DTL 111 PAT 100
DTL 111 ABC 100
TRL 111 2 200
HDR 222
DTL 222 JON 200
DTL 222 JON1 200
TRL 222 2 400
HDR 333
DTL 333 JJJ 300
DTL 333 JJJ 300
TRL 333 2 600
Thanks
Patrick
thanks for replying, this is what i was looking for. Can you give little more details , my requirement is same as you mentioned below. Instead of creating 3 seq files, Is there any way we can create in one file. Give more details please.
HDR 111
DTL 111 PAT 100
DTL 111 ABC 100
TRL 111 2 200
HDR 222
DTL 222 JON 200
DTL 222 JON1 200
TRL 222 2 400
HDR 333
DTL 333 JJJ 300
DTL 333 JJJ 300
TRL 333 2 600
Thanks
Patrick
What 'little more details'? If you have specific questions, ask.
And why the concern about the three files? You create the three to create the one, I don't see any way to create that kind of output directly. However, if you are worried about 'the three files' ( why does that remind me of the 'Demolition Man' movie?
) replace them with a properly keyed hashed file. Write to it and then use a Universe stage over the top of it so you can use SQL to pull the results out. Or use a database.
Easier to deal with the three 'work' files, in my opinion.
And why the concern about the three files? You create the three to create the one, I don't see any way to create that kind of output directly. However, if you are worried about 'the three files' ( why does that remind me of the 'Demolition Man' movie?
![Wink :wink:](./images/smilies/icon_wink.gif)
Easier to deal with the three 'work' files, in my opinion.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 16
- Joined: Fri Apr 28, 2006 1:01 pm
Thanks for your suggestion
chulett wrote:What 'little more details'? If you have specific questions, ask.
And why the concern about the three files? You create the three to create the one, I don't see any way to create that kind of output directly. However, if you are worried about 'the three files' ( why does that remind me of the 'Demolition Man' movie?) replace them with a properly keyed hashed file. Write to it and then use a Universe stage over the top of it so you can use SQL to pull the results out. Or use a database.
Easier to deal with the three 'work' files, in my opinion.
If you dont wish to land three files, you need to compromise with number of stages. User transformer to split the recoreds in 3 links, user another transformer for each link to restructure, user link collector to merge all the three. Then user the sort with the sort order you per-specified.
Impossible doesn't mean 'it is not possible' actually means... 'NOBODY HAS DONE IT SO FAR'
-
- Participant
- Posts: 16
- Joined: Fri Apr 28, 2006 1:01 pm
Kumar.
thanks kumar for sharing the idea. It gives me an error when use the Link collector . It says "Link collector stage does not support in-process active-to-active inputs or outputs". I did use like what u suggested, like from Transfer created 3 links (hdr,trailer,detail) sort on key aggregated and used 3 transfers and then link collector(from 3 transfmers to link collector it is not accepting to merge, it gives above mentioned error message). Any help appreicated.
Thanks
Patrick
thanks kumar for sharing the idea. It gives me an error when use the Link collector . It says "Link collector stage does not support in-process active-to-active inputs or outputs". I did use like what u suggested, like from Transfer created 3 links (hdr,trailer,detail) sort on key aggregated and used 3 transfers and then link collector(from 3 transfmers to link collector it is not accepting to merge, it gives above mentioned error message). Any help appreicated.
Thanks
Patrick
You can also stick an IPC stage in between the transformer and the link collector stage to get rid of that error.
This error occurs because both transformer and link collector are active stages and DataStage will not allow a direct link between them unless you have an IPC stage in between or have Interprocess buffering enabled.
This error occurs because both transformer and link collector are active stages and DataStage will not allow a direct link between them unless you have an IPC stage in between or have Interprocess buffering enabled.
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.
-
- Participant
- Posts: 16
- Joined: Fri Apr 28, 2006 1:01 pm
Hi,
Thanks everybody, it worked. Thanks again.
Patrick
Thanks everybody, it worked. Thanks again.
Patrick
DSguru2B wrote:You can also stick an IPC stage in between the transformer and the link collector stage to get rid of that error.
This error occurs because both transformer and link collector are active stages and DataStage will not allow a direct link between them unless you have an IPC stage in between or have Interprocess buffering enabled.