Enum in where clause in OQL query

0
Hi Team,Im trying to use the OQL query in Execute OQL statement (Count rows ) in version 11.12.2. Can any one suggest me the query.I tried multiple values of having the where clause for status field, but no luck.Current Query statement is -'SELECT * FROM Example.Customer WHERE Status = "Pending"'Note - In my enum values both the caption adn key are same type.Appreciate your help!Thank you!
asked
3 answers
1

The enum value should be in single quotes. If you're building the query string in the modeler you'll need to escape the single quotes inside the string with, get this, single quotes. So try something like: 

'SELECT * FROM Example.Customer WHERE Status = ''Pending'''

Note that this only contains single quotes.

The executed OQL statement should then be:

SELECT * FROM Example.Customer WHERE Status = 'Pending'


Also if you only want to know how many there are, you may just as well retrieve from database using xpath and do a count. Mendix will automatically optimize this so it only retrieves a count, not a list of objects.

answered
0

Hi Jhansi,

As Martin said,

I tried the same syntax in the OQL playground.

https://service.mendixcloud.com/p/OQL

By using sytax 

SELECT  FirstName FROM Sales.Customer WHERE CustomerType = 'FirstTimer'

and it's wokring there.


can you please check your enum attribute again.

answered
0

Hi Jhansi,


For Mendix 11.12.2, you can filter an Enumeration attribute using the technical name (Name/key) of the enumeration value.


The issue in the query you posted is the use of double quotes around Pending. In OQL, double quotes are used for identifiers, while string literals need to be enclosed in single quotes.


So your query should be:


SELECT *
FROM Example.Customer
WHERE Status = 'Pending'

If you need to filter for multiple enumeration values, you can use IN:


SELECT *
FROM Example.Customer
WHERE Status IN ('Pending', 'Approved', 'Rejected')

Make sure that Pending, Approved, etc. are the Name of the enumeration values in Studio Pro, not just their translated captions. The enumeration value Name is the technical name used by the application.

One version-specific point: the syntax


WHERE Status = Example.CustomerStatus#Pending

is not available in Mendix 11.12.2. Direct enumeration references in OQL were introduced in Mendix 11.13.0.

Therefore, for 11.12.2, I would use:


SELECT *
FROM Example.Customer
WHERE Status = 'Pending'

If this is being used in the Execute OQL Statement / Count Rows action, the same WHERE condition applies; the important part is to use the enumeration's technical value with single quotes.

Hope this helps.


One important thing da: question screenshot-la SELECT * irukku and they say Execute OQL statement (Count rows). If the action itself is specifically expecting a count query rather than a normal result query, then the query may need to be shaped according to that action's expected result. But the enum filtering syntax itself is definitely:


Status = 'Pending'

or


Status IN ('Pending', 'Approved')

—not double quotes, and not Enum#Value on 11.12.2.

answered