Re: SQL question

On 09 Sep 2013 09:44, Vern Hamberg wrote:

<<SNIP>> Someone else mentioned doing the totals with some reporting
tool. Otherwise, you have to use some "interesting" value like
9,999,999 to get the sort right when doing a UNION SELECT - and in
this case, you couldn't use a true date data type.

The database NULL value collates as an effective *HIVAL [and is valid for a date data type; of course ODLDAT is just CHAR(6)]. Any of the examples using 999999 for a contrived high-value for the 'MMDDYY' of the column ODLDAT would best have used the following expression, to have no particular value [a non-value] vs the invalid\contrived value:

CAST(NULL as CHAR(6))

And besides, the SQL OLAP capability introduced GROUPING SETS which "can be thought of as the union of two or more groups of rows into a single result set." Thus a typical query coding the UNION ALL query to get those rows, the equivalent OLAP query can be used instead. Specific grouping sets are available with CUBE and ROLLUP syntax.