Query returning NULL value for columns

Question

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

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

When you just run the inner query, are there any p_number from the list you pass to IN in the complete query? I'd guess no. — sticky bit
– sticky bit, Commented Apr 25, 2019 at 9:32
@stickybit You guessed it right, I'm not passing any p_number. It just picks random p_number. — Red Virus
– Red Virus, Commented Apr 25, 2019 at 9:33
'31-JUL-2017' is a string not a timestamp, you should compare data types properly and not rely on implicit conversion and NLS settings; and your rownum filtering isn't right as the ordering happens too late. Neither of which is necessarily relevant... but please include the table DDL; particularly, what are the data types of the two p_number columns? Your output suggests they might strings and that could cause a problem, but that might just be from how you created/generated your image. (And please post text rather than images...) — Alex Poole
– Alex Poole, Commented Apr 25, 2019 at 9:33
@RedVirus: Then there are no rows from the inner query that satisfy the ON clause of the left join. And that's why the columns stay null. — sticky bit
– sticky bit, Commented Apr 25, 2019 at 9:36

Ponder Stibbons · Accepted Answer · 2019-04-25 09:59:16Z

4

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}

answered Apr 25, 2019 at 9:59

Ponder Stibbons

14.9k2 gold badges23 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Red Virus Over a year ago

Thank you. This works. This is my first time using partition. Cheers!

Collectives™ on Stack Overflow

Query returning NULL value for columns

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related