CODEWITHSUNDEEP
oracle-databasenullrows
Landon Statis
Landon Statis

Reputation: 839

Always return X number of rows

We have a set of values which we use to populate a bar chart. For this application, we will always need 5 years of data, we will always need 5 rows of data, even if the values are NULL.

See this query. Assume that the DATE column goes from 2017, 2016, 2015.........even those we may have no data for 2014 & 2013, I will need to return a 2014 & 2013 for, with a NULL as the other column.....

SELECT period_date, actual_eps
  FROM (SELECT LAST_DAY(TO_DATE(TO_CHAR(period_date),'YYYYMM')) period_date, actual_eps
        FROM period_data
        WHERE ticker = 'ADRO'
          AND period_type = 'A'
          AND actual_eps IS NOT NULL
        ORDER BY period_date DESC NULLS LAST)
  WHERE rownum <= 5;

So, it will return what rows it has, up to 5, and NULL for the other rows which it does not have, up to 5.......

Thanks in advance

Upvotes: 0

Views: 58

Answers (1)

bruceskyaus
bruceskyaus

Reputation: 783

Try using a Common Table Expression/Subquery Factoring to generate rows for each year value. Use a RIGHT JOIN to generate NULLs for any missing rows. Normally I would use a LEFT JOIN. But in this case I think it reads better this way. Use NVL to substitute the year for NULL period_date values.

with years as
(
    select to_char(sysdate, 'YYYY') as year from dual
    UNION ALL
    select to_char(add_months(sysdate,-12), 'YYYY') as year from dual
    UNION ALL
    select to_char(add_months(sysdate,-24), 'YYYY') as year from dual
    UNION ALL
    select to_char(add_months(sysdate,-36), 'YYYY') as year from dual
    UNION ALL
    select to_char(add_months(sysdate,-48), 'YYYY') as year from dual
)
SELECT
NVL(TO_CHAR(LAST_DAY(pd.period_date),'YYYYMM'),y.year) as period_date,
pd.actual_eps
FROM period_data pd
RIGHT JOIN years y ON y.year = to_char(pd.period_date,'YYYY')
                  AND pd.ticker = 'ADRO'
                  AND pd.period_type = 'A'
                  AND pd.actual_eps IS NOT NULL
WHERE rownum <= 5
ORDER BY period_date desc, actual_eps nulls last;

Output:

| PERIOD_DATE | ACTUAL_EPS |
|-------------|------------|
|      201902 |        foo |
|      201802 |        foo |
|      201702 |        foo |
|        2016 |     (null) |
|        2015 |     (null) |

SQL Fiddle example

Upvotes: 1

Related Questions