Sql - Exists With queries in Caché?
Hi guys.
Do You known If Caché has suport to Common Table Expressions (With queries)
By example: https://www.postgresql.org/docs/9.6/static/queries-with.html
Discussion (6)0
Log in or sign up to continue
https://test.community.intersystems.com/post/sql-exists-queries-cach%C3%A9
InterSystems still support only SQL-92. While
WITHappeared only in SQL-99. So, Caché doesn't support it.Hi Dmitry. Trank you to your answer.
Hmm, ok, dont have WITH, but there any caché's implementation to recursive queries in SQL? My trouble is about hierarchy, I have a table where each record points to parent's id...
Caché does NOT support SQL-92 in its full implementation, so please refrain from saying so. Example:
SELECT 3+3*3
Result: 18
Clearly, by not following order of operations we are violating the standard. Moreover, despite not conforming perfectly to SQL-92 we do support plenty of extensions, such as Table-Valued Functions.
That said, Caché does not support WITH in the way you describe. However, you can accomplish the same thing by using subqueries, VIEWs, or multiple queries. You can also JOIN each record with its parent and filter on that. If you give some more information I'd be happy to point you in the right direction.
Hi Kyle. So...
I have a hierarchy, a tree. Each node is a row.
To simplify, imagine only 2 fields:
NodeId | ParentId
3 | 2
2 | 1
1 | 0
So, each node knows your father. But I need answer things like:
All nodes that are descendants from node x
OK, I can make a object script method or I can use severals "self inner Join", but I would like to use a more elegant way
I think the best way to do this would be to write something in COS, write a class query with it, and use that as a Table Valued Function. While there might be a relational way to do this, it seems like it'd be messy.
Yes, for now is the best option :(
Ok, Kyle, thanks very much for your help :)