[ACCEPTED]-Informix: Select null problem-informix

Accepted answer

Score: 24

You don't have to write a stored procedure; you 17 simply have to tell IDS what type the NULL 16 is. Assuming you are not using IDS 7.31 15 (which does not support any cast notation), you 14 can write:

SELECT NULL::INTEGER FROM dual;

SELECT CAST(NULL AS INTEGER) FROM dual;

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

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

The 'sysdual' table was added 11 relatively recently (IDS 11.10, IIRC), so 10 if you are using an older version, it won't 9 exist. The following works with any version 8 of IDS - it's what I use.

-- @(#)$Id: dual.sql,v 2.1 2004/11/01 18:16:32 jleffler Exp $
-- Create table DUAL - structurally equivalent to Oracle's 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 7 you are going to SELECT from Systables to 6 get a single row, you should include 'WHERE tabid = 1'; this 5 is the entry for Systables itself, and if 4 it is missing, the fact that your SELECT 3 statement does return any data is the least 2 of your troubles. (I've never seen that 1 as an error, though.)

Score: 5

This page says the reason you can't do that is because 7 "NULL" doesn't have a type. So, the 6 workaround is to create a sproc that simply 5 returns NULL in the type you want.

That sounds 4 like a pretty bad solution to me though. Maybe 3 you could create a variable in your script, set 2 it to null, then select that variable instead? Something 1 like this:

DEFINE dummy INT;
LET dummy = NULL;

SELECT group_ser, item_ser, dummy
FROM sometable

Score: 3

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

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

Score: 2

Is there any reason to go for an actual 1 table? I have been using

select blah from table(set{1})

Score: 1

select blah from table(set{1})

is nice when you are using 10.x database. This 6 statement doesn't touch database. The amount 5 of read/write operations is equal to 0,

but

when 4 you're using 11.x it will cost you at least 3 4500 buffer reads because this version of 2 Informix creates this table in memory and 1 executes query against it.

Score: 0

select to_date(null) from table;

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

Score: 0

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

This 7 (''+1.0001) evaluates to DECIMAL(16,4). And 6 so on.

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

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

To 3 obtain NULL value of type VARCHAR(1) use 2 this expression: DATE(''+1)||' '

Works in 1 9.x and 11.x.

Source: stackoverflow.com

[ACCEPTED]-Informix: Select null problem-informix

How to see queries executing on Informix DB

Show a one to many relationship as 2 columns - 1 unique row (ID & comma separated list)

How do I list all stored procedures in Informix?

Insert into a Informix table or update if exists

Informix datetime: How to subtract 15 Minutes

MySQL IN with LIKE

How to get running sum of a column in sql server

Update Table with a "Select query" with a where clause

What is better? Subqueries or inner joining ten tables?

Select all if parameter is null in stored procedure