Skip to Content

Joins statements in JDBC Receiver

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

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

4 Answers

  • Posted on Apr 01, 2009 at 03:46 AM

    yes you can.

    More details follow up with this thread - jdbc---table-join

    Add a comment
    10|10000 characters needed characters exceeded

    • 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

  • author's profile photo Former Member
    Former Member
    Posted on Apr 01, 2009 at 04:05 AM

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

    Thanks,

    Hetal

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Apr 01, 2009 at 04:12 AM

    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:)

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Apr 01, 2009 at 05:00 AM

    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

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.