how to take single structure , two tables are coming from source(data base)

[ravi_reddy1]

Hi  experts,

i am working on one interface that is JDBC to proxy scenario.........

My requirement is  suppose two tables  TABLE A, TABLE B  are coming from oracle database.TABLE A contains 10 fields .........and TABLE B contains 6 fileds..........in that 3 fields are common............(suppose  TESTID, CNTDESC,TIME)

for these 2 tables i need to take single structure................

see the flow

TABLE A-------------------------->SINLE                    ------------------------>    TABLE A                                           SRTUCTURE                                                  ECC                                             IN                                             PI TABLE B-------------------------->                              --------------------------->  TABLE B ----------------                          ----------------------                                      --------------------- ORACLE DB                                PI                                                  ECC

MY QUESTION IS HOW TO TAKE SINGLE STRUCTURE (IN DATA TYPE)...................AND  WAHTEVER DATA IS COMING FROM TABLE A THAT DATA IS GOING TO TABLE A IN ECC AND WHATEVER DATA IS COMING FROM TABLE B THAT SAME DATA IS GOING TO ECC............

TABLE A AND TABLE B CONTAINS 3 UNIQUE FIELDS........

please help me in this...............<Removed by Moderator>

<Do not write in Upper case. You are asking for help and not shouting for it>

<Please read Rules of Engagement>

Thanks & Regards

E. Ravi Chandra reddy

Message was edited by: Prateek Raj Srivastava

[Harish · Accepted solution]

Hi Ravi,

You can perform the inner join on two table in select statement.

you can create the data type with 13 elements (10 from table A and 3 table B as 3 of table is already covered in table A). the message or data comes from database is in recordset.

you can write the query

select A.1, A.2....A.10,B.4, B.5, B.6 from A inner join A on A.1 =B.1

Please test your query on the database. please also refer the below discussion

regards,

Harish

[ravi_reddy1 · Accepted solution]

Hi Harish,

thank you for  yours replay,

But it gives error in Message mapping like MappingException.

please see the error

CutStitchandPackingQAdata/OM_MESDB_2_ECC_CutStitchandPackingQAdata" failed to execute: MappingException: Mapping failed in runtimeRuntime Exception when executing application mapping program com/sap/xi/tf/_MM_MESDB_2_ECC_CutStitchandPackingQAdata_; Details: com.sap.aii.mappingtool.tf7.IllegalInstanceException; Cannot create target element /ns1:MT_CutStitchandPackingQAdata_ECC/Header. Values missing in queue context. Target XSD requires a value for this element, but the target-field mapping does not create one. Check whether the XML instance is valid for the source XSD, and whether the target-field mapping fulfils the requirement of the target XSD, ApplicationRuntimeException: Runtime Exception when executing application mapping program com/sap/xi/tf/_MM_MESDB_2_ECC_CutStitchandPackingQAdata_; Details: com.sap.aii.mappingtool.tf7.IllegalInstanceException; Cannot create target element /ns1:MT_CutStitchandPackingQAdata_ECC/Header. Values missing in queue context. Target XSD requires a value for this element, but the target-field mapping does not create one. Check whether the XML instance is valid for the source XSD, and whether the target-field mapping fulfils the requirement of the target XSD, IllegalInstanceException: Cannot create target element /ns1:MT_CutStitchandPackingQAdata_ECC/Header. Values missing in queue context. Target XSD requires a value for this element, but the target-field mapping does not create one. Check whether the XML instance is valid for the source XSD, and whether the target-field mapping fulfils the requirement of the target XSD

please give me any needful information for this...

Thanks & Regards

E. Ravichandra Reddy

[Harish · Accepted solution]

Hi Ravi,

ttrade the message is failed in mapping, can you check the input XML and mapping of header node. Please provide the input XML for mor inputs.

regards,

Harish

[former_member191435]

Hi RAVI,

It is better to use stored Procedure rather than joining 2 tables.

Thanks,

Sreenivas

[Former Member]

Hi Ravi

You can also refer the below links  which talks about using views and stored procedure approach.

Sender JDBC Adapter with Mutiple SQLDatabase T... | SCN

Stored procedure would be a good option here you can check below links for help to evaluate this option

Advantages-Disadvantages of Stored Procedure in JDBC Scenario - Process Integration - SCN Wiki