Using outer query in inner query

Question

I don't understand what is the mistake here why it is not working (Oracle DB)

https://www.hackerrank.com/challenges/binary-search-tree-1/problem?isFullScreen=true

This is a question of hackerrank

SELECT N, 
(case
 when P IS NULL then 'Root'
 else
    when (SELECT COUNT(*) FROM BST WHERE P=B.N) > 0 then 'Inner'
    else 'Leaf'
    end
 end ))
 FROM BST AS B ORDER BY N;

First I thought that outer query is used in inner query so it won't work. But if it is working for MySQL then it should work for Oracle also.

This below code is for MySQL and it was working

SELECT N, IF(P IS NULL,'Root',IF((SELECT COUNT(*) 
FROM BST WHERE P=B.N)>0,'Inner','Leaf')) 
FROM BST AS B ORDER BY N;

To your specific question, why doesn't your code work: You have a nested case expression. That is not necessary (as Barbaros Ozhan has shown), but if you do use a nested case expression (which is not wrong, it's just unnecessary) as you do, you are missing the keyword case in the inner expression - right before when (SELECT COUNT..... (As B.O. also said, you have an invalid use of as later in the query.) — user5683823
– user5683823, Commented Apr 5, 2020 at 15:05
You are saying that case is not necessary, can you tell me what is the simplified version of this query. Though Barbaroz Ozhan has given a great solution. I just want to know if you have another approach — harshal garg
– harshal garg, Commented Apr 7, 2020 at 15:34

Barbaros Özhan · Accepted Answer · 2020-04-05 14:10:46Z

2

Oracle doesn't support as for table aliases. However, I would recommend writing this as:

select b.N, 
       (case when b.P IS NULL
             then 'Root'
             when exists (select 1 from bst b2 where b2.p = b.n) 
             then 'Inner'
             else 'Leaf'
        end)
from BST B
order by N;

This is standard SQL and should work in both Oracle and MySQL.

Notes:

CASE expressions do not need to be nested. They can have multiple WHEN clauses.
In a query with multiple table references, qualify all column references.
exists is faster than count(*) because it can stop at the first match.
Aliasing tables through AS keyword is not allowed in Oracle DB

edited Apr 5, 2020 at 14:10

Barbaros Özhan

65.9k11 gold badges36 silver badges64 bronze badges

answered Apr 5, 2020 at 13:46

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Gordon Linoff Over a year ago

@harshalgarg . . . Is there a reason you unaccepted this answer?

Collectives™ on Stack Overflow

Using outer query in inner query

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related