How to fetch values from multiple tables using JDBC receiver adapter

[Former Member]

Hi,

In my requirement, For a particular business process, i need to fetch information/records from multiple tables and join them into one and pass it back to the sender.

How can i do this?

Thanks & Regards,

Senthil.

[former_member206760]

Dear Senthil,

is this wrking

[Former Member]

Dear Tarang,

I had gone to a meeting.. Give me sometime to check this out.

Thanks a lot.

Regards,

Senthil.

[former_member206760]

Dear senthil,

As you must be knowing that query specified her eis just an eg...it will change according to the database that you are using

[former_member206760]

Dear Senthil,

i will give u the complete solution..

create the target data type as

MT (This is request message type)

+Statement1

++anyname

++access

++key

+++field1

In Action : write SQL_Query.

in acess : write :

SELECT U1.PEID ,U1.PECOMP ,U1.PEVEND ,U1.PEBANK ,U2.PISUB ,U2.PICURR ,U2.PIINVN ,U2.PIIDTE FROM UAPE U1, UAPI U2 WHERE (U1.PECOMP = U2.PICOMP)

AND U1.PETREF = '$field1$'

see the $ sign here so taht wahtever value that goes into field1 will be passed to this select query

you also have to create appropriate response structure so that you can captures the rows sent by the database

MT_response (...note that MT here is the same name as we used above followed by _responswe)

+Statement1_response

++row

+++field1(fields which would be returned by the above query

+++field2

also create a inbound sync MI with request message type as MT and reponse as MT_response

now suppose your sender was RFC synchronous then you cna mapp this MT_response with the RFC response and specify this response mapping in the "response" tab of the interface mapping

Giving points is another way to say thanks

Edited by: Tarang Shah on Mar 5, 2009 11:50 AM

[Former Member]

Dear Tarang,

I have one doubt. Where should i write my select query? In Action attribure or in access?

This is my structure:

<STATEMENT>

<TABLENAME ACTION="">

<TABLE></TABLE>

<ACCESS>

<F1></F1>

<F2></F2>

</ACCESS>

<KEY>

<F1 compareOperation=""></F1>

</KEY>

</TABLENAME>

</STATEMENT>

Normally we will assign the table name to "TABLE" field, In my case do i need leave this field or what?

Please help me in this...

Thanks & Regards,

Senthil.

[former_member206760]

In Action : write the word "SQL_Query".

in access : write your actually select query ( like select *....)

no need of node "table"...see the structure that i have specified above

[Former Member]

Hi Tarang,

I have created a structure without the element "TABLE". In action i gave "SQL_Query" and in access i gave the actual query.(Select * from table1 t1, table2 t2 where t1.key = t2.key)

I am getting the following error:

Error processing request in sax parser: Error when executing statement for table/stored proc. 'TABLENAME' (structure 'STATEMENT'): java.sql.SQLException: A string parameter value with zero length was detected

Could you explain me the reason for this error and how to solve this?

Thanks & Regards,

Senthil.

[varun_k]

Hi senthil nathan,

Please refer to this blog which has similar requirement, which might help you

[Extracting data from an AS400 with multimember tables using XI|https://www.sdn.sap.com/irj/scn/weblogs?blog=/pub/wlg/2497] [original link is broken] [original link is broken] [original link is broken];

Regards,

Varun