Oracle 12c: Side Note on IGNORE NULL for FIRST Aggregate Function Ordering (IT Toolbox Blogs)



Yes, I know that’s a catchy title for this posting. It’s that kind of nerdy posting. No false advertising or clickbait-and-switch here…

 

In 2011, I FIRST (cough, cough) wrote about using the FIRST and LAST aggregates. I don’t use them frequently since the same functionality can be achieved by analytic functions; the latter is more “my wheelhouse.” For example, the next time (the only other time to date) that I mentioned FIRST in a blog post was four years later (2015) as one of those obscure but handy features of Oracle.

 


 

Here’s a quick recap on why FIRST is a “good thing”. Consider a typical, time-oriented table for part and weight:

 

WITH part_wt ( part#, wt, dt ) AS ( SELECT
   1, 25, sysdate       FROM DUAL UNION ALL SELECT
   1, 26, sysdate-1     FROM DUAL UNION ALL SELECT
   1, 27, sysdate-2     FROM DUAL
) select * from part_wt
order by dt;

PART#         WT DT
----- ---------- -------------------
    1         25 2017-04-30 12:55:52
    1         26 2017-05-01 12:55:52
    1         27 2017-05-02 12:55:52

 

Over time, the weight of the part has decreased… maybe there’s been a upgrade to lighter material.

 

A typical approach to “get the weight of the part” is to do this:

 

select part#, max(wt) as wt
  from part_wt
 group by part#
 order by 1;

PART#         WT
----- ----------
    1         27

 

Just as clearly, that’s wrong. The current weight of the part is 25, not 27.

 

So the correct method uses KEEP… FIRST…:

 

select part#
     , max(wt) KEEP (DENSE_RANK FIRST ORDER BY dt DESC)
           as wt
  from part_wt
 group by part#
 order by 1;

PART#         WT
----- ----------
    1         25

 

Lovely. 

 


 

The wrinkle for today is a NULL value. For example, suppose the data is like this:

 

WITH part_wt ( part#, wt, dt ) AS ( SELECT
   1, NULL, sysdate       FROM DUAL UNION ALL SELECT
   1, 26, sysdate-1     FROM DUAL UNION ALL SELECT
   1, 27, sysdate-2     FROM DUAL
) select * from part_wt order by dt;

PART#         WT DT
----- ---------- -------------------
    1         27 2017-04-30 13:05:53
    1         26 2017-05-01 13:05:53
    1            2017-05-02 13:05:53

 

Clearly the NULL is nonsense. For reporting purposes, we want to ignore it.

 

Here the simple MAX function at least provides an answer since MAX will ignore NULLs.

 

select part#
     , max(wt) --K
           as wt
  from part_wt
 group by part#
 order by 1;

PART#         WT
----- ----------
    1         27

 

Not so KEEP…FIRST:

 

select part#
     , max(wt) KEEP (DENSE_RANK FIRST ORDER BY dt DESC)
           as wt
  from part_wt
 group by part#
 order by 1;

PART#         WT
----- ----------
    1

 

No good at all (again that’s the situation I found today).

 


 

 

Even up through version 12c I couldn’t find an IGNORE NULLS clause for KEEP…FIRST. So I used a CASE statement thusly:

 

select part#
     , max(wt) KEEP (DENSE_RANK FIRST
        ORDER BY CASE
                 WHEN wt IS NULL
                 THEN ADD_MONTHS(dt,-520)
                 ELSE dt
                  END DESC)
           as wt
  from part_wt
 group by part#
 order by 1;

PART#         WT
----- ----------
    1         26

 

Note: it depends on your data, but “going back” 520 months (over 43 years) if there’s a NULL value works for my purposes.

 

It’s a similar application to the CASE statement I talked about back over a decade ago early in my blogging!

 

<END> 

 

 



 

The wikipedia image is a Smith-Chart in high resolution. The Smith chart, invented by Phillip H. Smith (1905–1987), is a graphical aid or nomogram designed for electrical and electronics engineers specializing in radio frequency (RF) engineering to assist in solving problems with transmission lines and matching circuits. The Smith chart can be used to simultaneously display multiple parameters including impedances, admittances, reflection coefficients, scattering parameters, noise figure circles, constant gain contours and regions for unconditional stability, including mechanical vibrations analysis. I just picked it because I found it and liked it when searching wikipedia images with the key words: “complex case first value.”

 

 
KEEP
 
FIRST
 
DENSE_RANK
 
CASE statement

Source: SANS ISC SecNewsFeed @ May 2, 2017 at 02:09PM

0
Share