981094 wrote:It's impossible to know even which columns are NULL.
SELECT COUNT (sub_menu_id)
tags (section 9). If you want to restrict a CONNECT BY query to LEVEL<x, then just include that condition in the CONNECT BY clause:
You could also put that condition in a WHERE clause, to discard the rows after they were found. But it's more efficient to put conditions like that in the CONNECT BY clause, so the unwanted rows are not found in the first place. Your CONNECT BY query does not consider whether the sub_menu_ids found were distinct or not. If you're trying to find an alternative query that does not use CONNECT BY, why are you including DISTINCT when that wasn't part of the original query? In the UNION query you posted, the first branch (before the keyword UNION) corresponds to LEVEL=1 of the CONNECT BY query, but the second branch (after the keyword UNION) corresponds to what would be LEVEL=2 if the CONNECT BY condition was "menu_id = PRIOR sub_menu_id", but for your CONNECT BY condition: "PRIOR menu_id = sub_menu_id", a corresponding UNION query would be:
Depending on what results you want, you may or may not need SELECT *DISTINCT* outside of the IN-subquery, but you never need DISTINCT in the IN-subquery.