SQL: My CASE for Ignoring or Lasting NULLS (IT Toolbox Blogs)



A day ago or so I wrote a “side note” on how to “IGNORE NULL for FIRST Aggregate Function Ordering“. As is typical, just the act of writing got some ideas rolling around in my head. Although I’ve been enjoying the FIRST_VALUE analytic function and applying CASE statements a long time (since at least 2006), I realized… I could do this better!

 

In my recent posting:

  1. I noted that even Oracle 12c SQL didn’t have an IGNORE NULL option for aggregate functions like FIRST. However, the 12c syntax diagram did include a “NULLS FIRST” or “NULLS LAST” option (which an earlier versions didn’t).
  2. I proposed a CASE statement to behave like IGNORE NULL.

Unfortunately, my CASE made assumptions I didn’t really like making. Minimalism in assumptions is my preference.

 


 

So I expanded yesterday’s example involving “weights and dates”. Just like yesterday, we’re trying to find the “latest weight” of a part. However,in this data sometimes the weight is is null, and sometimes the date is null. Here’s the sample data in Oracle:

 

WITH part_wt ( part#, wt, dt ) AS ( SELECT
   1,   30, NULL               FROM DUAL UNION ALL SELECT
   1,   25, NULL               FROM DUAL UNION ALL SELECT
   1, NULL, date'2017-05-01'   FROM DUAL UNION ALL SELECT
   1,   35, date'2017-05-01'-1 FROM DUAL UNION ALL SELECT
   1,   27, date'2017-05-01'-2 FROM DUAL UNION ALL SELECT
   1, NULL, date'2017-05-01'-3 FROM DUAL UNION ALL SELECT
   1,   38, date'2017-05-01'-4 FROM DUAL ),

 

and in SQL Server 2012:

 

WITH part_wt ( part#, wt, dt ) AS ( SELECT
   1,   30, NULL                        UNION ALL SELECT
   1,   25, NULL                        UNION ALL SELECT
   1, NULL, CAST('2017-05-01' AS DATE)  UNION ALL SELECT
   1,   35, CAST('2017-04-30' AS DATE)  UNION ALL SELECT
   1,   27, CAST('2017-04-29' AS DATE)  UNION ALL SELECT
   1, NULL, CAST('2017-04-28' AS DATE)  UNION ALL SELECT
   1,   38, CAST('2017-04-27' AS DATE)  ), 

 

(The part# is the same throughout; it’s just there to give a realistic “feel” to the example — I did start with “real data” on a “real problem” with which I’ve been wresting quite a few weeks).

 


 

Now here’s a simple example of FIRST_VALUE with date descending in Oracle 12c:

 

...
simple_order AS (
select wt
     , dt
     , ROW_NUMBER()OVER
            (ORDER BY dt DESC) r#
     , FIRST_VALUE(wt)OVER
            (ORDER BY dt DESC) first_wt
  from part_wt
)
select * from simple_order order by r#;

WT DT                   R#   FIRST_WT
-- ------------------- --- ----------
25                       1         25
30                       2         25
   2017-05-01 00:00:00   3         25
35 2017-04-30 00:00:00   4         25
27 2017-04-29 00:00:00   5         25
   2017-04-28 00:00:00   6         25
38 2017-04-27 00:00:00   7         25

7 rows selected.

 

Yet in SQL Server 2012, there’s obviously a different collation, because the results are different:

 

wt          dt         r#    first_wt
----------- ---------- ----- -----------
NULL        2017-05-01 1     NULL
35          2017-04-30 2     NULL
27          2017-04-29 3     NULL
NULL        2017-04-28 4     NULL
38          2017-04-27 5     NULL
25          NULL       6     NULL
30          NULL       7     NULL

(7 row(s) affected)

 

 


 

I decided it was time to be very deliberate, very explicit in my ordering. I want to 

  • Ignore NULL in the weights
  • Ignore NULL in the dates
  • Prefer the latest date’s weight
  • Choose the leats weight on the wild chance that all dates are NULL.
  • You can see the implementation of all four orderings below:

     

    complex_order AS (
    select wt
         , dt
         , ROW_NUMBER()OVER
                (ORDER BY CASE WHEN wt IS NULL
                               THEN 2 ELSE 1
                           END -- "IGNORE NULLS" in WT
                        , CASE WHEN dt IS NULL
                               THEN 2 ELSE 1
                           END -- "NULLS LAST" in DT
                        , dt DESC
                        , wt DESC
                ) AS r#
         , FIRST_VALUE(wt) OVER
                (ORDER BY CASE WHEN wt IS NULL
                               THEN 2 ELSE 1
                           END -- "IGNORE NULLS" in WT
                        , CASE WHEN dt IS NULL
                               THEN 2 ELSE 1
                           END -- "NULLS LAST" in DT
                        , dt DESC
                        , wt DESC
               ) as first_wt
      from part_wt
    ) select * from complex_order order by r#;
    
     WT DT                   R#   FIRST_WT
    --- ------------------- --- ----------
     35 2017-04-30 00:00:00   1         35
     27 2017-04-29 00:00:00   2         35
     38 2017-04-27 00:00:00   3         35
     30                       4         35
     25                       5         35
        2017-05-01 00:00:00   6         35
        2017-04-28 00:00:00   7         35
    
    7 rows selected.

     

    Same in SS2012:

     

    wt      dt         r#         first_wt
    ------- ---------- ---------- -----------
    35      2017-04-30 1          35
    27      2017-04-29 2          35
    38      2017-04-27 3          35
    30      NULL       4          35
    25      NULL       5          35
    NULL    2017-05-01 6          35
    NULL    2017-04-28 7          35
    
    (7 row(s) affected)

     

    I like that the results are the same in both DBMS.

     

    I like that I’m not monkeying around with DBMS-specific date implementations.

     

    I like that I’m not jockeying around a large-and-clumsy CASE statement: just 2 very specific and short CASE statements.

     

    I like that I don’t have to remember which version or implementation has IGNORE NULLS or NULLS LAST or other variations.

     

    <END>

     

     



     

     

    The wikipedia image is the Wikibook “Introduction to SQL” (in German) by Jürgen Thomas.

     

     

     

     
    FIRST_VALUE
     
    IGNORE NULLS
     
    NULLS LAST
     
    Oracle 12c
     
    SQL Server

    Source: SANS ISC SecNewsFeed @ May 4, 2017 at 06:18AM

    0
    Share