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

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

John


That works. I find it baffling because ElevateDB seems to know what column type is expected so why a CAST?

Roy Lambert

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

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

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