Help needed on JDBC select - JOIN

[Former Member]

I have this rquirement

one table for header

one table for lines

the table for the header has one unique record and the table for the lines has multiple records that belongs to the header

for instans the header table has

doc nr 1

f1

f2

f3

lines table has

doc nr 1 pos 1 f1 f2 f3

doc nr 1 pos 2 f1 f2 f3

doc nr 1 pos 3 f1 f2 f3

and so on.

I have to fetch the one records from the header table and all the records for the lines that belongs to the header

like this

select header.f1 header.f2 header.f3 lines.pos lines.f1 lines.f2 lines.f3 from header , lines where header.docnr = lines.docnr

my problem that the data from the header is repeated for each record from the lines table.

the result has to be

<header>

f1

f2

f3

<lines>

pos 1

f1

f2

f3

</lines>

<lines>

pos 2

f1

f2

f3

</lines>

<lines>

pos 3

f1

f2

f3

</lines>

</header>

the result has to be mapped to an Idoc with one header and multipl lines

any help will be higly apreciated

Thanks

[Former Member]

Hi Merhej,

You need to create multiple idocs at the receiver end.

So you need to change cardinality for that, refer the below blog:

/people/michal.krawczyk2/blog/2005/12/04/xi-idoc-bundling--the-trick-with-the-occurance-change

For getting single header elements, try this mapping:

Header_Field --> removeContext ---> splitByvalue(valueChange) --> collapseConext ---> Target Header_Field

-Supriya.

[Former Member]

Hi

Problem solved with you help

Thanks.

[Former Member]

Hi Merhej,

This can be easily controlled in message mapping.

You have to use node functions, like collapseContext, splitByvalue,etc.

If required, give the exact source & target structure, somebody from forum will help you out with mapping.

-Supriya.

[Former Member]

Hi Supriya

Thanks for your effort

The source structure is as following

<resultset>

<row>

header.docnr = 1

header.f1

header.f2

header.f3

lines.pos 1

lines.f1

lines.f2

lines.f3

</row>

<row>

header.docnr = 1

header.f1

header.f2

header.f3

lines.pos 2

lines.f1

lines.f2

lines.f3

</row>

<row>

header.docnr = 2

header.f1

header.f2

header.f3

lines.pos 1

lines.f1

lines.f2

lines.f3

</row>

<row>

header.docnr = 2

header.f1

header.f2

header.f3

lines.pos 2

lines.f1

lines.f2

lines.f3

</row>

</resultset>

The target structur is ORDERS05, one idoc has to be created for each header.docnr , in this example 2 idocs has to be created one for docnr 1 and one for docnr 2

furthermore only one Header should be mapped to the segment EDI_DC40, but here the Header record is repeated for each line item., it is a result from the JDBC sender adpater

I hope for any help

BR

[stefan_grube]

Check the example with formatByExample:

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

It seems difficult when you do it the first time, but you will have to use this pattern often.

[Former Member]

HI Stefan

Thanks for your replay

I will try this as soon as I figure out how to select the one header and the multiple lines

BR.