Originally posted by: iWork321

Hello

I am relatively new to WTX development. I am trying to see how I can do a database (db2 - source)  to flat file (positional file - target) using WTX. I have 3 tables (Table A, B and C which are joined by a single column. A to C and A to C are one to many relationship.

The output format should be displayed as below

Row from Table A

        Row(s) from Table B that pertain to this parent row A

        Row(s) from Table C that pertain to this parent row A

Next Row from Table A...

        Row(s) from Table B that pertain to this parent row A

        Row(s) from Table C that pertain to this parent row A

I tried to create a Database Query Type tree that joins all the 3 tables , but didn't know how I could transform the joined result set into a multi - tiered output format.

Any ideas????

Thanks

Originally posted by: jvanboga

Being new I'd recommend you use the Database Interface Designer (DBID) build your query there.  You can generate a data definition (type tree) via the DBID that will match your definition. You'll use that as an input card for your map.  Next define your output data definition (type tree).  You can define your output to be as simple or complex as you like or define as many output definitions as you'll need.

Originally posted by: PaulBrettIBM

I have a mapping solution example for you here.  I have used flat files as input, but you should be able to see the concepts quite easily, and apply those to tables instead.

Input1:

1,AAAAAAA,AAAAAAAAAAA,AAAAAAAAAAAA,AAAAA
2,BBBBBBB,BBBBBBBBBBB,BBBBBBBBBBBB,BBBBB
3,CCCCCCC,CCCCCCCCCCC,CCCCCCCCCCCC,CCCCC

Input2:

1,aaaaaaa,aaaaaaaaaaa,aaaaaaaaaaaa,aaaaa
1,aaaaaaa,aaaaaaaaaa,aaaaaaaaaaaa,aaaaa
1,aaaaaaa,aaaaaaaaa,aaaaaaaaaaaa,aaaaa
1,aaaaaaa,aaaaaaaa,aaaaaaaaaaaa,aaaaa
2,bbbbbbb,bbbbbbbbbbb,bbbbbbbbbbbb,bbbbb
2,bbbbbbb,bbbbbbbbbbb,bbbbbbbbbbb,bbbbb
2,bbbbbbb,bbbbbbbbbbb,bbbbbbbbbb,bbbbb
2,bbbbbbb,bbbbbbbbbbb,bbbbbbbbb,bbbbb
2,bbbbbbb,bbbbbbbbbbb,bbbbbbbb,bbbbb
2,bbbbbbb,bbbbbbbbbbb,bbbbbbb,bbbbb
3,ccccccc,ccccccccccc,cccccccccccc,ccccc
3,ccccccc,ccccccccccc,ccccccccccc,ccccc
3,ccccccc,ccccccccccc,cccccccccc,ccccc

Input3:

1,1111111,11111111111,111111111111,11111
1,1111111,11111111111,11111111111,11111
1,1111111,11111111111,1111111111,11111
2,2222222,22222222222,222222222222,22222
2,2222222,22222222222,22222222222,22222
3,3333333,33333333333,333333333333,33333
3,3333333,33333333333,33333333333,33333
3,3333333,33333333333,3333333333,33333
3,3333333,33333333333,333333333,33333
3,3333333,33333333333,33333333,33333
3,3333333,33333333333,3333333,33333

My output flie looks like this:

1,AAAAAAA,AAAAAAAAAAA,AAAAAAAAAAAA,AAAAA
  1,aaaaaaa,aaaaaaaaaaa,aaaaaaaaaaaa,aaaaa
  1,aaaaaaa,aaaaaaaaaa,aaaaaaaaaaaa,aaaaa
  1,aaaaaaa,aaaaaaaaa,aaaaaaaaaaaa,aaaaa
  1,aaaaaaa,aaaaaaaa,aaaaaaaaaaaa,aaaaa
  1,1111111,11111111111,111111111111,11111
  1,1111111,11111111111,11111111111,11111
  1,1111111,11111111111,1111111111,11111
2,BBBBBBB,BBBBBBBBBBB,BBBBBBBBBBBB,BBBBB
  2,bbbbbbb,bbbbbbbbbbb,bbbbbbbbbbbb,bbbbb
  2,bbbbbbb,bbbbbbbbbbb,bbbbbbbbbbb,bbbbb
  2,bbbbbbb,bbbbbbbbbbb,bbbbbbbbbb,bbbbb
  2,bbbbbbb,bbbbbbbbbbb,bbbbbbbbb,bbbbb
  2,bbbbbbb,bbbbbbbbbbb,bbbbbbbb,bbbbb
  2,bbbbbbb,bbbbbbbbbbb,bbbbbbb,bbbbb
  2,2222222,22222222222,222222222222,22222
  2,2222222,22222222222,22222222222,22222
3,CCCCCCC,CCCCCCCCCCC,CCCCCCCCCCCC,CCCCC
  3,ccccccc,ccccccccccc,cccccccccccc,ccccc
  3,ccccccc,ccccccccccc,ccccccccccc,ccccc
  3,ccccccc,ccccccccccc,cccccccccc,ccccc
  3,3333333,33333333333,333333333333,33333
  3,3333333,33333333333,33333333333,33333
  3,3333333,33333333333,3333333333,33333
  3,3333333,33333333333,333333333,33333
  3,3333333,33333333333,33333333,33333
  3,3333333,33333333333,3333333,33333

Let me know if I have understood the mapping requirement correctly.

Thank you.

Paul

Follow me on Twitter

Attachment(s)

3tables.zip   5 KB 1 version

Originally posted by: iWork321

Thank you very much for detailed example :)

Originally posted by: FentonO

Would it not be more efficient for the database to do a single query (with joins) to get the data?

Fenton O.

Originally posted by: CNewman

Holding a DB admin role in the past, I would have to agree.   DB2 is just going to be so much better at collating this data into a single result set.   

Originally posted by: iWork321

A single query with joins definitely sounds simple but I was not sure how I would take a single result set and transform the data back into 2 tier format (i.e group data together)..