cancel
Showing results for 
Search instead for 
Did you mean: 

JDBC Adapter: Select from two tables with same field names

Former Member
0 Kudos

Hi all,

I have a challenge with JDBC adapter call to two different tables having similar field names (although they mean different things), how can I manage to get the two similar field names seprated somehow in the result XML.

Let say I have need to access two DB tables, SO and SOITEM (i.e. Salesorder and Salesorder item).

SO has columns SO_NUM, AMOUNT, STATUS

SOITEM has columns SO_NUM, ITEM, AMOUNT

SO table

SO_NUM AMOUNT STATUS

-


123456 200 1

SOITEM table

SO_NUM ITEM AMOUNT

-


123456 1 100

123456 2 50

123456 3 50

Here is my current select to the two tables

SELECT SO.SO_NUM, SO.AMOUNT, SO.STATUS, SOITEM.ITEM, SOITEM.AMOUNT FROM SO INNER JOIN SOITEM ON SO.SO_NUM = SOITEM.SO_NUM WHERE SO.STATUS = '1'

resultset look following

<resultset>

<row>

<SO_NUM>123456</SO_NUM>

<AMOUNT>200</AMOUNT>

<STATUS>1</STATUS>

<ITEM>1</ITEM>

<AMOUNT>100</AMOUNT>

</row>

<row>

<SO_NUM>123456</SO_NUM>

<AMOUNT>200</AMOUNT>

<STATUS>1</STATUS>

<ITEM>2</ITEM>

<AMOUNT>50</AMOUNT>

</row>

<row>

<SO_NUM>123456</SO_NUM>

<AMOUNT>200</AMOUNT>

<STATUS>1</STATUS>

<ITEM>4</ITEM>

<AMOUNT>50</AMOUNT>

</row>

</resultset>

Is there anyway in the select statement to get the two different AMOUNT fields with two different names into the xml, i.e.

<row>

<SO_NUM>123456</SO_NUM>

<AMOUNT>200</AMOUNT>

<STATUS>1</STATUS>

<ITEM>4</ITEM>

<ITEMAMOUNT>50</ITEMAMOUNT>

</row>

Kind regards, Vesa

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Hi Vesa,

Try this

In mapping, give <b>SQL_QUERY</b> for <b>action</b> attribute and give the full query in <b>access</b> tag.

Refer this link for action= SQL_QUERY http://help.sap.com/saphelp_nw04/helpdata/en/2e/96fd3f2d14e869e10000000a155106/content.htm

For chaning the column name, modify the query as

SELECT SO.SO_NUM, SO.AMOUNT, SO.STATUS, SOITEM.ITEM, SOITEM.AMOUNT <b>AS ITEMAMOUNT</b> FROM SO INNER JOIN SOITEM ON SO.SO_NUM = SOITEM.SO_NUM WHERE SO.STATUS = '1'

Regards,

Uma

Former Member
0 Kudos

thanks, works fine.

simple but efficient

Answers (3)

Answers (3)

bhavesh_kantilal
Active Contributor
0 Kudos

Vesa,

Are you are using a Sender JDBC adapter?

One option is to use the ALIAS fieldname in your Select Query.

<i>Select empname as EMPNAMALIAS from...</i>

This way you can distinguish the field names of the 2 tables.

Regards

Bhavesh

prabhu_s2
Active Contributor
0 Kudos

can u pls be more clear...unable to get the real picture of ur req.

Former Member
0 Kudos

I think you cant acces two tables at a time