CODEWITHSUNDEEP
sqloracle-database
harshal garg
harshal garg

Reputation: 15

Using outer query in inner query

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;

Upvotes: 1

Views: 80

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269853

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

Upvotes: 2

Related Questions