JDBC Configuration with three Tables

[Former Member]

Hello together,

I want to create a scenario and I have to extract from three Tables with JDBC. In the next step i'll map to an IDOC.

I read in the threads, that is possible with one communication Channel.

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

(I Don't understand how to implement these informations)

My Questions:

1. I Have three tables Tab1 Tab2 and TAb3. In Tab2 and Tab3 there can be more the one dataset per Tab1 Key. Can you explain me, how to configure the select in the communication Channel?

2. I must define one import XML Messages. Can you describe me the message description with Tab1 , tab2 and tab3.

3.How can I link the Tab names of the select to to XML description.

regards

wolfgang

Edited by: Wolfgang Disch on Apr 20, 2009 11:38 AM

[Former Member]

I get the answer

/people/stefan.grube/blog/2005/12/29/new-functions-in-the-graphical-mapping-tool-xi-30-sp13

[Former Member]

I changed the sequence of the input xml, First lifnr 146128 than 146129 and finally 146128 again.

The function sort doesn't work. The queue of sort shows me first

146128

146129

146128

after sort the same

146128

146129

146128

is here the problem ?

[Former Member]

I want two IDOCs

one for 146128 and one for 146129

[Former Member]

Hi togehter,

soory for my delayed answer. I was ill.

I created my scenario but it doesn#t work.

This is my input:

<?xml version="1.0" encoding="utf-8" ?>

<ns:SAKRJOIN.resultSet xmlns:ns="urn:sap.com:jdbcAdapter">

<row>

<MANDT>100</MANDT>

<LIFNR>146128</LIFNR>

<BUKRS>41</BUKRS>

<row>

<MANDT>100</MANDT>

<LIFNR>146128</LIFNR>

<BUKRS>42</BUKRS>

</row>

<row>

<MANDT>100</MANDT>

<LIFNR>146129</LIFNR>

<BUKRS>42</BUKRS>

</row>

</ns:SAKRJOIN.resultSet>

This is my target:

IDOC Element CREMAS.CREMAS05 1..unbounded

BEGIN Attribut xsd:string required

EDI_DC40 Element EDI_DC40.CREMAS.CREMAS05 1

E1LFA1M Element CREMAS05.E1LFA1M 1

SEGMENT Attribut xsd:string required

MSGFN Element xsd:string 0..1 maxLength="3"

LIFNR Element xsd:string 0..1 maxLength="10"

ANRED Element xsd:string 0..1 maxLength="15"

If I mapped the field row to the target field IDOC I create three IDOCs, but I want only one IDOC per <LIFNR>. In this example I want to see two IDOCs. One for 146128 and one for 146129.

If I mapped <LIFNR> -> split by value (valuechanged) in field IDOC I get only one IDOC.

Whats wrong ?

I tried

lifnr>sort>splitbyvalue(valuechange)->collapse context->IDOC

Now I get three once again.

Kind regards

Wolfgang

[VijayKonam]

XI is doing as you map it. What is your problem now? Do you want 3 or 1?

VJ

[Former Member]

Hi together,

back to my three questions:

1. I Have three tables Tab1 Tab2 and TAb3. In Tab2 and Tab3 there can be more the one dataset per Tab1 Key. Can you explain me, how to configure the select in the communication Channel?

2. I must define one import XML Messages. Can you describe me the message description with Tab1 , tab2 and tab3.

3.How can I link the Tab names of the select to to XML description.

To define a join this not the problem, but can qou give me an example to configure step1 to step 3.

Thanks

Wolfgang

[Former Member]

Please let me know if you are using the Sender JDBC adapter or Receiver Adapter, So that I can give you an example accordingly.

[Former Member]

Hi

SELECT TAB1.field1 as fld1 , TAB1.field2 as fld2 , TAB2.FIELD3 AS FLD3, TAB2.FEILD4 AS FLD4 WHERE <<FOLLOWED BY A VLID WHERE CONDITION>>

Interface

<resultset> 1..1
<row> 0 to UB
<fld1>column-value</fld1>
<fld2>column-value</fld2>
<fld3>column-value</fld3>
<fld4>column-value</fld4>
</row>
</resultset>

http://help.sap.com/saphelp_nw04/helpdata/en/7e/5df96381ec72468a00815dd80f8b63/content.htm

Srini

[Former Member]

Hi,

You can use table JOIN's for your requirements. Use primary key to link the records and fetch the data. Make sure you use exactly one SELECT statement. Using Join inside Store procedures will be helpful and easy to maintain.

Regards,

Jitender

[Former Member]

Hi

Try to use Joins in Select query , Sender JDBC Adapter

Srini