Issue with OQL case Statement

0
Hi team,   I am trying to created a data set to show data in a pie chart and I am using a OQL widget for that. But I am getting unusual error while executing microflow. I am using mendix default Db and reading data from a persistant table. Below is the OQL Statement: SELECT   A/AppName as AppName,   A/Function as Function,   (case when A/Status = ''Green'' then ''Up'' else ''Down'' end) as AppStatus ,  Count(*) as StatusCount   FROM Dashboard.APP_STATUS As A   where A/AppName = 'ETD'   GROUP BY A/AppName, A/Function, A/Status Error Message: Caused by: java.sql.SQLSyntaxErrorException: data type cast needed for parameter or null literal in statement [SELECT "A"."appname" AS "AppName",  "A"."function" AS "Function",  CASE WHEN "A"."status" = ? THEN ? ELSE ? END AS "AppStatus",  COUNT(*) AS "StatusCount"  FROM "dashboard$app_status" "A"  WHERE "A"."appname" = ?  GROUP BY "A"."appname",  "A"."function",  "A"."status"]     at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)     at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)     at org.hsqldb.jdbc.JDBCPreparedStatement.<init>(Unknown Source)     at org.hsqldb.jdbc.JDBCConnection.prepareStatement(Unknown Source)   However when I modify the OQL query as shown below, it runs fines also gives the result as according this query. SELECT   A/AppName as AppName,   A/Function as Function,   A/Status  as AppStatus ,  Count(*) as StatusCount   FROM Dashboard.APP_STATUS As A   where A/AppName = 'ETD'   GROUP BY A/AppName, A/Function, A/Status   Not Sure what is causing the issue. Any suggestion would be appreciated.   Also would like to know if it is possible to write sql queries to read data from non persistant tables like we do for persistant tables using OQL.
asked
4 answers
2

This should solve your issue. You need to cast the data type:-

 

SELECT

 A/AppName as AppName,

 A/Function as Function,

 case

        when A/Status = ''Green'' then cast(''Up'' as String)

        else cast(''Down'' as String) end as AppStatus,

 Count(*) as StatusCount

 FROM Dashboard.APP_STATUS As A

 where A/AppName = 'ETD'

 GROUP BY A/AppName, A/Function, A/Status

 

answered
0

Hi, 

Did you try changing the double quotes in the CASE expression to single quotes?

Regarding the other question, Non Persistent Entity objects are kept in the memory of the client so they are not on the database server. For more information: https://docs.mendix.com/refguide/transient-objects-garbage-collecting.

Cheers,

Jeffrey  

answered
0

Your mishap is in the last  GROUP BY A/AppName, A/Function, A/Status

The last name should be AppStatus since that is what you aliased it to

Btw. Just to show the single quotes, on https://mydemoversion8-sandbox.mxapps.io/p/OQL this works fine:

SELECT ID, FirstName, LastName,
 (case when C/FirstName = 'Savitha' then 'Up' else 'Down' end) as AppStatus
 FROM Sales.Customer as C WHERE Name = 'Swathi'

 

*Editted*

Just now noticed: Your doublequotes around Green and Up and Down are actually two single quote’s every time. That won’t get understood. Change those.

Also try, just to experiment and get to working oql’s:

SELECT (case when A/Status = 'Green' then 'Up' else 'Down' end) as AppStatus 
 FROM Dashboard.APP_STATUS As A 
 GROUP BY AppStatus

if need be followed by LIMIT 5

answered
0

I tried with single quoted but getting below error . This time I tried even without case statement.

 

answered