Select query on key field for different time format

[Former Member]

Hello Experts,

I am working in Sender ABAP proxy <===> SAP PI 7.31 JAVA <===> Oracle database.

Oracle Database stores all records of material sold where the date is maintained in 01/15/2010 01:48:20 which has date and time format.

In ECC , I will be sending a proxy request with system date as the only field and this would be key field to select all the material records sold for 01/15/2010 in Oracle Database i.e. Select statement.As we can observe that the system data of ECC does not have time stamp.

I am clueless how to get this done in Message Mapping or in some way? If I maintain just the date stamp of ECC as the key field, would I be

able to fetch all the records from Database?

Regards

Rebecca

[Harish · Accepted solution]

Hi Rebecca,

you can use the select statement with LIKE operator to retrieve the data.

Select * from Table where date LIKE 'mm/DD/YYYY%'

regards,

Harish

[Former Member · Accepted solution]

Dear Harish,

You almost saved my whole day.

But my scenario is with receiver Oracle Database.. I hope I can not write the select statement in receiver Cc.

Where I can maintain mm/DD/YY% in receiver Cc ;(.. I assume I have to maintain these values in Message Mapping .. Please confirm if I am deviating from your assumptions. Could you elaborate more?

Regards

Rebecca

[former_member184720 · Accepted solution]

Modify your JDBC receiver structure and add another attribute under key field "compareOperation" and map the value with the key word "LIKE"

JDBC Structure :

Refer to  "Attributes in the <key> Elements " section :

https://help.sap.com/saphelp_nw04/helpdata/en/2e/96fd3f2d14e869e10000000a155106/content.htm

[Former Member · Accepted solution]

Dear Hareesh and Harish,

Ooopps the names are confusing. but very close..

Yes I changed my receiver structure and added <compareOperation> under the key field Material Number. In the Message Mapping , I assigned a constant EQ to <compareOperation>.

Now tested the proxy will value of Material with no 100 , expecting a response from database for Material 100. But getting error as below.

xmlns:SOAP="http://schemas.xmlsoap.org/soap/envelope/">

<SAP:Category>XIProtocol</SAP:Category>

<SAP:Code area="PARSING">GENERAL</SAP:Code>

<SAP:P1 />

<SAP:P2 />

<SAP:P3 />

<SAP:P4 />

<SAP:AdditionalText />

<SAP:Stack>com.sap.engine.interfaces.messaging.api.exception.MessagingException: com.sap.engine.interfaces.messaging.api.exception.MessageExpiredException: Message 53cc7fab-e712-0b20-e100-8000ac10134c(OUTBOUND) expired. at com.sap.aii.adapter.soap.web.SOAPHandler.processSOAPtoXMB(SOAPHandler.java:746) at com.sap.aii.adapter.soap.web.MessageServlet.doPost(MessageServlet.java:505) at

I am not sure whether there was any response from database.

I am close to end of this thread..

Please guide.

Regards

Rebecca

[Former Member · Accepted solution]

Hi Rebbaca,

If you are only considered with the timestamp format, I would suggest an alternative.

After getting the date from ECC, concatenate the date with the timestamp using the current date function and pass the value to the database. Now, Database system will be able to accept the data.

Regards,

[former_member184720 · Accepted solution]

Where do you see this error? in ECC?

What is the message""53cc7fab-e712-0b20-e100-8000ac10134c" status in PI adapter engine? Please check in Runtime workbench. You should be able to get additional details.

[Former Member · Accepted solution]

Dear Hareesh,

Reproduction of error.

1. Test the message payload in Tx SPROXY in ECC at Request tab

2. After 5-6 minutes, a pop-up message comes with PARSING error. Normally the response tab should be there ..:(

Now I checked at the following places..

3. Tx SXMB_MONI of ECC, There are 2 messages with system error.

4. The 1st message i.e. request message shows the error as mentioned earlier.

com.sap.aii.adapter.soap.web.SOAPHandler.processSOAPtoXMB(SOAPHandler.java:746) at com.sap.aii.adapter.soap.web.MessageServlet.doPost(MessageServlet.java:505) at

5. The 2nd message which should be response doesn't have the any payload.

Now to check the Adapter and Message status I checked in NWA -> Message Monitoring and Channel Monitoring.

6.The message monitoring shows the same error with only 1 message which has been cancelled due to message expired.

7. There is no response message in NWA -> Message Monitoring.

8. SOAP channel shows error with message expired.

Note : I have applied enough time out values in receiver JDBC Cc as mentioned in SAP note.

Regards

Rebecca

Regards

Rebecca