JDBC sender strucutre

[former_member229310]

Hello all, i have a scenario having JDBC sender that needs to read from 3 tables.

TB1 (Header)

TB 2  (Item) , 

TB3 (Text) .

I need to know how to create the source JDBC strucutre. I need the data to be read in below format,

TB1   

    ROW                      0: UNBOUNDED         

          DOCNUM         

          FILED1         

          FILED2   

      TB2         

                ROW                  0:UNBOUNDED

                      DOCNUM               

                      FIELD3               

                      FIELD4

        TB3         

                ROW                  0:UNBOUNDED               

                        DOCNUM               

                        FIELD5               

                        FIELD6 

1) TB1 is a header table and consist of 1 record for every DOCNUM whereas there will be multiple entries present in other 2 tables.      How should i write a select query to fetch all the entries from 3 tables using join condition and populate in a single structure.

2) If i create a single structure as shown above does my select query fetch entries into this meta data ? I need TB2 and TB3 to be inside the header only (TB1).

Thanks in advance.

[Former Member]

Hi Hari

I will also suggest you to create a store procedure in DB and use the procedure in the sender JDBC adapter.

You need to create your strcuture as below to receive the data from DB

The logic for selecting the data needs to be written inside the store procedure.

[anand_shankar10]

I agree with Indrajeet. We should try to keep things least complicated. Else the structure that you are asking is like sub tables and it will be tough to get the transformation logic as we will have to call multiple table.

Thanks

Anand

[Former Member]

Hello,

I would suggest you to either use SP or use Views instead of writing join queries to fetch data from multiple tables.

>>I need to know how to create the source JDBC strucutre. I need the data to be read in below format,

Ur structure should be like this:

row... 0...Unbounded

Field1

Field2

Fiel3...

Thanks

Amit Srivastava

[iaki_vila]

Hi Hari,

1)

You only need to use in the 'ON' statement the fields that are the key in the first table and they are also the other two. Check this link for more information http://www.w3schools.com/sql/sql_join.asp

2)

You only need to add the columns according to the format pointed here (check final example) http://help.sap.com/saphelp_nw73/helpdata/en/7E/5DF96381EC72468A00815DD80F8B63/frameset.htm

Regards.