on 03-26-2023 3:38 PM
I would like to use the Hierarchy function as a filter.
I have a table with 3 fields (see below). For each value in P_CHAIN I want to compare the fields in the columns. In particular, I want to check if the value (node) in LOOKUP_2 is contained in a hierarchy which has LOOKUP_1 as the top node. I want to eliminate every record where this condition is satisfied.
In my SQL Query I have a WHERE condition which
This is not what happens. When I execute my code, I get an error message: "Feature not supported: correlated GENERIC operators".
As I understand this error message, the line "start where TGT = LOOKUP_1" is the problem. The subquery is correlated because it uses the LOOKUP_1 columnn from the outer query. SAP HANA SQL does not support this.
How can I rewrite this query?
Any help would be appreciated.
LT_OUTTAB = select P_CHAIN
, LOOKUP_1
, LOOKUP_2
from :TABLE_WITH_3_FIELDS
where LOOKUP_2 not in ( select distinct NODE_ID from hierarchy
( source
( select SRC as node_id
, TGT as parent_id
from RSBKDTP
)
start where TGT = LOOKUP_1
)
)
;
Hi,
Not being familiar with the SQL syntax you are using, perhaps this annot be done, but here it goes:
SELECT -whatever-
FROM (-your query above, returning the unfiltered result-) X
WHERE -filter logic here-
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Martin,
Taking another look at your question, I realize that I suggested a solution to a nonexistent problem. Like I said, I am not familiar with the specific syntax you are using. Rereading your question, I am getting the impression that you are trying to make your solution work, rather than finding a solution to the actual problem.
Can you please tell me a little more about the example table? P_CHAIN does not appear to be relevant. What is the meaning of the highlighted fields in LOOKUP_1 and LOOKUP_2? Are you looking to get a result that will only show records that have 'C' in either column, or vice versa, or what?
Regards,
Johan
User | Count |
---|---|
79 | |
11 | |
10 | |
8 | |
8 | |
6 | |
6 | |
6 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.