RE: How to show Null expression in SQL query ?

On Mon, 14 Dec 1998 rfurdzik@paulweiss.com wrote: > <<1. You could create a procedure that returns a NULL value. > > Is it necessary to create one procedure for each data type. It looks > that for the compiler NULL from date is different than NULL from Integer. > I tried to use Date(NULL) when integer was expected, but this did not > work . You can convert from CHAR to any other type except BYTE and TEXT, so you could (should be able to) use: CREATE PROCEDURE null_value() RETURNING CHAR(1); RETURN NULL; END PROCEDURE; SELECT ..., null_value(), ... I haven't formally tested this, but I'd be surprised if it did not work on its own. These days, the requirements for union compatability have been relaxed and it may be adequate for that too (once upon a time, you could not use "SELECT 'A' FROM ... UNION SELECT 'BC' FROM ..." because the types did not match), but I'm not guaranteeing it -- in fact, I suspect it will not work. If it doesn't, then you are probably back to separate procedures for each type. Beware, there are in theory over 300 different DATETIME and INTERVAL types, though you'd probably be able to reduce the number of stored procedures to many fewer than that. You'd need separate procedures for BYTE and TEXT blobs, and IDS/UDO throws another chunk of uncertainty into the equation. > <<2. Prior to IDS 7.3 I used to join with systables where tabid=1 and > << use the site column for a NULL value. ...which worked because it was a CHAR field. > I guess there will be the same problem. What should be site column name I > should use? Relying on the contents of a particular column in the system catalogue is a trifle iffy. > From: "Bernstein, Rick" <rbernste@alarismed.com> on 12/11/98 05:07:09 PM > To: Rafal Furdzik/PaulWeiss > > Several options are available: > 1. You could create a procedure that returns a NULL value. > 2. You could create a table which has 1 row and columns with NULL values. > 3. Prior to IDS 7.3 I used to join with systables where tabid=1 and > use the site column for a NULL value. > > -----One Previous Message----- > From: rfurdzik@paulweiss.com [mailto:rfurdzik@paulweiss.com] > Sent: Friday, December 11, 1998 13:57 > > I need to create an union query, so number and type of column must be > the same. I want to replace missing columns with NULL's, so client > application will show nothing for those missing columns. > > From: "Bernstein, Rick" <rbernste@alarismed.com> on 12/11/98 04:40:32 PM > > With IDS 7.3 you can return NULL from a case construct: > SELECT tabid, > CASE WHEN 1=1 THEN NULL END AS col1 > FROM systables > WHERE tabid = 1;> > -----Original Message----- > From: rfurdzik@paulweiss.com [mailto:rfurdzik@paulweiss.com] > Sent: Friday, December 11, 1998 13:24 > > Does anybody knows how to show Null expression in SQL query ?. I tried > something like this: > "SELECT NULL As col1 FROM... " , but it did not work. Any idea ? I'll gladly destroy this pointless footnote! I'm not sure whose it is, but it is a waste of bandwidth in a news group, and doubly so when repeated as it was in the original message. > This message is intended only for the use of the > Addressee and may contain information that is > PRIVILEGED and CONFIDENTIAL. If you are not the > intended recipient, you are hereby notified that > any dissemination of this communication is > strictly prohibited. If you have received this > communication in error, please erase all copies of > the message and its attachments and notify us > immediately. Thank You. ...zap... @!##@!@! ZAP, ZAP, ZAP, ZAP @!#!#@$%$!% Uh oh, it seems to be a viral infection and indestructible :-( :-) Yours, Jonathan Leffler (jleffler@informix.com) #include <witticism.h> Guardian of DBD::Informix v0.60 -- http://www.perl.com/CPAN Informix IDN for D4GL & Linux -- http://www.informix.com/idn