NULL computed field

Is there any way to make a NULL computed field? Specifically I have a union in which I want to make a corresponding field null. Ex: SELECT informix.leave.effective_return_date, "" as cleared, "" as absent, "Y" as Resident FROM informix.leave UNION SELECT NULL as effective_return_date, **** where I want the null **** informix.morning_attendence_check.mac_cleared, "" as absent, "Y" as Resident FROM informix.morning_att_check Sent via Deja.com http://www.deja.com/ Before you buy.

wgoldenarm@my-deja.com wrote: > Is there any way to make a NULL computed field? > > Specifically I have a union in which I want to make a corresponding > field null. Ex: > > SELECT informix.leave.effective_return_date, > "" as cleared, > "" as absent, > "Y" as Resident > FROM informix.leave > UNION > SELECT NULL as effective_return_date, **** where I want the null **** > informix.morning_attendence_check.mac_cleared, > "" as absent, > "Y" as Resident > FROM informix.morning_att_check It depends in part on the type of NULL you need. You could generate a numeric null by using (SELECT SUM(tabid) FROM "informix".systables WHERE tabid < 0) where you want the NULL. I'd guess that the MAX or MIN of an empty set is also NULL (it sounds SQL-ish and is the sort of thing that would send C J Date into fits), so you can probably use: (SELECT MIN(created) FROM "informix".systables WHERE tabid < 0) to generate a NULL date. In fact, for the numeric columns you'd probably be better off with MIN or MAX since they won't alter the data type on you, whereas an aggregate like SUM can return a DECIMAL(32) for the sum of a set of INTEGER values (primarily to avoid overflow). But this is kinda tricky... -- Jonathan Leffler (jleffler@informix.com, jleffler@earthlink.net) Guardian of DBD::Informix v0.62 -- see http://www.perl.com/CPAN #include <disclaimer.h>