JDBC QUERY error

[Former Member]

Dear Experts,

We are having RFC to JDBC syn interface in that we are using query to get the data from Oracle databse.

This is the query  " :SELECT Row_index,Plant,PostingDate,DocumentDate,MovementType,MaterialNum,Quantity,UOM,CostCenter,MaterialDocNumber,MaterialDocYear,Status,Message,UserName FROM HIR_Reports1.dbo.HIE_POST_SAP_TRANS where MaterialDocNumber is NULL and postingdate='2015-09-07 00:00:00.000'"

This query we are using in SAP PI mapping.

Problem is if we give any date in ECC to get the data but PI sending only one date which we are using in Query.

Can you please help me to resolve this.

cheers

[Former Member]

Query is below-

SELECT Row_index,Plant,PostingDate,DocumentDate,MovementType,MaterialNum,Quantity,UOM,CostCenter,MaterialDocNumber,MaterialDocYear,Status,Message,UserName FROM HIR_Reports1.dbo.HIE_POST_SAP_TRANS where MaterialDocNumber is NULL and postingdate='2015-09-07 00:00:00.000'

Every time we are changing the date in PI  for testing but it should pass from ECC to significance.

Any help to write a UDF or how we can resolve it?

Cheers

[Former Member]

You can try use concat function - const (query concat with single quote) as below - you may need to use date transformation function to convert the Ecc date to correct format too.

[Former Member]

Dear Ganesh,

Thanks for your help.

I have used given format of mapping but I am getting below mentioned error.

I think problem with the query we are giving date in query as well and now we are passing date as well.

Below is the query-

SELECT Row_index,Plant,PostingDate,DocumentDate,MovementType,MaterialNum,Quantity,UOM,CostCenter,MaterialDocNumber,MaterialDocYear,Status,Message,UserName FROM HIR_Reports1.dbo.HIE_POST_SAP_TRANS where MaterialDocNumber is NULL and postingdate='2015-08-08 00:00:00.000'

Can you help me to complete this.

Cheers

[Former Member]

Dear Ganesh,

Please ignore my previous mail.

It's working and thanks for your help.

[Former Member]

Gald to know - please close the thread and mark it as resolved

[Former Member]

Dear Ganesh,

Again client needs 2015-09-07 00:00:00.000 .(HH:MM:SS:MLS).

Before we are sending 2015-09-07 but now we need to pass HH:MM:SS.MLS also.

So can you please let me know how we can resolve?

Cheers

[Former Member]

as i said earlier you can use datetrans function and covert to required format

refer thread -

Date conversion in PI Mapping | SCN

[Former Member]

HI Ganesh,

Thanks for your quick response.

I have gone through the data transaction but we dont milliseconds option.

Cheers

[Former Member]

can you try this

[Former Member]

Believe you are using UDF to form the query dynamically? if so make sure to pass ecc date as parameter with all values in queue.

[Former Member]

refer thread

https://scn.sap.com/thread/3391379

[Former Member]

Thanks for your reply..

The querying is correct but in that query we are passing only one date value but we should pass all the date values which we we give in ecc.

we are not using UDF hear.. can you please let me know how we can pass ecc date value to database

cheers

[Former Member]

kindly share screenshot of how you are currently handling the query..

[Former Member]

Thanks Ganesh for your reply.