Records based on time from Oracle database

[Former Member]

Dear Experts,

I need some valuable suggestion from your end.

This a synchronous scenario from Sender ECC to Oracle Database.

In Oracle Database, the swiping entry time of employees is stored in format as 03/11/2012 08:10:22 which would be the primary key while making ECC request.

I have to fetch all the records along with other 3 output fields for the date between 03/11/2012 00:00:01 to 04/11/2012 00:00:01.

The Oracle view has following column EMPID,CITY,TIME,DEVICE

1. How would be ECC and JDBC request structure so that the start time and end time are captured?

2.How to map the ECC and Oracle Database request structure where the SQL query would look something below.

select EMPID,CITY,TIME,DEVICE  from time_details where

TIME > TO_DATE('03/11/2012 00:00:01', 'MM/DD/YYYY HH24:MI:SS')

and

TIME < TO_DATE('04/11/2012 00:00:01', 'MM/DD/YYYY HH24:MI:SS')

3. I tried to fetch the records by putting the WILDCARD in the date input value, I am receiving the response but with no payload.Is there any other design where I can fetch all the records for 03/11/2012 from 0 hours to 24 hours.

Quick help is highly appreciated.

Regards

Rebecca

[Former Member · Accepted solution]

Hi,

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

You can form the sql query by using place holders like below in your request mapping (ECC to Oracle)

<StatementName6>

<anyName action=” SQL_QUERY” | “SQL_DML”>

<access>select EMPID,CITY,TIME,DEVICE  from time_details where TIME > TO_DATE('$placeholder1$', 'MM/DD/YYYY HH24:MI:SS') and TIME < TO_DATE('$placeholder2$', 'MM/DD/YYYY HH24:MI:SS')</access>

<key>

  <placeholder1>value1</placeholder1>

  <placeholder2>value2<placeholder2>   

</key>

</anyName >

  </StatementName6>

Regards,

Praveen

[Former Member · Accepted solution]

Hi Praveen,

Genius..

You saved my whole day by giving this input..

I would need your help to in the Access block specially to form the JDBC request structure from where clause. Let me first try to form the Message Type.

<access>select EMPID,CITY,TIME,DEVICE  from time_details where TIME > TO_DATE('$placeholder1$', 'MM/DD/YYYY HH24:MI:SS') and TIME < TO_DATE('$placeholder2$', 'MM/DD/YYYY HH24:MI:SS')</access>

1. I assume for TO_DATE, I have to check in the DateTransformation Mapping function whether HH24:MI:SS is available or not.

Thanks once again for your input.

Regards

Rebecca

[Former Member · Accepted solution]

Dear Praveen,

What to say... and what a coincidence.

I did the configuration as per your exact input...and it worked.

I wanted to give you more points.. in vain..

I assume since our forum is very active, we are helping each other.

Kudos to every expert members.

Regards

Rebecca