JDBC - Table join

[Former Member]

Hi All,

I need to configure a sender JDBC adapter which needs to pull data from 2 tables. I need all the fields of both the tables. Say my first table contains field1, field2 andmy second table contains field3 and field 4. For this scenario how can I develop a data type? Also can I use a select querry which pulls data from 2 tables?

Thanks & Regards,

Jai Shankar.

[bhavesh_kantilal · Accepted solution]

Hi Jai,

You can give the Select Query as,

<b>SELECT TAB1.field1 as fld1 , TAB1.field2 as fld2 , TAB2.FIELD3 AS FLD3, TAB2.FEILD4 AS FLD4 WHERE <<FOLLOWED BY A VLID WHERE CONDITION>></b>

For such a query, the datatype will be ,

<i><b><resultset> 1..1

<row> 0 to UB

<fld1>column-value</fld1>

<fld2>column-value</fld2>

<fld3>column-value</fld3>

<fld4>column-value</fld4>

</row>

</resultset></b></i>

http://help.sap.com/saphelp_nw04/helpdata/en/7e/5df96381ec72468a00815dd80f8b63/content.htm

Regards,

Bhavesh

[bhavesh_kantilal · Accepted solution]

Jai,

the option in the thread mentioned By prashanth refers to the Receiver JDBC adapter.

For a sender JDBC adapter , you can use the Select with a JOIN as i have shon in my previous reply or you can make a call to a stored procedure.

Just to note that Sender JDBC adapters allows Oracle Stored Procedures for Sender only from SP16 onwards.

If you are belowe SP16, and trying to connect to an ORacle using JDBC adapters, you need to have a SELECT with JOIN.

Regards,

Bhavesh

[Former Member · Accepted solution]

Hi Bhavesh,

My tables contain 10 fields each. So is it possible I can use a querry like select * from tab1,tab2?

Also can I use graphical editor to design the data type?

<b>Pls note my database is not oracle or sql server, it is AS/400</b>

Thanks & Regards,

Jai Shankar.

Message was edited by: Jai Shankar

[Former Member · Accepted solution]

Hi,

Yes you can use graphical editor but the structure needs to be in the specified format.

Regards

Vijaya

[Former Member · Accepted solution]

Hi ,

<i>My tables contain 10 fields each. So is it possible I can use a querry like select * from tab1,tab2?</i>

yes...basing on key fields you can select..

<i>Also can I use graphical editor to design the data type</i>

yes...

sekhar

[bhavesh_kantilal · Accepted solution]

Jai,

<i>My tables contain 10 fields each. So is it possible I can use a querry like select * from tab1,tab2</i>

If you are not using a WHERE clause in your SELECT then it is possible. But, if you do not use a WHERE clause, then you are in for trouble as all rows in the table will be selected every polling interval.

You need to use a where clause in SELECT . and when you use a where clause, you will need a JOIN conidition to select data from the 2 tables. So, you will have to explicity mention the table column names. If the column names are different in both the tables, then no ALIAASING is needed.

Yes, you can use Graphical editore to create the datatype.

Regards,

Bhavesh

[bhavesh_kantilal · Accepted solution]

Prashanth,

if you look at the final datatype listed in the thread, it refers to the SELECT for the Receiver JDBC adapter.

So, I guess it has both Sender as well as receiver JDBC adapter Selection , but, the final solution looks like poitning to a Receiver JDBC adapter.

Regards,

Bhavesh

[Former Member · Accepted solution]

Hi All,

Thanks for all your help. Can I design my data type in this format

>>resultset

>>row

>>field1

>>field2

>>field3

>>field4

>>field5

Also do I need to mention my table name in the data type?

Thanks & Regards,

Jai Shankar.

[Former Member · Accepted solution]

Hi ,

<i>resultset

>>row

>>field1

>>field2

>>field3

>>field4

>>field5</i>

Yes you can design like this .. You have to mention your data type name in the sender Jdbc Adapter.

No need to mention your table name in the data type

sekhar

[bhavesh_kantilal · Accepted solution]

Jai,

While createing the DATATYPE , you will mention something Data tYpe name as something like DT_JDBCSENDER for the dataype of your JDBC sender.

and then ,

<DT_JDBCSENDER>

<resultset>

<colm1>

<colm2>

</resultset>

</DT_JDBCSENDER>

<b>Now, you need to mention the DT_JDBCSENDER and the namespace as the values against DOCUMENT NAME AND DOCUMENTNAMESPACE in your Sender JDBC adapter.</b>

Please read the contents under this link for all info,

http://help.sap.com/saphelp_nw04/helpdata/en/7e/5df96381ec72468a00815dd80f8b63/content.htm

Regards,

Bhavesh

[Former Member · Accepted solution]

Hi All,

Thanks for all your help. I l try my scenario with these details and get back to you if I have any problems.

Thanks & Regards,

Jai Shankar.

[Former Member]

Hi Jai,

Since you mentioned that ur database is going to be AS/400.. I looked up these links for u.

http://as400bks.rochester.ibm.com/html/as400/v4r5/ic2924/index.htm?info/java/rzaha/jdbc.htm

http://as400bks.rochester.ibm.com/html/as400/v4r5/ic2924/info/java/rzahh/jdbc.htm

http://publib.boulder.ibm.com/iseries/v5r2/ic2924/index.htm?info/rzahh/javadoc/JDBCProperties.html

Will come in handy later

cheers,

Prashanth

P.S Please mark helpful answers

[Former Member]

Hi Jai,

Since you mentioned that ur database is going to be AS/400.. I looked up these links for u.

http://as400bks.rochester.ibm.com/html/as400/v4r5/ic2924/index.htm?info/java/rzaha/jdbc.htm

http://as400bks.rochester.ibm.com/html/as400/v4r5/ic2924/info/java/rzahh/jdbc.htm

http://publib.boulder.ibm.com/iseries/v5r2/ic2924/index.htm?info/rzahh/javadoc/JDBCProperties.html

Will come in handy later

cheers,

Prashanth

P.S Please mark helpful answers

[Former Member]

@Bhavesh

Sender JDBC adapter??? Bhavesh.. These are excerpts from the thread..

<i>>>U can get the data from two tables via a single select statement.

If the JDBC adapter is of sender type, then we can use the select query to select data from two tables.</i>

<i>>>U can use the Query SQL Statement in Processing paramters in Sender JDBC adapter for selecting datas from two tables.</i>

Did I miss something?

cheers,

Prashanth

[Former Member]

Hi ,

see this weblog also...

https://www.sdn.sap.com/irj/sdn/weblogs?blog=/pub/wlg/1725 [original link is broken] [original link is broken] [original link is broken] [original link is broken] [original link is broken]

sekhar

[Former Member]

Hi Jai,

This thread has got all u need to know..

Hope it helps!

cheers,

Prashanth

P.S Please mark helpful answers