can I create common table expression (CTE) in custom query?

Q&A
1

Hi

I wana use a complex query. I checked it on my databas on the server and it worked on the postgreSQL, but when I add it in the custom query on the BDM in Bonita Studio, it has an error:

30-Nov-2022 11:32:39.009 ERROR [http-nio-8080-exec-4] org.hibernate.internal.SessionFactoryImpl.<init> HHH000177: Error in named query: MyTable.findQuery
    org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected token: with near line 1, column 1 [with myName as

It is possible to write complex query like this:

with myName as (
SELECT m.field, ....
         sum(1) OVER () as total
    FROM MyTable m
    )

select * from myName

2

Hi nasrin,

Custom BDM queries in studio are written JPQL, not SQL and also they may return same atomic value (Long for a count), or the BDM object, Single or multiple

In your example, it seems to be a simple count query..

3

Thanks for your answer

Honestly my real query is much more complicated than it and I have to use sub queries and with clause.

4

in this case, you can use a database connector in read only way to use native SQL 

5

Many thanks Laurent

and the only way to use this query in an application page is using Rest API?

6

you can also use the SQL connector on enter and get the result