![]() | ![]() Products ![]() ![]() ![]() ![]() |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 6 of 6 total |
![]() |
Wed, May 26 2010 10:40 AM | Permanent Link |
Roy Lambert NLH Associates ![]() | Can you allow NULLs to be part of a SELECT please. I'd like to be able to do things like:
SELECT W._Surname AS Surname, W._Forename AS Forename, _fkContacts AS ContactID, _ID AS CareerID, C._Name AS Company, _JobTitle AS Job FROM Career LEFT JOIN Companies C ON C._ID = _fkCompanies LEFT JOIN Contacts W ON W._ID = _fkContacts WHERE _EMail = :FindEMail UNION SELECT _Surname AS Surname, _Forename AS Forename, _ID AS ContactID, NULL AS CareerID, <<<<<<<< currently using -1 AS CareerID NULL AS Company, <<<<<<<< currently using '' AS Company NULL AS Job <<<<<<<<< currently using '' AS Company FROM Contacts WHERE _HomeEMail = :FindEMail ORDER BY Surname, Forename The first statement can return NULLs but the second can't Roy Lambert |
Thu, May 27 2010 5:55 AM | Permanent Link |
John Hay | Roy
> Can you allow NULLs to be part of a SELECT please. I'd like to be able to do things like: How about CAST(NULL AS Integer), CAST(NULL AS Char), CAST(NULL AS Char), John |
Thu, May 27 2010 6:14 AM | Permanent Link |
Roy Lambert NLH Associates ![]() | John
That works. I find it baffling because ElevateDB seems to know what column type is expected so why a CAST? Roy Lambert |
Thu, May 27 2010 8:20 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. ![]() | Roy,
<< That works. I find it baffling because ElevateDB seems to know what column type is expected so why a CAST? >> Because of type promotion. You can combine INTEGER types with SMALLINT types, for example, as far as ElevateDB is concerned: SELECT CAST(CustNo AS INTEGER) FROM customer UNION ALL SELECT CAST(CustNo AS SMALLINT) FROM customer See here for more info: http://www.elevatesoft.com/manual?action=viewtopic&id=edb2sql&topic=Type_Promotion -- Tim Young Elevate Software www.elevatesoft.com |
Thu, May 27 2010 9:17 AM | Permanent Link |
Roy Lambert NLH Associates ![]() | Tim
>Because of type promotion. You can combine INTEGER types with SMALLINT >types, for example, as far as ElevateDB is concerned: Having read the link I come to the conclusion that NULL can't be promoted. Is that right? Roy Lambert |
Thu, May 27 2010 5:35 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. ![]() | Roy,
<< Having read the link I come to the conclusion that NULL can't be promoted. Is that right? >> NULL has no type, so it doesn't really factor into type promotion until you use CAST() to "give" it a type, after which the type you "give" it is used in type promotion considerations. -- Tim Young Elevate Software www.elevatesoft.com |
This web page was last updated on Monday, July 15, 2024 at 07:03 PM | Privacy Policy![]() © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? ![]() |