Receiver JDBC adapter inner join

[Former Member]

I am trying to create Receiver JDBC adapter.

I have an employee table in which each employee may have supervisor. Not all employess have supervisor. Now I want to get employee information for a specific employee_id. In case if the employee has supervisor, I want to get supervisor information as well.

Since I want to get<u> both employee and supervisor</u> (who is also an employee) information for employee_id = 1234 The SQL looks like as follows (join on the same table).

<b>select * from employee_tbl where employee_id in (1234, select supervisor_id from employee_tbl where employee_id in 1234))

Now my questions is how the Datatype for the JDBC request message or xml message structure looks like for above select statement.</b>

I appreciate your help.

Thank you,

Balaji

[former_member189387 · Accepted solution]

Balaji ,

<b> In the place of action="SQL_DML" please type as SQL_QUERY .</b>

then whatever query you want u can write

Hope it helps.,

• Assign Points if Helpful

Best Regards .,

V.Rangarajan

[Former Member · Accepted solution]

thank you for help.

[former_member189387]

Hi ,

Please refer the passgae in the above link

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=’$NAME$’, Address=’$ADDRESS$' WHERE CustomerID='$KEYFIELD$’

</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'

U can write whatever query u want

• Assing points if helpful

Regards.,

V.Rangarajan

[Former Member]

Hello Wg,

Thank you for your response, I already looked at this page before posting and I could not correlate to select statement with join I am looking for.

<b>select * from employee_tbl where employee_id in (1234, select supervisor_id from employee_tbl where employee_id in 1234))</b>

<b>I would like to see how the Datatype for the JDBC request message or xml message structure looks like for above select statement.</b>

Please provide details if I am missing some thing.

THank you,

Balaji

[Former Member]

Hi,

Isn't it better to use left join in your case?

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

/wg

<i>** Please reward points if found it useful **</i>