Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

Other special values besides NULL?

Are there other special values like NULL in Vertica SQL ?

NULL is nice in that data columns of any type can have NULL values.
We can interpret NULL as the concept of "N/A" (i.e. missing, not available, not asked, not answered, etc.).
I would also like to represent another concept, the idea of Overall, in the same result set.

Is there another special value that I can return in a query, other than NULL, but which like NULL:

  • can be returned in any type of column integer, float, string, date, etc.
  • doesn't match any actual finite value that might be in the data
  • doesn't throw ERROR: For 'UNION', types varchar and numeric are inconsistent in this toy query:
select 1 as numeral, 'one' as name
union all 
select 2 as numeral, 'two' as name
union all 
select NULL as numeral, NULL as name
union all 
select ??? as numeral, ??? as name;

https://www.vertica.com/docs/9.3.x/HTML/Content/Authoring/SQLReferenceManual/LanguageElements/Literals/Number-typeLiterals.htm

Answers

  • moshegmosheg Administrator

    NaN (Not a Number) does not equal anything, not even another NaN.
    You can query for them using the predicate … WHERE column != column
    See: https://www.vertica.com/blog/vertica-quick-tip-query-nan-values

    create table NaN_Test (c1 float);
    insert into NaN_Test select 'NaN';
    select * from NaN_Test;
    c1
    -----
    NaN
    (1 row)
    
    select * from NaN_Test where c1 = 'NaN';
    c1
    ----
    (0 rows)
    
    select * from NaN_Test where c1 != c1;
    c1
    -----
    NaN
    (1 row)
    
  • edited November 15

    @mosheg

    Circling back with new words from exploration, my actual goal was to calculate COUNT/GROUP BY with rollups and differentiate counts of actual NULL values from rollups across values. For this the Vertica ROLLUP(x1, x2, ...) feature is _almost_ perfect.

    But there are three complications in the toy example below:

    • the BY variables are of unknown possibly mixed types
    • I'd like to differentiate true NULL values from rollups but they both return as NULL
    • I'd like to merge the results back but you can't merge on NULL values

    Where I'm netting out is that it's practical if not pretty to:

    • cast all by columns to VARCHAR to avoid type conflicts
    • coalesce all NULL values to a special string to differentiate rollups from true nulls
    • use the vSQL syntax GROUP BY ROLLUP(1,2,3...)
    • on return, interpret NULL as a rollup value and the special string as a NULL
    WITH 
        base AS (
            SELECT 1 as numeral, 'one' AS name UNION ALL
            SELECT 2 as numeral, 'two' AS name UNION ALL
            SELECT NULL as numeral, NULL AS name
        ),
        summed AS (
            SELECT numeral, name, GROUPING_ID(),count(*) 
            FROM base
            GROUP BY ROLLUP(1,2)
        )
    SELECT * FROM summed
    ORDER BY grouping_id, numeral, name
    ;
    
    numeral name grouping_id count
    NULL NULL 0 1
    1 one 0 1
    2 two 0 1
    NULL NULL 1 1
    1 NULL 1 1
    2 NULL 1 1
    NULL NULL 3 3
  • VValdarVValdar Employee

    Hi Pieter_Sheth-Vo,

    You're only lacking the knowledge of the grouping function that let you know if it's a base line row or a rolled up one.

    with cte_base (numeral, name) as
    (
    select 1   , 'one' union all
    select 2   , 'two' union all
    select null, null
    )
      select case grouping(numeral) when 1 then 999        else numeral end as numeral
           , case grouping(name)    when 1 then 'All name' else name    end as name
           , count(*)
        from cte_base
    group by rollup(numeral, name)
    order by grouping_id(), 1, 2;
    
    numeral  name     count
    -------- -------- -----
     [NULL]  [NULL]       1
          1  one          1
          2  two          1
     [NULL]  All name     1
          1  All name     1
          2  All name     1
        999  All name     3
    
  • moshegmosheg Administrator

    ..and a VSQL count scale example:

    \set SCALE_MAX_CHARS 40
    with list1 (numeral, name) as
    (
    select 1   , 'one' union all
    select 2   , 'two' union all
    select 2   , 'two' union all
    select 3   , 'three' union all
    select 3   , 'three' union all
    select 3   , 'three' union all
    select null, null
    ),
    list2 as (select case grouping(numeral) when 1 then 'TOTAL'    else ISNULL(TO_CHAR(numeral),'[NULL]') end as numeral
           , case grouping(name)    when 1 then '' else ISNULL(name,'[NULL]') end as name
           , grouping_id() as gid
           , count(*) as count
        from list1
    group by rollup(numeral,name)
    order by grouping_id(), 1, 2),
    norm as (select max(count / :SCALE_MAX_CHARS)::INT as norm_factor from list2)
    select numeral, name,
           case when gid > 0 then count end as count,
           case gid when 1 then REPEAT('*', ((count / (norm_factor +1))::INT)) else '' end as Count_Scale
    from list2, norm
    order by gid ,1,2;
    
     numeral |  name  | count | Count_Scale
    ---------+--------+-------+-------------
     1       | one    |       |
     2       | two    |       |
     3       | three  |       |
     [NULL]  | [NULL] |       |
     1       |        |     1 | *
     2       |        |     2 | **
     3       |        |     3 | ***
     [NULL]  |        |     1 | *
     TOTAL   |        |     7 |
    (9 rows)
    

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.