My query
SELECT node_nme, os_nme, os_vrsn_nme, CPU_QTY, mch_id_nme
FROM node_t
WHERE node_nme IN
(
SELECT DISTINCT upper(node_nme) as node_nme FROM node_t WHERE row_status_cde IN (1,4)
AND node_nme NOT IN (SELECT DISTINCT node_nme FROM drone_load_bad_nodes)
MINUS
SELECT DISTINCT upper(node_nme) as node_nme FROM drone_load_master
WHERE node_nme NOT IN (SELECT DISTINCT node_nme FROM drone_load_bad_nodes)
ORDER BY node_nme
)Now, when I run this i get the missing parenthesis error, though I don't konw why.
The subselect in the IN works fine and returns exactly what it should.
Oracle does not allow to write ORDER BY clause in a sub-query that is defined in WHERE clause, but allows you to use ORDER BY clause in sub-query that is defined in FROM clause. When you write any sub-query in FROM clause, it becomes an inline view. For example
SELECT empno, ename, sal, deptno
FROM (select empno, ename, sal, deptno
FROM dept
ORDER BY DEPTNO)
Please write your WHERE clause condition same as it is, in the FROM clause. If it still does not work then remove the ORDER BY clause.
Regards
Oracle does not allow to write ORDER BY clause in a sub-query that is defined in WHERE clause, but allows you to use ORDER BY clause in sub-query that is defined in FROM clause. When you write any sub-query in FROM clause, it becomes an inline view. For example
SELECT empno, ename, sal, deptno
FROM (select empno, ename, sal, deptno
FROM dept
ORDER BY DEPTNO)
Please write your WHERE clause condition same as it is, in the FROM clause. If it still does not work then remove the ORDER BY clause.
Regards
Is it true? "with SQL*Plus: Release 8.0.4.0.0,order by clause in FROM clause is not working"