cancel
Showing results for 
Search instead for 
Did you mean: 

Joins statements in JDBC Receiver

former_member203627
Participant
0 Kudos

Hi,

Can we use Join tables in JDBC receiver, this would be synchronous.

I have 2 tables from where I need to read.

Regards

Ria

Accepted Solutions (0)

Answers (4)

Answers (4)

former_member206760
Active Contributor
0 Kudos

Ria,

This can be done using a standard way...

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 : pass constant SQL_Query.

in acess : pahe following query

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

former_member203627
Participant
0 Kudos

Thanks for your detailed solution, I gave points to Jabbs.....

Former Member
0 Kudos

Hi,

you can do it without joins also using Stored Procedures(SP)

1) Create temporary table inside the SP which has all the fields.

2) Insert the data into temp table from multiple tables by executing the joins which u have created.

3) After inserting all the records just execute a select * on that temporary table which is the final o/p.

4) Drop temporary table.

Hope this will help you.

jabbs:)

former_member203627
Participant
0 Kudos

Where I should write this SP?.....

Former Member
0 Kudos

you need to write stored procedures in your database ( example : SQL or Oracle) with which you are communicating.

for the stored procedure structure you can refer to :

http://help.sap.com/erp2005_ehp_04/helpdata/EN/22/b4d13b633f7748b4d34f3191529946/frameset.htm

/people/sap.user72/blog/2005/06/01/file-to-jdbc-adapter-using-sap-xi-30

Thanks,

Hetal

Former Member
0 Kudos

hi

you can write the stored procedure in your database and execute it thru the mapping.

example:

DT_************

StatementName

StoredProcName

(attribute)action ( Map constant:Execute)

table (constant: your stored procedure name)

Parameter1 (if your are passing parameters to the procedure )

isInput(attribute) (constant: TRUE)

Type(attribute) (VARCHAR or INT or ....)

Parameter2....

isInput

Type

..........

jabbs

former_member203627
Participant
0 Kudos

Solved ! ! !

Former Member
0 Kudos

Yes you can join tables (2 or more than 2 also) in JDBC receiver channel.

Thanks,

Hetal

Shabarish_Nair
Active Contributor
0 Kudos

yes you can.

More details follow up with this thread -

former_member203627
Participant
0 Kudos

Hi

The link you refered is talking about the JDBC sender not for JDBC receiver.

I have a querry which looks like following and if I can use the same in JDBC receiver Synchronous.

select *

from BSPYHP a,BSPYDP b

where

a.BUSTYP in (u2018Bu2019,'F','T') and

a.SETDAT = b.SETDAT and

a.BATCHN = b.BATCHN and

substr(a.BATCHN,1,4) = substr(a.DEPNUM,1,4)

order by a.BUSTYP, a.SETDAT ,a.BATCH

How can we have this kind of join in JDBC Receiver.... Can you please give the sturtcure also how it would look like ?

Please help ! ! ! !

Regards

Ria

Edited by: ria sen on Apr 1, 2009 6:07 AM