Oracle --> XI --> IDOC

[Former Member]

Hi All,

In my JDBC Sender scenario i need to pick one header field from header table and Item fields from the other using a common link. For my message type which gets this data, i had taken the structure like

Message Type

row --> 0-unbounded

Header-field --> 1-1

Item-field1 --> 0-unbounded

Item-field2 --> 0-unbounded

Item-field3 --> 0-unbounded

My question here is, when iam picking the data like one header field from header table and the item fields from the item table, will this work ?

Regards,

Kittu.

Message was edited by: kittu chowdary

[bhavesh_kantilal · Accepted solution]

Hi Kittu,

Let me makes thinsg clear with an example,

select header.field,item.field1,item.field2, item.field2 from header and item where ...

In this case, the source datatype will be,

<root>

< row>

<field></field>

<field1> </field1>

<field2></field2>

<field3></field3>

</row>

</root>

For every row that satisfies the Select Query, you will have a ROW occuring in the source.

Hope this helps. If you can give the exact query that you are trying to use, the Datatype of the JDBC adapter can be arrived at.

Regards,

Bhavesh

[Former Member · Accepted solution]

Hi Bhavesh,

Iam clear about that, but my question is that if i have three item records which matches the condition for the header record, in this case will i get the three item record fields in the same ROW or will i get three corresponding ROWS for each. Please calrify..

Regards,

Kittu.

[bhavesh_kantilal · Accepted solution]

Hi Kittu,

It all depends on the way you frame your SELECT query.

Can you please give us the Select Query and maybe the table stracuture so that I can understand what you are trying to acheive a bit better.

<i>i have three item records which matches the condition for the header record</i>

If each of these item record is a different row in your table, then they will come in different ROWS. If they are all different ciolumns of the Table, then the same ROW.

Regards,

Bhavesh

[Former Member · Accepted solution]

Hi Bhavesh,

My requirement is like this...

I need to pick the Header and the Item level data from two different tables. since i can have multiple items for my header record, each item is a different record in the table.please throw some light on the design so that we can acheive the requirement.Please let me know whether there is any way to fetch the Header and the Item records into a single ROW structure..

The select which i had written presently is like

select hdr.ts_i, det.fg_stock, det.id_to, det.cd, det.qt_action from HEADER hdr, DETAILS det where hdr.cd_wr = det.cd_wr and hdr.no_doc = det.no_doc and hdr.fg_error is null

i had created my data type thinking that the ROW returns me the details of all the items in a single select. The data type which i created resembles like given below

Message Type

row --> 0-unbounded

Header-field --> 1-1

Item-field1 --> 0-unbounded

Item-field2 --> 0-unbounded

Item-field3 --> 0-unbounded

regards,

kittu.

[bhavesh_kantilal · Accepted solution]

Hi Kittu,

<i>select hdr.ts_i, det.fg_stock, det.id_to, det.cd, det.qt_action from HEADER hdr, DETAILS det where hdr.cd_wr = det.cd_wr and hdr.no_doc = det.no_doc and hdr.fg_error is null</i>

<ROOT>

<row>

<ts_i> </ts_i>

<fg_stock> </fg_stock>

<id_to> </id_to>

<cd> </cd>

<qt_action> </qt_action>

</row>

</ROOT>

(or)

<ROOT>

<row>

<hdr.ts_i> </hdr.ts_i>

<det.fg_stock> </det.fg_stock>

<det.id_to> </det.id_to>

<det.cd> </det.cd>

<det.qt_action> </det.qt_action>

</row>

</ROOT>

Hope this clears things,

Regards,

Bhavesh

[moorthy]

Hi Kittu,

Check the last part of this help-

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

Also have a look into this thread-

Based on this, I think you can understand that, it requires just one flat structre with Header and other fields.

If you are using 2 tables, you need to use Join condition.

Hope this helps,

Regards,

Moorthy

[Former Member]

Hi Moorthy,

Thanks for the reply, here my question is different.

Iam not asking about the select query, i want to know whether the structure which i had taken will work out for the Header, line item requirement.

If u see in the structure which i had taken; the header field is 1-1 where as the item fields are 0-unbounded, will that work when my select is picking up the data for one Header and the corresponding Item fields

Regards,

Kittu.

[moorthy]

<i>Iam not asking about the select query,</i>

>>Yes, that's the reason i told you to check the last part of the SAP help given in my ealier post. It clearly tells about your data structure. and how result set is created..

In simpler way, all columns used in the select statement will give you one structre..So your occurence is set to segment/node level. Not in the field level.

Hope this helps,

Regards,

Moorthy

[Former Member]

Hi Moorthy,

Thanks for the clarification. Just one more doubt..

If i have one header with 3 line items then as per what u have said, i will get three structures with header and the corresponding line item in each of the structure..

Hope this is what u said.. Correct me if iam wrong.