JDBC adapter to SAP: select & join from multiple tables ???

[Former Member]

Hello,

I have a task to get an aggregating information from two tables and send it into SAP.

I use XI JDBC sender adapter to access to MSSQL DB.

I know how to construct SQL quiery:

(something like that)

SELECT table1.Date SUM(table1.sum) from table1

INNER JOIN table2

ON table1.Number = table2.Number

But I don't understand how to construct Data Type for this complex select operator?

How does it look like?

Where to store and how to map the selected data?

Please give me an example or an advice.

Thank You.

[aashish_sinha · Accepted solution]

Hi,

Use your structure like this.

<StatementName4>

<dbTableName action=”SELECT”>

<table>realDbTableName</table>

<access>

<col1/>

<col2/>

<col3/>

</access>

<key1>

<col2>val2old</col2>

<col4>val4</col4>

</key1>

<key2>

<col2>val2old2</col2>

</key2>

</dbTableName>

</StatementName4>

action=SELECT

Statements with this action cause existing table values to be selected. Therefore, the statement corresponds to an SQL SELECT statement.

The <access> block contains the column names to be selected, a <key> element contains the columns whose values must be identical with the specified value to get the new column values. The name of the <key> element is arbitrary. Column values within a <key> element are combined with a logical AND; different <key> elements are combined with a logical OR.

A statement with the action SELECT must have exactly one <access> element. The number of <key> elements with arbitrary names is not restricted.

The corresponding SQL statement for StatementName4 in the example above is as follows:

“

SELECT col1,col2,col3 FROM dbTableName WHERE ((col2=’val2old’ AND col4=’val4’) OR (col2=’val2old2’))

“

If there is no <key> element, or if there is a <key> element but it is empty, then no condition is specified and the entire table is to be selected. This may not be permitted by the configuration of the JDBC adapter for security reasons and will therefore result in an error during message processing and an appropriate error message.

The response document contains the result of the action in XML format as follows:

“

<row>

<column1>value11</column1>

<column2>value12</column2>

...

</row>

...

<row>

<column1>valueN1</column1>

<column2>valueN2</column2>

...

</row>

“

This will help you.

regards

Aashish Sinha

PS : reward points if helpful

[VijayKonam · Accepted solution]

Ashish, the one that you mentioned is for receiver side. We are talking about the sender side strucuture here.

Use row tag with 0..unbounded and names of the fields. you are done.

VJ

[justin_santhanam]

Igor,

It's simple. Run the SQL query in your DB and see the column names. Just create the data types as per your resulted column name.

raj.

[prateek]

Structure would be

<resultset> 1

<row> 0 to unbounded

<date></date> -

---

this is from table 1

<sum></sum> -

---

this is from table 2

</row>

</resultset>

Regards,

Prateek

[VijayKonam]

Hi,

Sender side, what ever you do by joining or anything, finally the thing that comes to XI is a recordset from Database. Since it is a lenear strucutre, have the row tag and then the fields.

VJ

[Former Member]

Hey

it won't be a complex structure,it will be a fairly simple structure, coz its on the sender side it will be pretty much same as the below blog

/people/yining.mao/blog/2006/09/13/tips-and-tutorial-for-sender-jdbc-adapter

Thanx

Aamir