Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 5 of 5 total |
MIN and NULL |
Sat, Jul 13 2019 11:10 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Sun, Jul 14 2019 4:55 AM | Permanent Link |
Adam Brett Orixa Systems | >>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! |
Sun, Jul 14 2019 8:06 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Tue, Jul 23 2019 9:22 AM | Permanent Link |
Fernando Dias Team Elevate | Roy,
If all values are NULL what would you expect as MIN() ? About brexit... you are welcome in Portugal -- Fernando Dias [Team Elevate] |
Wed, Jul 24 2019 4:33 AM | Permanent Link |
Roy Lambert NLH Associates 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 |
This web page was last updated on Sunday, May 19, 2024 at 08:46 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |