CODEWITHSUNDEEP
oracle-databaseoracle10g
Red Virus
Red Virus

Reputation: 1707

Query returning NULL value for columns

I'm very new to using sub queries. When running the following query, the column t_num and timestamp is NULL whereas in the respective table those columns have values.

Table trans contains multiple p_num, trans_amt, trans_balance. I just want to get the transaction that happened before 31-JULY-2017 and before.

select 
    p.p_number, 
    p.name, 
    t.t_num, 
    t.timestamp 
from payers p
    left join (
        select p_number, t_num, timestamp from trans a
        where a.timestamp <= '31-JUL-2017' and rownum = 1 
        order by a.timestamp desc
    ) t on t.p_number = p.p_number
where p.p_number in(44545558, 44545559, 44545560, 44545561, 44545562)

Result

Result

I tried just running this portion of the query and I can see the t_num and timestamp column values.

select p_number, t_num, timestamp from trans a
where a.timestamp <= '31-JUL-2017' and rownum = 1 
order by a.timestamp desc

Upvotes: 0

Views: 957

Answers (1)

Ponder Stibbons
Ponder Stibbons

Reputation: 14848

Your query first orders data in subquery and takes first which has not proper p_number. It's better and safer to use row_number in such queries:

select p.p_number, p.name, t.t_num, t.timestamp
  from payers p
  left join (select p_number, t_num, timestamp,
                    row_number() over (partition by p_number order by timestamp desc) rn 
               from trans t 
               where t.timestamp <= date '2017-07-31') t
         on p.p_number = t.p_number and rn = 1
  where p.p_number between 44545558 and 44545562

dbfiddle example

Upvotes: 4

Related Questions