CODEWITHSUNDEEP
sqloracle-databaseoracle11g
user4441082
user4441082

Reputation: 371

Table left join with itself returning NULL

I have a table like

Tdate          Symbol       new_close
20100110        xxx           1.2
20100111        xxx           1.3
...
20100110        yyy           1.1
20100111        yyy           1.5

Where Tdate was stored as integer and updated per day for each symbol. I want generate a new table from it by subtracting new_close value to its previous one, which looks like this

    Tdate          Symbol       delta
    20100110        xxx           =1.2-1.2
    20100111        xxx           =1.3-1.2
    ...
    20100110        yyy           =1.1-1.1
    20100111        yyy           =1.5-1.1

Here is my code

with delta as
( select a.Tdate as TDate,  a.Symbol as Symbol,
a.new_close-b.new_close as Pdelta, b.new_close as oldPrice
   from ctsWithSplit a left join ctsWithSplit b
   on a.TDate-b.TDate=1 and a.Symbol=b.Symbol)

However, in the new generated table, some delta value is NULL, how to fix it please?

Upvotes: 0

Views: 56

Answers (2)

Boneist
Boneist

Reputation: 23578

Why is TDATE being stored as a number? Your query will fail to find the previous day when the date is the first of the month - eg: 20150201 - 20150131 = 70, whereas there's only a day between 31st Jan and 1st Feb.

Store dates as DATE or TIMESTAMP datatype, and then you give Oracle a chance at getting the date arithmetic correct.

Perhaps you're after something like:

with sample_data as (select to_date('10/01/2010', 'dd/mm/yyyy hh24:mi:ss') tdate, 'xxx' symbol, 1.2 new_close from dual union all
                     select to_date('11/01/2010', 'dd/mm/yyyy hh24:mi:ss') tdate, 'xxx' symbol, 1.3 new_close from dual union all
                     select to_date('10/01/2010', 'dd/mm/yyyy hh24:mi:ss') tdate, 'yyy' symbol, 1.1 new_close from dual union all
                     select to_date('11/01/2010', 'dd/mm/yyyy hh24:mi:ss') tdate, 'yyy' symbol, 1.5 new_close from dual)
select tdate,
       symbol,
       new_close - lag(new_close, 1, new_close) over (partition by symbol order by tdate) delta
from   sample_data;

TDATE      SYMBOL DELTA
---------- ------ -----
10/01/2010 xxx      0.0
11/01/2010 xxx      0.1
10/01/2010 yyy      0.0
11/01/2010 yyy      0.4

If you've never worked with analytic functions, then I suggest you look them up - they're incredibly useful and very powerful.

N.B. If you can't convert your TDATE column to be DATE datatype, then you need to convert the column into a date in any queries you run by using to_date().

Upvotes: 1

TobyLL
TobyLL

Reputation: 2286

When your left join doesn't find a matching row in b (i.e. on the first Tdate, or where there isn't a Tdate for the preceding day), then b.new_close will be NULL, as will the result of the subtraction. Try:

select
    a.Tdate as TDate,
    a.Symbol as Symbol,
    CASE WHEN b.new_close IS NULL THEN 0 ELSE a.new_close-b.new_close END as Pdelta,
    b.new_close as oldPrice
from ctsWithSplit a
left join ctsWithSplit b
    on a.TDate-b.TDate=1
    and a.Symbol=b.Symbol

Upvotes: 0

Related Questions