Informix: select null error

Question

Using Informix, I created a tempory table that I am trying to populate from a select statement. After that, I want to do an update to populate more fields in the pace table.

So, I am doing something like:

create temp table _results (group_ser int, item_ser int, restype char(4)); insert into _results (group_ser, item_ser) select group_ser, item_ser, null from sometable 

But you cannot select zero.

For instance:

 select first 1 current from systables 

works but

 select first 1 null from systables 

fails!

(Don't make me start by why I can't just do SQL Server, for example, "select current" without specifying a table!)

Answer 1

This page talks about the reason you cannot do this because "NULL" is not of type. So the workaround is to create a sproc that simply returns NULL in the form you want.

This sounds like a very bad decision for me. Perhaps you could create a variable in your script, set it to null, and then select that variable? Something like that:

 DEFINE dummy INT; LET dummy = NULL; SELECT group_ser, item_ser, dummy FROM sometable 

Answer 2

You do not need to write a stored procedure; you just need to tell IDS which type is NULL. Assuming you are not using IDS 7.31 (which does not support any letter notation), you could write:

 SELECT NULL::INTEGER FROM dual; SELECT CAST(NULL AS INTEGER) FROM dual; 

And if you don't have dual as a table (you probably don't), you can do one of several things:

 CREATE SYNONYM dual FOR sysmaster:"informix".sysdual; 

The "sysdual" table was added relatively recently (IDS 11.10, IIRC), so if you are using an older version, it will not be. The following works with any version of IDS - this is what I use.

 -- @(#)$Id: dual.sql,v 2.1 2004/11/01 18:16:32 jleffler Exp $ -- Create table DUAL - structurally equivalent to Oracle similarly named table. -- It contains one row of data. CREATE TABLE dual ( dummy CHAR(1) DEFAULT 'x' NOT NULL CHECK (dummy = 'x') PRIMARY KEY ) EXTENT SIZE 8 NEXT SIZE 8; INSERT INTO dual VALUES('x'); REVOKE ALL ON dual FROM PUBLIC; GRANT SELECT ON dual TO PUBLIC; 

Idiomatically, if you are going to SELECT from Systables to get a single row, you should include ' WHERE tabid = 1 '; this is the record itself for Systables, and if it is missing, then the fact that your SELECT statement returns any data is the least of your problems. (I never saw this as a mistake.)

Answer 3

 SELECT group_ser, item_ser, replace(null,null) as my_null_column FROM sometable 

or you can use nvl(null,null) to return null for your select statement.

Answer 4

Is there any reason for the actual table? I used

 select blah from table(set{1}) 

Answer 5

 select blah from table(set{1}) 

nice when you use the 10.x database. This expression does not apply to the database. The number of read / write operations is 0,

but

when you use 11.x, it will cost you at least 4,500 buffers because this version of Informix creates this table in memory and queries against it.

Answer 6

 select to_date(null) from table; 

This works when I want to get a date with a null value

Answer 7

You can use this expression ('' +1) in the SELECT list instead of the null keyword. It evaluates the NULL value of type DECIMAL (2,0).

This ('' +1.0001) evaluates to DECIMAL (16.4). And so on.

If you want the DATE type to use DATE ('' + 1) to get a null value of type DATE.

('' + 1) || "'evaluates an empty string of type VARCHAR (1).

To get a NULL value of type VARCHAR (1), use this expression: DATE ('' + 1) || "

Works in 9.x and 11.x.