Custom SQL and CTE

[k2R400]

Hello,

I need to use a CTE (Common Table Expression) in a custom SQL but it doesn't work.

ex :
It work fine with this Custom SQL : Select * from employee
Don't work with this Custom SQL : With T1 as (Select * from employee) Select * from T1

any idea ?

Regards

[Scott Klement]

That's correct, you cannot use CTEs in "custom sql" for a grid. This is because the system does not provide a way for us to get an accurate count of rows in the result set when you use a CTE, and this is manditory in a database-driven grid, it is the only way to make scrolling work properly.

There are three possible solutions:

1) You can create a SQL view using your statement with the CTE. Then, a simple select statement over the view from the widget will give the same results.

2) Instead of letting Profound UI load the grid automatically, you can run the CTE in your RPG program and load the grid using traditional RPG opcodes for writing a subfile.

3) You can load the grid using the "data url" property, and write a web service that runs the SQL statement and returns the rows.

[k2R400]

OK thank you