CODEWITHSUNDEEP
sqloracle-databaseoracle11g
Kermit the Hermit
Kermit the Hermit

Reputation: 379

Alternative to second-level nested correlated subquery

I need an alternative to the following SQL query. The reason being that some earlier versions of Oracle database (prior to 12C, not sure which exactly) don't support the 2nd-level nested correlated subquery, where Oracle 12C does.

select 
    a.val1,
    a.val2,
    (
        select /* select the top single-field record from the inner select 
                statement */
            * 
        from 
        (
            select 
                b.val1
            from 
                tb2 b
            where 
                b.val2 < a.val3 and b.val3 = a.val4
            order by 
                b.val2 desc /* order by b.val2 desc in order to allow 
                                the outer select statement to obtain the 
                                largest b.val2 value */
        ) 
        where rownum = 1
    ) as someName
from
    tb1 a;

Upvotes: 0

Views: 1216

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270573

You can do this using keep:

select a.val1, a.val2,
       (select max(b.val1) keep (dense_rank first order by val2 desc)
        from tb2 b
        where b.val2 < a.val3 and b.val3 = a.val4
       ) as someName
from tb1 a;

keep is powerful syntax in Oracle, which is explained in the documentation. Essentially, this gets the first value of val1 based on the ordering by val2. This is very similar to what first_value(val1) over (order by val2 desc)does -- except keep acts as an aggregation function.

Upvotes: 4

Jim Macaulay
Jim Macaulay

Reputation: 5155


You can use below query

select a.val1, a.val2, b.val1 from tb1 a
inner join
tb2 b on (b.val2 < a.val3 and b.val3 = a.val4) 
order by b.val2 desc;

or

 select a.val1, a.val2, b.val1 from tb1 a
inner join
(select val1 from tb2) b on (b.val2 < a.val3 and b.val3 = a.val4)
order by b.val2 desc;

Let me know in case you face any issues

Upvotes: 0

Related Questions