I would like to be able to use null as a constant in sql eg

SELECT
CAST(_xTime AS VARCHAR(5) TIME FORMAT 'hh:mm') AS _Time,
CAST(_xDate AS VARCHAR(10) DATE FORMAT 'DD/MM/YYYY') AS _Date,
_TestPoint AS _Point,
_mmolPL AS _Blood,
_Comments AS _Comments,
_Meds
FROM BloodTests
LEFT OUTER JOIN Medication ON Medication._fkBloodTests = _ID
UNION
SELECT
null AS _Time,
_Date AS _Date,
null AS _Point,
_HbA1c AS _Blood,
null AS _Comments
FROM HbA1c

There will be another 4 tables included in the final expression many of which do not have equivalent columns so null is a better substitution than ''



Roy Lambert

Roy,

> I would like to be able to use null as a constant in sql eg

you can do that by casting the null value, e.g.:

SELECT CAST(null as INTEGER) as test, test2 FROM mytable

Uli

Uli


Thanks, that will move me forward, and, as always is the case, now you suggest it I have seen it before but I'd forgotten it.

It does raise an interesting question in my mind. Given the definition of null why should you have to cast it as a specific datatype? Unless its the first statement in the UNION then the parser already knows what the type should be, and objects if any subsequent ones are in error.

Even though this is an acceptable workround I'd still like to be able to just use NULL without casting.

Roy Lambert