Native SQL with SAP PI

[Former Member]

Hi, expertsI need your opinion on the following topic.

Table A contains the headers, table B contains details of headers in table A. the relationship between headers and details is 1:N

I need to join them through SAP PI without exceeding 100,000 registers and don´t deliver any header without all its details to the receiver.

I want to know if there is some way to do it using native SQL as database. A u201Cjoinu201D sentences isnu2019t helpful due to it can´t avoid more than 100,000 registers, and the sentence of SQL code of SAP PI can´t use this logic.

I´ll be very grateful to hear your suggestions, Thank you

[former_member854360]

Hi,

You can use SQL Query option for your requirement as mentioned by Baskar

<StatementName6>

<anyName action=u201D SQL_QUERYu201D | u201CSQL_DMLu201D>

<access>SQL-String with optional placeholder(s)</access>

<key>

  <placeholder1>value1</placeholder1>

  <placeholder2>value2<placeholder2>     

</key>

 </anyName >  
</StatementName6>

you can write the query in access tag with some placeholders (Variables)

and in key tag only mention the value of those placeholders

Example (Without Placeholders):

<root>

  <stmt>

     <Customers action="SQL_DML">

      <access> UPDATE Customers SET CompanyName='Company', Address='Street 3' WHERE CustomerID='CO'

      </access>

    </Customers>

  </stmt>

</root>

The unchanged SQL statement is executed in the database:

UPDATE Customers SET CompanyName='Company', Address='Street 3' WHERE CustomerID='CO'

Example (with Placeholders):

<root>

  <stmt>

    <Customers action="SQL_DML">

      <access> UPDATE Customers SET CompanyName=u2019$NAME$u2019, Address=u2019$ADDRESS$' WHERE CustomerID='$KEYFIELD$u2019

      </access>

      <key>

        <NAME>Company</NAME>

        <ADDRESS>Street 3 </ADDRESS>

        <KEYFIELD>CO</KEYFIELD>

      </key>

    </Customers>

  </stmt>

</root>

After the placeholders have been replaced, the same SQL statement is executed in the database as above:

UPDATE Customers SET CompanyName='Company', Address='Street 3' WHERE CustomerID='CO'

[baskar_gopalakrishnan2]

Refer this link for native sql

http://www.sdn.sap.com/irj/scn/go/portal/prtroot/docs/library/uuid/00b895cb-7148-2c10-e599-81501451f...

Why dont you try with the option Statement6 SQL_Query using the below link. This is not native SQL.

http://help.sap.com/saphelp_nw04/helpdata/en/2e/96fd3f2d14e869e10000000a155106/content.htm