Skip to Content
author's profile photo Former Member
Former Member

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

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.

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

4 Answers

  • Best Answer
    Posted on Dec 04, 2007 at 03:55 PM

    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

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Dec 04, 2007 at 03:02 PM

    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

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Dec 04, 2007 at 04:21 PM

    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.

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Dec 04, 2007 at 02:54 PM

    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

    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.