I'm determining the earliest date I might have a transaction in either of two tables so

SELECT MIN(EXTRACT(YEAR FROM _DatePosted)) AS Earliest FROM Transactions
UNION
SELECT MIN(EXTRACT(YEAR FROM _DatePaid)) FROM Transactions
UNION
SELECT MIN(EXTRACT(YEAR FROM _Date)) FROM Transactions
UNION
SELECT MIN(EXTRACT(YEAR FROM _Acquired)) FROM Assets
UNION
SELECT MIN(EXTRACT(YEAR FROM _Disposed)) FROM Assets


should I get a null in the result set?

2019
2018
NULL

The full statement

SELECT MIN(Earliest) FROM
(
SELECT MIN(EXTRACT(YEAR FROM _DatePosted)) AS Earliest FROM Transactions
UNION
SELECT MIN(EXTRACT(YEAR FROM _DatePaid)) FROM Transactions
UNION
SELECT MIN(EXTRACT(YEAR FROM _Date)) FROM Transactions
UNION
SELECT MIN(EXTRACT(YEAR FROM _Acquired)) FROM Assets
UNION
SELECT MIN(EXTRACT(YEAR FROM _Disposed)) FROM Assets
) X

works fine and ignores the null so I'm not really bothered, I'd just like to know.


Roy Lambert

>>should I get a null in the result set?

>>2019
>>2018
>>NULL

Yes. If all the values of "DatePaid" are null.

In which case your customers must be pretty poor!

Adam


>Yes. If all the values of "DatePaid" are null.

Now you say it I remember - its one of the slightly strange (to me) interpretations of how NULL is handled

>In which case your customers must be pretty poor!

Yup - I specially selected and entered test data for how the experts tell us we're going to be after brexit

Roy

Roy,

If all values are NULL what would you expect as MIN() ?

About brexit... you are welcome in Portugal

--
Fernando Dias
[Team Elevate]

Fernando


>If all values are NULL what would you expect as MIN() ?

How about what an accountant would expect?

>About brexit... you are welcome in Portugal

Hmmm - at least you have some nice wine

Roy