CODEWITHSUNDEEP
sqloracle-database
behzad omidi
behzad omidi

Reputation: 39

how use column from one subquery to anoter subquery

I have two subquery .i want put p.price from first subquery into secound subquery(place XXX) . but get error => ORA-00904: "P"."PRICE": invalid identifier

select 
    p.product_id,
    p.price, 
    l.delegation, 
    l.state 
from users
inner join (
    select 
        start_date,
        price,
        product_id, 
        row_number() over (partition by serial order by start_date desc ) as rn
    from prices 
) p on users.serial = p.serial 
inner join (
    select 
        sso_id , 
        delegation,
        state,
        updated_at, 
        row_number() over (partition by state order by updated_at asc) as tl  
    from payments
    where state = 'green' or (state = 'yellow' and delegation > XXX)
) l on users.sso_id= l.sso_id
where  
    p.rn = 1 
    and l.tl = 1

Upvotes: 0

Views: 107

Answers (3)

Popeye
Popeye

Reputation: 35930

Use it in the main WHERE clause 

select 
    p.product_id,
    p.price, 
    l.delegation, 
    l.state 
from users
inner join (
    select 
        start_date,
        price,
        product_id, 
        row_number() over (partition by serial order by start_date desc ) as rn
    from prices 
) p on users.serial = p.serial 
inner join (
    select 
        sso_id , 
        delegation,
        state,
        updated_at, 
        row_number() over (partition by state order by updated_at asc) as tl  
    from payments
    where state = 'green' or state = 'yellow'
) l on users.sso_id= l.sso_id
where  
    p.rn = 1 
    and l.tl = 1 
    -- add following condition
and l.delegation > p.price

Upvotes: 0

MT0
MT0

Reputation: 168806

Join on the PAYMENTS table and filter the rows to exclude the invalid state/delegation rows and then generate the ROW_NUMBER and filter to find the first row per partition:

Oracle Setup:

CREATE TABLE users ( serial, sso_id ) AS
SELECT 1, 1 FROM DUAL UNION ALL
SELECT 2, 2 FROM DUAL UNION ALL
SELECT 3, 3 FROM DUAL;

CREATE TABLE prices ( serial, start_date, price, product_id ) AS
SELECT 1, DATE '2019-01-01', 20, 1 FROM DUAL UNION ALL
SELECT 1, DATE '2019-02-01', 30, 2 FROM DUAL UNION ALL
SELECT 1, DATE '2019-03-01', 25, 3 FROM DUAL UNION ALL
SELECT 2, DATE '2019-01-01', 20, 1 FROM DUAL UNION ALL
SELECT 2, DATE '2019-02-01', 25, 2 FROM DUAL UNION ALL
SELECT 2, DATE '2019-03-01', 30, 3 FROM DUAL UNION ALL
SELECT 3, DATE '2019-01-01', 40, 3 FROM DUAL;

CREATE TABLE payments ( sso_id, delegation, state, updated_at ) AS
SELECT 1, 20, 'green',  DATE '2019-01-01' FROM DUAL UNION ALL
SELECT 1, 30, 'green',  DATE '2019-02-01' FROM DUAL UNION ALL
SELECT 1, 27, 'green',  DATE '2019-03-01' FROM DUAL UNION ALL
SELECT 1, 22, 'green',  DATE '2019-04-01' FROM DUAL UNION ALL
SELECT 1, 26, 'yellow', DATE '2019-05-01' FROM DUAL UNION ALL
SELECT 2, 31, 'yellow', DATE '2019-01-01' FROM DUAL UNION ALL
SELECT 2, 31, 'green',  DATE '2019-02-01' FROM DUAL UNION ALL
SELECT 3, 30, 'green',  DATE '2019-01-01' FROM DUAL UNION ALL
SELECT 3, 30, 'yellow', DATE '2019-01-01' FROM DUAL UNION ALL
SELECT 3, 50, 'yellow', DATE '2019-02-01' FROM DUAL;

Query:

SELECT product_id,
       price, 
       delegation, 
       state
FROM   (
  select p.product_id,
         p.price, 
         l.delegation, 
         l.state,
         row_number() over ( partition by l.sso_id, l.state order by l.updated_at asc) as tl  
  from   users
         inner join (
           select serial,
                  start_date,
                  price,
                  product_id, 
                  row_number() over (partition by serial order by start_date desc ) as rn
           from   prices 
         ) p
         on ( users.serial = p.serial AND p.rn = 1 )
         inner join payments l
         on ( users.sso_id = l.sso_id AND ( l.state = 'green' or (l.state = 'yellow' and l.delegation > p.price ) ) )
)
where  tl = 1

Output:

PRODUCT_ID | PRICE | DELEGATION | STATE 
---------: | ----: | ---------: | :-----
         3 |    25 |         20 | green 
         3 |    25 |         26 | yellow
         3 |    30 |         31 | green 
         3 |    30 |         31 | yellow
         3 |    40 |         30 | green 
         3 |    40 |         50 | yellow

db<>fiddle here

Upvotes: 1

Ed Bangga
Ed Bangga

Reputation: 13026

We need to rejoin your users and price table to your payment to get matching p.price

SELECT p.product_id,
    p.price,
    l.delegation,
    l.state
FROM users
INNER JOIN
    (SELECT start_date,
        price,
        product_id,
        ROW_NUMBER() OVER (PARTITION BY serial ORDER BY start_date DESC) AS rn
    FROM prices) p ON users.serial = p.serial
INNER JOIN
    (SELECT y.sso_id,
        y.delegation,
        y.state,
        y.updated_at,
        ROW_NUMBER () OVER (PARTITION BY y.state ORDER BY y.updated_at ASC) AS tl
    FROM payments y
    INNER JOIN users u ON u.sso_id = y.sso_id
    INNER JOIN prices p ON p.serial = y.serial
    WHERE tl.state = 'green' OR (tl.state = 'yellow' AND tl.delegation > p.price)) l ON users.sso_id = l.sso_id
WHERE  
      AND p.rn = 1 AND l.tl = 1

Upvotes: 0

Related Questions