Oracle How to make SELECT INSIDE A SELECT work?-CodePudding

Just wondering why the following select isn't working:

 SELECT 
    A.FIELD1
     , (SELECT PCN FROM (select B.PRIORITY, B.PCN 
                        from
                        TABLE2 B 
                        WHERE B.CUST= A.CUST 
                        ORDER BY B.PRIORITY) 
         WHERE ROWNUM = 1) AS PCN 
  FROM TABLE1 A;

ERROR at line 2: ORA-00904: "A"."CUST": invalid identifier

Important to mention:

TABLE1 has as fields FIELD1, CUST.
TABLE2 has as fields PCN, PRIORITY, CUST.

Thanks in advance.

CodePudding user response：

Your query shouldn't give you that error message, on when you remove the outer qiery this would happen

CREATE tABLE TABLE1  (FIELD1 int, CUST int)

INSERT INTO TABLE1 VALUES(1,1)

1 rows affected

CREATE TABLE TABLE2 (PCN int, PRIORITY int, CUST int)

INSERT INTO TABLE2 VALUES (1,1,1)

1 rows affected

SELECT 
    A.FIELD1
     , (SELECT PCN FROM (select B.PRIORITY, B.PCN 
                        from
                        TABLE2 B 
                        WHERE B.CUST= A.CUST 
                        ORDER BY B.PRIORITY) 
         WHERE ROWNUM = 1) AS PCN 
  FROM TABLE1 A;

FIELD1	PCN
1	1

fiddle

CodePudding user response：

You could try this:

SELECT 
A.FIELD1
 , (SELECT B.PCN 
                    from
                    TABLE2 B 
                    WHERE B.CUST= A.CUST 
                    ORDER BY B.PRIORITY
     FETCH FIRST 1 ROWS ONLY) AS PCN 
FROM TABLE1 A;

FETCH FIRST 1 ROWS ONLY gets you the first ordered record. Works on 12c and up and supports nesting, and no 2nd subquery needed.

CodePudding user response：

You can't nest inline selects (more than one level) without losing the ability of the inner nested selects being able to reference the parent block. So your query on TABLE2 cannot see the columns from TABLE1 because of this nesting.

Try this:

SELECT a.field1,
       pcn.pcn
  FROM table1 a,
       (SELECT b.cust,
               b.priority,
               b.pcn,
               ROW_NUMBER() OVER (PARTITION BY b.cust ORDER BY b.priority DESC) seq
          FROM table2 b) pcn
 WHERE a.cust = pcn.cust( )
   AND pcn.seq( ) = 1

That will work well for report queries. If you end up adding a filter on a specific customer, then you would be better off using OUTER APPLY if you have a recent-enough version of Oracle that supports that.

CodePudding user response：

Yet another option might be a CTE.

Sample data:

SQL> with
  2  table1 (field1, cust) as
  3    (select 1, 100 from dual union all
  4     select 2, 200 from dual
  5    ),
  6  table2 (pcn, priority, cust) as
  7    (select 10, 1, 100 from dual union all
  8     select 20, 2, 100 from dual union all
  9     select 30, 1, 200 from dual
 10    ),

Query begins here. Rank rows by priority, and then fetch the ones that rank as the highest (line #20):

 11  temp as
 12    (select a.field1,
 13         b.pcn,
 14         rank() over (partition by a.field1 order by b.priority desc) rnk
 15     from table1 a join table2 b on a.cust = b.cust
 16    )
 17  select field1,
 18         pcn
 19  from temp
 20  where rnk = 1;

    FIELD1        PCN
---------- ----------
         1         20
         2         30

SQL>

CodePudding user response：

You may use first aggregate function to achieve the same (assuming that you have completely deterministic order by) functionality without nested subquery:

select
  a.field1
  , (
    select max(b.pcn) keep(dense_rank first order by b.priority)
    from table2 b
    where b.cust = a.cust
  ) as pcn
from table1 a

which for this sample data

insert into table1 values(1,1);
insert into table1 values(2,2);
insert into table2 values(1,1,1);
insert into table2 values(2,2,1)

returns

FIELD1	PCN
1	1
2	(null)

SQL fiddle