on 02-09-2007 12:04 PM
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
can u pls be more clear...unable to get the real picture of ur req.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
I think you cant acces two tables at a time
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
84 | |
24 | |
11 | |
9 | |
7 | |
6 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.