very urgent (using join conditions in PI)

[Former Member]

Hi PI gurus.

My scenario is SOAP to JDBC(Portal Webdynpro screen to Oracle database).

I have a requirement to fetch some complex data from the database using complex nested queries.How can i go ahead using join conditions in PI.Here the client is insisiting only on queries and rejected all other methods for solving this like

1)Java proxies

2)stored procedures written in the database system

3)Create a logical view in database system .

The following option is permitted for me by the client to follow

· Using the SQL_QUERY as a query method in the JDBC Receiver Channel in XI

· Trying out JOIN conditions to make multiple query into a single query:- Here need to check the feasibility of the JOIN Condition

Please help me to complete our task.

points will be rewarded for helpful answers.

Thanks & Regards

Veena

[justin_santhanam · Accepted solution]

Veena,

You need to create the target strcuture as

<StatementName>

<anyName action=” SQL_QUERY”>

<access>SQL-String with optional placeholder(s)</access>

</anyName >

</StatementName>

The above structure will fetch the row from JDBC.

Please refer -http://help.sap.com/saphelp_nw70/helpdata/en/2e/96fd3f2d14e869e10000000a155106/content.htm

I think we discussed the same in other thread, isn't it?

raj.

[Former Member · Accepted solution]

Dear Raj,

Thanks for your quick reply,

I have seen this help document.Bit of confusion,Could you Please explain briefly where I can exactly put my query logic.

Your valuable help is appreciated.

Thanks in anticipate.

Kind Regards,

Veena

[justin_santhanam · Accepted solution]

Veena,

Inside the access tag

<access> Place ur query here </access>

raj.

[Former Member · Accepted solution]

Dear Raj,

You mean that shall i select std constant function.and place my query logic under constant function and map this constant function to the access.

please correct me if I am wrong.

Thanks & regards,

Veena.

[justin_santhanam · Accepted solution]

Veena,

Yes. But if the query is two long, then u can go ahead by creating simple UDF which returns the sql query.

something like,

UDF:

return " your query";

raj.

[Former Member · Accepted solution]

Dear Raj,

Could you please tell me briefly how I will assign this nested queries under user defined function.bcz in my case all are nested queries.

Thanks & Regards,

Veena

[justin_santhanam · Accepted solution]

Veena,

Is ur query going to be dynamic or its static. If it's static then no need for any inputs to the UDF. If it's dynamic then u need input to the UDF.

Just copy the SQL query,

create simple UDF with no inputs (in case of static)

write the single line of code as below

return " Paste the query which u've copied" ;

If u don't mind could you reply back the query which u are using?

raj.

[Former Member · Accepted solution]

Dear Raj,

Please find the following one of my nested queries.

1- Requisition Query

SELECT DISTINCT DBNAME.R_RQNMAS.RQN_NO, DBNAME.R_RQNMAS.RQN_TITLE,

DBNAME.R_RQNMAS.STANDARD_TEXT,

DBNAME.R_RQNMAS.ACCT_CODE_ID,

DBNAME.R_RQNMAS.ROS_DATE, DBNAME.R_RQNMAS.RQTR, DBNAME.R_RQNMAS.RQN_DATE,

DBNAME.R_RQNMAS.APPROVE_BY, DBNAME.R_RQNMAS.APPROVE_DATE,

DBNAME.R_RQNMAS.RQN_AMT, DBNAME.R_RQNDET.ITEM_NUM, DBNAME.R_RQNDET.ITEM_CODE,

DBNAME.R_RQNDET.ITEM_DESC,

DBNAME.R_RQNDET.SPARE_PART_DESC,

DBNAME.R_RQNDET.UOM, DBNAME.R_RQNDET.RATE_OR_PRICE, DBNAME.R_RQNDET.QTY_REQUIRED,

DBNAME.R_RQNDET.QTY_REQUESTED, DBNAME.R_RQNDET.ITEM_AMT,

DBNAME.R_RQNDET.STORE_NO, DBNAME.R_RQNDET.REMARKS, DBNAME.R_RQNDET.LOCAL_DESC,

DBNAME.R_RQNMAS.KEY_CODE, DBNAME.R_RQNMAS.RQN_STATUS,

DBNAME.R_FEMAST.FE_KEY

FROM DBNAME.R_RQNDET, DBNAME.R_RQNMAS, DBNAME.R_FEMAST

WHERE (DBNAME.R_RQNMAS.ID=DBNAME.R_RQNDET.RQN_ID)

AND (DBNAME.R_RQNMAS.FE_ID=DBNAME.R_FEMAST.ID(+))

AND (DBNAME.R_RQNMAS.RQN_NO = UPPER(:RQN_NUM))

ORDER BY RQN_NO, ITEM_NUM

2- Acceptable Combination Query

SELECT R_ELECBN.COMBINATION_ID, R_ELECBN.COMBINATION_CODE

FROM DBNAME.R_ELECBN;

3- Requester Query

SELECT R_USERRG.USER_ID, R_USERRG.USER_NAME

FROM DBNAME.R_USERRG;

4- Approval Query

SELECT R_USERRG.USER_ID, R_USERRG.USER_NAME

FROM DBNAME.R_USERRG;

Please help me for further proceedings.

Thanks & Regards,

Veena

[justin_santhanam · Accepted solution]

Veena,

Gr8. Before going ahead lemme ask some questions. So u want to pass 4 different queries to DB is it? What u r going to do once u fetched these records? R u going to use BPM for this scenario?

Could you plz tell the end to end scenario.

raj.

[Former Member · Accepted solution]

Define the data structures (Request & Response)

A JDBC Synchronous scenario, the response message type name is dependent on the request message type name

i.e., if request is

Dear Raj,

This is my scenario SOAP-to-JDBC(syn) with out using BPM's.

4datatypes & message types,

MT_JDBCRequest

--->Statement1

My response msg type

MT_JDBCRequest_response

-

---

>Statement1_response

-

---

>row

twoMessage Interfaces, two mappings (for request & response) and one interface mapping

Configure the Objects In Integration directory.

Define the Web services and save the WSDL file After completion of configuration.

WSDL file is used for the integration of Portal - PI scenario.

Process Flow:

1. User requests for a report from the available list of PSMA reports by providing required set of input parameters.

2. Portal forwards the request with these input parameters to PI using web service model through SOAP protocol and the required results are derived from PI

3. Report is viewed on portal screen.

Thanks & regards,

Veena

[justin_santhanam · Accepted solution]

Veena,

Plz see the below target structure and let me know ur suggestions.

I can say create one Request Data type for accessing the DB with the following structure

1) Create the request structure like

<DT_SQL>

<StatementName1>

<anyName action=” SQL_QUERY”>

<access> First query </access>

</anyName >

</StatementName1>

<StatementName2>

<anyName action=” SQL_QUERY”>

<access> Second query </access>

</anyName >

</StatementName2>

<StatementName3>

<anyName action=” SQL_QUERY”>

<access> Third query </access>

</anyName >

</StatementName3>

<StatementName4>

<anyName action=” SQL_QUERY”>

<access> Fourth query </access>

</anyName >

</StatementName4>

</DT_SQL>

2) Create 4 UDFs for each query. Map each UDF to each Access tag.

3) Create response structure as for example

<DT_SQL_response>

<Statement1_response>

<row>

<field1/>

<field2/>

<field3/>

......

<fieldn/>

</row>

</Statement1_response>

<Statement2_response>

<row>

<field1/>

<field2/>

......

<fieldn/>

</row>

</Statement2_response>

<Statement3_response>

<row>

<field1/>

......

<fieldn/>

</row>

</Statement3_response>

<Statement4_response>

<row>

<field1/>

<field2/>

</row>

</Statement4_response>

</DT_SQL_response>

raj.