MDO Query Action Block In MII Transaction

[raj44]

Hi Experts,

I am using a MDO query action block in a transaction. I am trying to select view columns from a MDO for which I've written the query in Query tab of Configure Links. However, on running the transaction I get the foll. error-

• [ERROR] [MDO_Query_0]com.sap.xmii.Illuminator.logging.LHException: com.sap.xmii.mdo.core.MDORuntimeException: No attributes were defined for this query

I have configured the Query, MDO, Mode and Attribute Values.

Foll. is my query:

"Select count(*)  from  [MDO_NAME]

where

[COLNAME] like  '[AttributeValue.1]'  AND

CONVERT(DATETIME,[COLNAME],110) >=CONVERT(DATETIME,'[AttributeValue.2]',110) AND

CONVERT(DATETIME,[COLNAME],110) <=CONVERT(DATETIME,'[AttributeValue.3]' ,110)AND

[COLNAME] like '[AttributeValue.4]'

AND [COLNAME] like '[AttributeValue.5]'"

I also tried to use a simple select statement as select column name from mdo name- this also gave me the same error.

Is there a syntax issue wrt MDO? Also, can we use the stringif function in query tab of MDO configure links?

Thanks

Rajeev

[swaroop_anasane · Accepted solution]

Hi Rajeev,

You should update following links:

Attributes with "ColumnName" and Mode with "Select".

Leave the mode blank in template, just select the source. No query is required  to be supplied in this case.

Only this two properties along with parameters for filter would be enough to run your query.

Thanks,

Swaroop

[raj44 · Accepted solution]

Hi Swaroop,

If I want to just get the count of records how can I do that? Also, for all columns that I need to display must be included in Attributes with comma?

Thanks

Rajeev

[swaroop_anasane · Accepted solution]

For that, PFB:

Comma sep values in [], count(*) and GroupingExp have to be given.

Note: Only count(*) and count(DISTINCT ...) are allowed.

Regards,

Swaroop

[raj44 · Accepted solution]

Thanks Swaroop, however the count gives all the records in that MDO. I've a condition for which I've used stringif function in query part of MDO link...and I need the count as per the condition:-

elect count(*)  from  [MDO_NAME]

where

[COLNAME] like  '[AttributeValue.1]'  AND

CONVERT(DATETIME,[COLNAME],110) >=CONVERT(DATETIME,'[AttributeValue.2]',110) AND

CONVERT(DATETIME,[COLNAME],110) <=CONVERT(DATETIME,'[AttributeValue.3]' ,110)AND

[COLNAME] like '[AttributeValue.4]'

AND [COLNAME] like '[AttributeValue.5]'

Or is there another alternative to specify conditions elsewhere in the MdO link actions and get the count as per the query condition?

Thanks

Rajeev

[swaroop_anasane · Accepted solution]

Hi Rajeev,

If we undersand the concept, MII works in a standard way. So the way it worked for GroupingExpr, it should work for FilterExpr as well.

You can embed [Param.n] in it the way we do for other QTs.

Thanks,

Swaroop

[raj44 · Accepted solution]

Hi Swaroop,

Thanks. I have configured my mdo action block like:-

mode-"Select"

attributes-"count(*)"

mdo-path of mdo

filterexpr- stringif(condition,

"[DATE_TIME] >='%#Transaction.StartDateTime#%' AND

[DATE_TIME]<='%#Transaction.EndDateTime#%'",

"[DATE_TIME] >='%#Transaction.StartDateTime#%' AND

[DATE_TIME]<='%#Transaction.EndDateTime#%'")

On execution, I get the foll error:-

3:1 - type check error: the left hand side >>DATE_TIME.< (TIMESTAMP) and the right hand side >>'%2015-04-21T09:58:14%'<< (CHAR) of a comparison operator are not comparable

Startdatetime and ENDdatetime are both transaction variables of datetime type.

Is there a syntax issue wrt to above(% wildcard or etc.)? How can I handle such in MDOs?

Thanks

Rajeev

[swaroop_anasane · Accepted solution]

If it's date time comparison, you have to mark Typed Parameter property as true.

It should work then.

I could see your comment on one of the previous discussions:

Check my comment on this one.

Thanks,

Swaroop

[raj44 · Accepted solution]

Hi, I am not using a predefined query- I am just using the mdo action block. As for the UseTypedParams, it was set to True and I've set it to "True" again explicitly. Still the problem persists.

[swaroop_anasane · Accepted solution]

Yah I know.

Please note you need to define respective Params and paramtypes as well when you mark UseTypedParams and use the same in filter condition. Simply, setting UseTypedParams would obviously not work. PFB the screeshot:

When I say like other QTs, I meant the general mapping and all works the same way.

You cannot pass the transaction properties directly when you are using typed params. This has to go via params only.

Thanks,

Swaroop

[raj44 · Accepted solution]

Hi Swaroop,

I have specified the params like you mentioned:

But then I get the foll error:

• [ERROR] com.sap.xmii.Illuminator.logging.LHException: java.lang.StringIndexOutOfBoundsException: String index out of range: -1

[swaroop_anasane · Accepted solution]

Hi Rajeev,

This comes when the query does not find any data related to the filter applied. This means, syntactically the query is good to go now.

For your case, make sure you pass date in 'mm/dd/YYYY hh:MM:ss' format. MDO doesn't compare xml date format. XML date is the reason for such error.

Best Regards,

Swaroop

[raj44 · Accepted solution]

Thanks Swaroop.

[swaroop_anasane · Accepted solution]

Always welcome .

-Swaroop

[swaroop_anasane]

Hi Rajeev,

I suppose your query has been solved. Kindly close the thread by marking this as answered.

Thanks,

Swaroop