cancel
Showing results for 
Search instead for 
Did you mean: 

Datetime parameter in KPI Delete query

darshansheth
Explorer
0 Kudos

Hi Experts,

We have KPI objects in which we need to retain data for some period of time lets say, I want to retain data for the last 24 hrs and delete the remaining KPI data. I have prepared KPI query in delete mode. In Filter Expression field of the query i am passing the expression as

"[TimeStamp] <= to_date ( '01/12/2015' , 'MM/DD/YYYY') "

then i am getting the following error:

SQL error has occurred on query The SQL statement "DELETE FROM MDO11061 WHERE FIELD2 <= to_date ( '01/12/2015' , 'MM/DD/YYYY')" contains the syntax error[s]: - 1:46 - SQL syntax error: the token "(" was not expected here

Is it possible to perform such function in the Delete mode of KPI query. If yes, what am I doing incorrectly ? Has anyone faced similar issue earlier.

I would highly appreciate any hint which could help in solving this problem.

System Information:

NW 7.31 SP 10

Oracle 11.2.0.4

MII 14.0 SP5 patch 7

Regards,
Darshan Sheth

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Hi Darshan,

Try using [Timestamp] > [Param.1]. In the parameters tab specify the parameter and type as timestamp.

Regards,

Anushree

darshansheth
Explorer
0 Kudos

Hi Anushree,

I modified the query per what you have mentioned. On execution I get the message 'Command Query Successful'. If I check corresponding MDO table in the netweaver schema then it still shows those records in table. I tried to delete records with another parameter along with Time stamp. It gives success message but does not delete records on the database.

I also tried to give another condition without Timestamp, there too i got the success message but no records were deleted from the database.

Do we need to perform any other steps to get the records deleted or is it know tool bug.

Regards,

Darshan Sheth

swaroop_anasane
Active Contributor
0 Kudos

Hi Darshan,

Can you share a screen shot of your query. Something is messed up with the syntax only. It's probably taking column as field.1 and as it's not getting the field, it is ignoring the delete op.

Thanks,

Swaroop

Former Member
0 Kudos

Hi darshan,

Kindly share the screenshot of the query.

Regards,

Anushree

darshansheth
Explorer
0 Kudos

It is working fine. Was checking in the wrong table on the netweaver DD.

Answers (1)

Answers (1)

swaroop_anasane
Active Contributor
0 Kudos

Hi Darshan,

Try removing double quotes. Refer below document by Rutika:

Also, check backend of you MII System. Is it Oracle or SQL?

Thanks,
Swaroop

darshansheth
Explorer
0 Kudos

Swaroop,

Double quotes are not in query i have put it here just for identification. DB is Oracle.

Regards,

Darshan Sheth