Using the SQL Hierarchy function as a filter

[martin_chambers]

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

1. creates the hierarchy with LOOKUP_1 as top node (start where TGT = LOOKUP_1)from the table RSBKDTP
2. uses the result to create a view (select distinct NODE_ID from hierarchy) for the field NODE_ID
3. checks if LOOKUP_2 is in this view or notThis is my table with the 3 fields. Because the node C is contained in the hierarchy with the top node B, I want to remove all records with the value C.

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
                                          )
                                   )
;

[Johan_H]

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-

[martin_chambers]

Hi Johan
Thank you for your idea. Unfortunately, I have not understood your reply. Could you add more details? A fully funcitonal code would be helpful.

[Johan_H]

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