Filtering a Select MDO Query based on Datetime values

[former_member1231563]

Hi All,

I am using SAP MII 14.0 version & working on MDO's

I am writing a select MDO query to fetch data from a Custom Persistant MDO

This MDO has five fields out of which 4 are string variables & one is a Date time variable

Now, when i filter the Query based on Date time, Its gives me issues

I am giving the condition as below in the filter Expression tab:

[INSERT_DATE] >='[Param.1]' AND [INSERT_DATE]<='[Param.2]'  where INSERT_DATE is date time variable

In the Parameters of the Template i give the values for the two parameters say 03/01/2015 00:00:00 and 03/10/2015 00:00:00 as i used the same format to populate the MDO

When i execute the Query, it gives me the below error:

java.sql.SQLException: Error while processing the template TMP452bc540-e1ee-11e4-83f1-d0b90a3b5f3b - 1:69 - type check error: the left hand side >>INSERT_DATE.< (TIMESTAMP) and the right hand side >>'?'<< (CHAR) of a comparison operator are not comparable

- 1:86 - type check error: the left hand side >>INSERT_DATE.< (TIMESTAMP) and the right hand side >>'?'<< (CHAR) of a comparison operator are not comparable

Although the same process gives proper results for a MII SQL Query template

Also, i tried to remove the Quotes in the Filtering Condition, then it gives the below error

java.lang.StringIndexOutOfBoundsException: String index out of range: -1

Could any one kindly help me understand this or give me a solution???

Thanks!!

Best Regards

Praveen

[raj44 · Accepted solution]

Hi Praveen,

Please check if you've enabled the TYPE PARAMETERS- you can check that and set the parameter type as timestamp for both param1 and param2 and then you can test. You need to set the parameter type.

Hope this helps.

Regards

Rajeev

[swaroop_anasane · Accepted solution]

Also, you need to remove the quotes if you are using typed params. And yes, as Rajeev suggested, typed params should work here.

[former_member1231563 · Accepted solution]

Hello Rajeev,

Thanks for the help..

Setting it to time stamp parameter did worked for me but i don't understand why it does not work when we are not using Typed Parameters as for a SQL Query template it works either ways

Could you explain me the difference?

Best Regards

Praveen

[raj44 · Accepted solution]

Hi Praveen,

I don't know exactly the reason why it doesn't work for MDO but it works for SQL; it could be that this requirement is specific to a MDO.

Regards

Rajeev

[swaroop_anasane · Accepted solution]

Hi Praveen,

This reminds me of, MDOs have been designed on open SQL concept, so it should take commands irrespective of the DB type.

In such scenarios, it's better to channel the complex parameters in a standard way.

Consider a scenario where you develop a product using MII that utilizes MDOs as well. Your system has SQL db and you have two clients, one uses SQL and another uses Oracle, so the same product won't be able to work for both if you supply either of Cast(dateString as Datetime) or to_date(dateString,<format>). In such scenarios, using typed parameters would be apt.

For SQL, it's very much, objects created for in a specific type of DB and hence both the ways are permissible.

This probably is a reason. Hope it helps.

Thanks,

Swaroop

[former_member1231563 · Accepted solution]

Hey Swaroop & Rajiv.....Thanks for the reply guys!!!

Yeah Swaroop that is a valid point which you made

Best Regards

Praveen