The Vertica Forum recently got a makeover! Let us know what you think by filling out this short, anonymous survey.
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;
0
Answers
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
@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 VerticaROLLUP(x1, x2, ...)
feature is _almost_ perfect.But there are three complications in the toy example below:
BY
variables are of unknown possibly mixed typesNULL
values from rollups but they both return asNULL
NULL
valuesWhere I'm netting out is that it's practical if not pretty to:
by
columns toVARCHAR
to avoid type conflictsNULL
values to a special string to differentiate rollups from true nullsGROUP BY ROLLUP(1,2,3...)
NULL
as a rollup value and the special string as aNULL
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.
..and a VSQL count scale example:
See this: https://www.vertica.com/blog/vertica-quick-tip-query-nan-values/