| Author |
Message
|
| Gale |
 Posted: Mon Sep 11, 2006 2:54 pm Post subject: Working with tree "tables" vs. hierarchy structure |
 |
|
Novice
Joined: 08 Dec 2004
Posts: 12
Location: Portland, OR
|
I am receiving RFC formatted data from SAP and translating it to XML. The SAP programmer has created one business object as a group of 9 "tables", all are siblings with a key to relate it to the others.
I am inexperienced working with this type of input structure. The SAP developer is reluctant to change it. What is the best way to work with data structured this way? Would it be more efficient for me to set references on each table and select the data where key = key or to have the input changed into a more familiar tree structure with one reference? From what I have seen during research, tree joins are not an option.... Please help! |
|
| Back to top |
|
 |
| jefflowrey |
| Posted: Mon Sep 11, 2006 3:55 pm Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
Well, first off.
Every structure in the logical message tree is, umm, a tree.
So the result of any database SELECT or any structured message received from a queue is a tree.
And you can use SELECT, the ESQL Select, to do joins on multiple trees.
_________________
I am *not* the model of the modern major general. |
|
| Back to top |
|
|
| elvis_gn |
| Posted: Mon Sep 11, 2006 7:54 pm Post subject: |
|
|
Padawan
Joined: 08 Oct 2004
Posts: 1905
Location: Dubai
|
Hi Gale,
If you could post a sample we could help you with it...
You will find a lot of code snippets here on how to go about this.
Best of luck.
Regards. |
|
| Back to top |
|
|
| Gale |
| Posted: Tue Sep 12, 2006 8:09 am Post subject: |
|
|
Novice
Joined: 08 Dec 2004
Posts: 12
Location: Portland, OR
|
I understand that this structure is a tree. I was using the word table to describe the sections of the tree. Sorry. I didn't mean to cause confusion.
I read somewhere in the Forum that joins on the tree using ESQL gave "unpredictable" results. Perhaps this was with an earlier version or I misunderstood?
Here is an example of the input structure. If I have editted it too much for space, please let me know. Each group can occur 0 to many times. The only key is PARTwith the exception of supplier and supplier costs having a relationship based on ORGANIZATION and ID.
<p:DATE>p:LV_I_DATE</p:DATE>
<p:TIME>p:LV_I_TIME</p:TIME>
<p:TYPE>p:LV_TYPE</p:TYPE>
<p:prgn_gt_dsind size="1">
<prgn_gt_dsind:prgn_gt_dsind delta="false" locale="" verb="Create" version="0.0.0">
<PART>PART</PART>
<ORGANIZATION>ORGANIZATION</ORGANIZATION>
<DIRECTSHIPINDICATOR>DSIND</DIRECTSHIPINDICATOR>
<EFFECTIVEDATE>EFFECTIVEDATE</EFFECTIVEDATE>
<prgn_gt_dsind:ObjectEventId>prgn_gt_dsind:ObjectEventId</prgn_gt_dsind:ObjectEventId>
</prgn_gt_dsind:prgn_gt_dsind>
</p:prgn_gt_dsind>
<p:prgn_gt_ds_disgrp size="1">
<prgn_gt_ds_disgrp:prgn_gt_ds_disgrp delta="false" locale="" verb="Create" version="0.0.0">
<PART>PART</PART>
<ORGANIZATION>ORGANIZATION</ORGANIZATION>
<DISCOUNTGROUP>DISCOUNTGROUP</DISCOUNTGROUP>
<ENDDATE>ENDDATE</ENDDATE>
<STARTDATE>STARTDATE</STARTDATE>
<prgn_gt_ds_disgrp:ObjectEventId>prgn_gt_ds_disgrp:ObjectEventId</prgn_gt_ds_disgrp:ObjectEventId>
</prgn_gt_ds_disgrp:prgn_gt_ds_disgrp>
</p:prgn_gt_ds_disgrp>
<p:prgn_gt_ds_prices size="1">
<prgn_gt_ds_prices:prgn_gt_ds_prices delta="false" locale="" verb="Create" version="0.0.0">
<PART>PART</PART>
<KSCHL>KSCHL</KSCHL>
<ORGANIZATION>ORGANIZATION</ORGANIZATION>
<ENDDATE>ENDDATE</ENDDATE>
<STARTDATE>STARTDATE</STARTDATE>
<KBETR>KBETR</KBETR>
<prgn_gt_ds_prices:ObjectEventId>prgn_gt_ds_prices:ObjectEventId</prgn_gt_ds_prices:ObjectEventId>
</prgn_gt_ds_prices:prgn_gt_ds_prices>
</p:prgn_gt_ds_prices>
<p:prgn_gt_ds_program size="1">
<prgn_gt_ds_program:prgn_gt_ds_program delta="false" locale="" verb="Create" version="0.0.0">
<PART>PART</PART>
<ORGANIZATION>ORGANIZATION</ORGANIZATION>
<YYMVKE_ADLGRP1>YYMVKE_ADLGRP1</YYMVKE_ADLGRP1>
<EFFECTIVEDATE>EFFECTIVEDATE</EFFECTIVEDATE>
<prgn_gt_ds_program:ObjectEventId>prgn_gt_ds_program:ObjectEventId</prgn_gt_ds_program:ObjectEventId>
</prgn_gt_ds_program:prgn_gt_ds_program>
</p:prgn_gt_ds_program>
<p:prgn_gt_general size="1">
<prgn_gt_general:prgn_gt_general delta="false" locale="" verb="Create" version="0.0.0">
<PART>PART</PART>
<MAKTX>MAKTX</MAKTX>
<REMAN_IND>REMAN_IND</REMAN_IND>
<NORMT>NORMT</NORMT>
<BRGEW>BRGEW</BRGEW>
<NTGEW>NTGEW</NTGEW>
<GEWEI>GEWEI</GEWEI>
<LAENG>LAENG</LAENG>
<BREIT>BREIT</BREIT>
<HOEHE>HOEHE</HOEHE>
<MEABM>MEABM</MEABM>
<UNIT_LTEXT>UNIT_LTEXT</UNIT_LTEXT>
<YYADLGRP4>YYADLGRP4</YYADLGRP4>
<prgn_gt_general:ObjectEventId>prgn_gt_general:ObjectEventId</prgn_gt_general:ObjectEventId>
</prgn_gt_general:prgn_gt_general>
</p:prgn_gt_general>
<p:prgn_gt_rfxp_ovr size="1">
<prgn_gt_rfxp_ovr:prgn_gt_rfxp_ovr delta="false" locale="" verb="Create" version="0.0.0">
<PART>PART</PART>
<ORGANIZATION>ORGANIZATION</ORGANIZATION>
<PLTYP>PLTYP</PLTYP>
<ENDDATE>ENDDATE</ENDDATE>
<STARTDATE>STARTDATE</STARTDATE>
<KBETR>KBETR</KBETR>
<PTEXT>PTEXT</PTEXT>
<prgn_gt_rfxp_ovr:ObjectEventId>prgn_gt_rfxp_ovr:ObjectEventId</prgn_gt_rfxp_ovr:ObjectEventId>
</prgn_gt_rfxp_ovr:prgn_gt_rfxp_ovr>
</p:prgn_gt_rfxp_ovr>
<p:prgn_gt_suplcosts size="1">
<prgn_gt_suplcosts:prgn_gt_suplcosts delta="false" locale="" verb="Create" version="0.0.0">
<ID>ID</ID>
<ORGANIZATION>ORGANIZATION</ORGANIZATION>
<NETPR>NETPR</NETPR>
<WAERS>WAERS</WAERS>
<PRDAT>PRDAT</PRDAT>
<prgn_gt_suplcosts:ObjectEventId>prgn_gt_suplcosts:ObjectEventId</prgn_gt_suplcosts:ObjectEventId>
</prgn_gt_suplcosts:prgn_gt_suplcosts>
</p:prgn_gt_suplcosts>
<p:prgn_gt_suppliers size="1">
<prgn_gt_suppliers:prgn_gt_suppliers delta="false" locale="" verb="Create" version="0.0.0">
<PART>PART</PART>
<ORGANIZATION>ORGANIZATION</ORGANIZATION>
<ID>ID</ID>
<NAME1>NAME1</NAME1>
<prgn_gt_suppliers:ObjectEventId>prgn_gt_suppliers:ObjectEventId</prgn_gt_suppliers:ObjectEventId>
</prgn_gt_suppliers:prgn_gt_suppliers>
</p:prgn_gt_suppliers>
<p:prgn_gt_top_prices size="1">
<prgn_gt_top_prices:prgn_gt_top_prices delta="false" locale="" verb="Create" version="0.0.0">
<PART>PART</PART>
<KSCHL>KSCHL</KSCHL>
<ORGANIZATION>ORGANIZATION</ORGANIZATION>
<ENDDATE>ENDDATE</ENDDATE>
<STARTDATE>STARTDATE</STARTDATE>
<KBETR>KBETR</KBETR>
<prgn_gt_top_prices:ObjectEventId>prgn_gt_top_prices:ObjectEventId</prgn_gt_top_prices:ObjectEventId>
</prgn_gt_top_prices:prgn_gt_top_prices>
</p:prgn_gt_top_prices>
<p:ObjectEventId>p:ObjectEventId</p:ObjectEventId>
</p:prgn_yvol_int2_delta_batch>
prgn_gt_general is the driving section and is the only one which will definitely be there. I need to pull off the information and write it to an XML schema. Here is the output format:
</p:PartClassificationInterface>
<Parts>
<Part> (Occurs 0 to many times)
<PartInfo>
<ProviderCode>ProviderCode</ProviderCode>
<PartNumber>PartNumber</PartNumber>
<Name>Name</Name>
<Description>Description</Description>
<StructureCode>StructureCode</StructureCode>
<Type>Type</Type>
<p:Validity>
<IsActive>IsActive</IsActive>
<ValidFrom>2001-01-01</ValidFrom>
<ValidTo>2001-01-01</ValidTo>
</p:Validity>
<UnitOfMeasurement>UnitOfMeasurement</UnitOfMeasurement>
<IsReturnable>IsReturnable</IsReturnable>
<IsSerialized>IsSerialized</IsSerialized>
<Attributes>
<Attribute>
<Code>Code</Code>
<Name Locale="">Name</Name>
</Attribute>
</Attributes>
</PartInfo>
<SupplierPricing> (Occurs 0 to many times)
<PurchOrg>PurchOrg</PurchOrg>
<CurrencyCode>CurrencyCode</CurrencyCode>
<UnitOfMeasure>UnitOfMeasure</UnitOfMeasure>
<Customer>
<Code>Code</Code>
<Name Locale="">Name</Name>
</Customer>
<Amounts>
<AmountInfo>
<Type>Type</Type>
<CurrencyCode>CurrencyCode</CurrencyCode>
<Amount>0.0</Amount>
</AmountInfo>
</Amounts>
<p:Validity>
<IsActive>IsActive</IsActive>
<ValidFrom>2001-01-01</ValidFrom>
<ValidTo>2001-01-01</ValidTo>
</p:Validity>
<AdditionalInfo>
<Code>Code</Code>
<Name Locale="">Name</Name>
</AdditionalInfo>
</SupplierPricing>
<PDCPrices> (Occurs 0 to many times)
<Type>Type</Type>
<SalesOrg>SalesOrg</SalesOrg>
<p:Validity>
<IsActive>IsActive</IsActive>
<ValidFrom>2001-01-01</ValidFrom>
<ValidTo>2001-01-01</ValidTo>
</p:Validity>
<Amount>0.0</Amount>
</PDCPrices>
<DirectShip> (Occurs 0 to many times)
<SalesOrg>SalesOrg</SalesOrg>
<Indicator>
<ValidFrom>2001-01-01</ValidFrom>
<IsActive>IsActive</IsActive>
</Indicator>
<Program>
<ValidFrom>2001-01-01</ValidFrom>
<Type>Type</Type>
</Program>
<Discount>
<p:Validity>
<IsActive>IsActive</IsActive>
<ValidFrom>2001-01-01</ValidFrom>
<ValidTo>2001-01-01</ValidTo>
</p:Validity>
<Group>Group</Group>
</Discount>
<PriceLevels>
<PriceLevel>
<p:Validity>
<IsActive>IsActive</IsActive>
<ValidFrom>2001-01-01</ValidFrom>
<ValidTo>2001-01-01</ValidTo>
</p:Validity>
<Amount>0.0</Amount>
<Type>Type</Type>
</PriceLevel>
</PriceLevels>
</DirectShip>
<RFXPOverrides> (Occurs 0 to many times)
<SalesOrg>SalesOrg</SalesOrg>
<PriceListType>PriceListType</PriceListType>
<PriceListDescription>PriceListDescription</PriceListDescription>
<Amount>0.0</Amount>
<p:Validity>
<IsActive>IsActive</IsActive>
<ValidFrom>2001-01-01</ValidFrom>
<ValidTo>2001-01-01</ValidTo>
</p:Validity>
</RFXPOverrides>
</Part>
</Parts>
</p:PartClassificationInterface>
My initial thoughts were to assign references to each level of the input. I would do a select from the prgn_gt_general tree and use that PART in the where clause for selects on the other levels of the tree. My fellow developers tell me this will be inefficient and I need to go back to the SAP developer and have him change the input structure to resemble a generic XML structure with PART as the high level and all info for that part under it. Any suggestions would be welcome.
Thanks in advance.
Gale |
|
| Back to top |
|
|
| fjb_saper |
| Posted: Tue Sep 12, 2006 1:06 pm Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20756
Location: LI,NY
|
Forget the suggestion you got.
SAP does not work like that and short from SAP having to entirely rewrite the IDOC it is not going to happen.
What you can do however is at parsing time of the IDOC build your structure in the required way.
Enjoy 
_________________
MQ & Broker admin |
|
| Back to top |
|
|
| Gale |
| Posted: Tue Sep 12, 2006 1:21 pm Post subject: |
|
|
Novice
Joined: 08 Dec 2004
Posts: 12
Location: Portland, OR
|
For this application, the SAP developer is using a custom RFC instead of an IDOC. It would still be a pain for them to change. Please tell me more about changing the structure at parsing time!? I didn't know you could do that! We are using the SAP adapter mySAP.com. I am creating the message set from the SAP RFC structure via ODA business objects.
Thanks! |
|
| Back to top |
|
|
| fjb_saper |
| Posted: Tue Sep 12, 2006 1:26 pm Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20756
Location: LI,NY
|
We are using straight IDOCs
At parsing time the IDOC is parsed as an IDOC.
After that a compute node transforms it into XML.
There you can do whatever transformation you want.../ need.
Enjoy
_________________
MQ & Broker admin |
|
| Back to top |
|
|
| Gale |
| Posted: Tue Sep 12, 2006 1:30 pm Post subject: |
|
|
Novice
Joined: 08 Dec 2004
Posts: 12
Location: Portland, OR
|
| Thanks for the information! Right now I am just trying to design the most efficient way to get the information out of the tree in a logical manner to format the output. Should be easy, but my inexperience is showing! |
|
| Back to top |
|
|
|
|
