Informix: Select Null Problem

Informix: Select null problem

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

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

DEFINE dummy INT;
LET dummy = NULL;

SELECT group_ser, item_ser, dummy
FROM sometable

Selecting null in Informix SQL

You need to specify which DATETIME type. Assuming modify_date is a DATETIME YEAR TO DAY, then you need to write:

SELECT CASE
       WHEN <some condition>
       THEN MAX(modify_date)
       ELSE NULL::DATETIME YEAR TO DAY -- or CAST(NULL AS DATETIME YEAR TO DAY)
       END
  FROM <table>

If the modify_date is actually DATETIME YEAR TO SECOND or something similar, change the cast accordingly. However, you need <t1> TO <t2> after DATETIME.

You might still run into an issue with 'aggregate vs non-aggregate' in the CASE expression. You might be better off with:

SELECT MAX(CASE
           WHEN <some condition>
           THEN modify_date
           ELSE NULL::DATETIME YEAR TO DAY
           END)
  FROM <table>

Or there might be another way to deal with it. It depends on what you're expecting as output. The aggregate outputs one row for the entire table; the null is a 'row per row' (which is why I think there'll be trouble). You don't specify a GROUP BY clause.

How to add an additional fake null column to select result in Informix?

Cast the NULL to the desired type:

SELECT CURRENT AS col1, NULL::INTEGER AS col2
  FROM sysmaster:"informix".sysdual;

Blanks causing problem while fetching rows (Informix)

You should be using a join to collect the data from the two tables in one operation. DBMS are very good at doing joins; when you do joins manually in the application, you slow things down, usually dramatically.

Data types are critical - and unspecified in the question.

SQL Types

• VARCHAR(n) - trailing blanks are significant; the zero length string is not NULL.
• CHAR(n) - trailing blanks are added by the DBMS and can be removed by applications.

ESQL/C Types:

• char - for CHAR data; blank padded to full length with NUL '\0' terminator.
• fixchar - for CHAR data; blank padded to full length without NUL '\0' terminator.
• varchar - for VARCHAR data; not blank padded.
• Pointers to these types can also be used, complicating matters again (the compiler doesn't know how big the space is that the pointer points at).

You also mention using '\' to load data. That is a special notation used with zero-length non-null VARCHAR fields to indicate that value; the empty field indicates a NULL value in the unload format. (On disk, an empty non-null VARCHAR occupies one byte, value 0x00 for length zero; a NULL VARCHAR occupies two bytes, values 0x01 0x00 for a length of 1 and a NULL (or NUL) value.)

Judging from what you say, you have possibly empty (but not NULL) VARCHAR(n) values in the second table. Those should show up as string values where the first byte is NUL '\0' in your C code - regardless of which of the variable types you use. The output from GDB is consistent with that; the first byte is '\0' (or '\000'); the rest is irrelevant.

You may want to look up indicator variables; these tell you about whether a particular value selected from the DB is NULL or not.

If you still have problems, post the code (preferably a very small compilable program that shows the problem - say up to 50 lines or so; or a small fragment of the code - say 20 lines or so).

informix replace null value with 0 in select

At first you should create simple query with simple db table, some values and requested result that clearly shows your problem.

Part 1: I want replace null value with 0

To change NULL into some value you can use NVL() function. Documentation says: The NVL expression returns different results, depending on whether its first argument evaluates to NULL.

Examples:

SELECT fld, NVL(fld, 0) FROM ...

Part 2: and if not null i want to count the data

SELECT COUNT(*) FROM ... WHERE fld IS NOT NULL

Selecting all rows from Informix table containing some null columns

After a balked attempt at installing DBD::Informix I came back around to this and found that for some reason enabling LongTruncOk on the database handle did allow all rows including those with null columns to be selected. I don't imagine this is the root of the issue but it worked here.

However, this solution seems to have collided with an unrelated tweak to locales to support non-ascii characters. I added DB_LOCALE=en_us.utf8 and CLIENT_LOCALE=en_us.utf8 to my connection string to prevent selects from similarly breaking when encountering non-ascii characters (i.e., in a result set of say 500 where the 300th row had a non-ascii character the trailing 200 rows would not be returned). With locales set this way as well as LongTruncOk enabled on the dbh all rows are being returned (without the NVL hack), but null columns have bytes added to them from previous rows, and not in any pattern that is obvious to me. When I leave the locale settings off of the connection string and set LongTruncOk, rows with null columns are selected correctly but rows with utf characters break.

So if you don't have a charset issue perhaps just LongTruncOk would work for you. For my purposes I have had to continue using the NVL workaround for nulls and specify the locales for characters.

Using JDBC Driver for Informix + SQLException being thrown when NULL Value is encountered

I think problem is in line:

 distCaseDefNumStr = rsDist.getObject("colname2").toString();

and with similar lines where getObject() can return null.

If your colname2 has null value then getObject() returns null and then Java tries to run toString() method on null object.

Yes, the message about column not found in result is strange, but I think that you really observe null pointer exception. Use getString() or getInt() methods of ResultSet.

---

Related Topics