4D SQL (v15.6): mix of JOINS + WITH clause + INNER queries on FROM, SELECT

Question

Has anyone here worked with 4D SQL anytime?

Our corporate application it's based on this, and through ODBC conector (I've tried multiple versions of ODBC driver, and now I'm currently using v17 on x64) and I can use a DB manager like DBeaver, to querying the associated DB:

This, is the only configuration I've ended with to correctly achieve the SQL querying throug DBeaver, respecting charsets, connections, etc...

But I've discovered multiple problems with 4D SQL:

1. There is not a "WITH" clause to precreate groups and the SELECT from them...
2. I can't do multiple JOINS combining them: for example, SELECT ... FROM ... INNER JOIN ... ON(...) LEFT JOIN ... ON(...). I can only use INNERs or LEFTs on the same block...
3. Use of inner queries, looks like it's only possible as predicates (on WHERE). But if I want to do a SELECT ... FROM (INNER QUERY) AS t WHERE..., it's not possible.

You can't do a SELECT COUNT(*) FROM (INNER QUERY), for example.

And more like this.

Anyone here on the same situation, and with some solutions?

Answer 1

You didn't mention the version of 4D you are working with. Recent versions, especially v18, have much improved the SQL implementation.

You will also find a deeper bench for help here: https://discuss.4d.com/