How to get a Null value instead of a backslash(\) when unloading an empty varchar string.

Technote (troubleshooting)


Problem(Abstract)

This article tells you how to get a Null value(||) instead of a backslash (|\|) When you unload an empty varchar string.

Symptom

When executing SQL like below:

Example:
unload to x.unl select tabname, "" from systables where tabname = "tab1"

The output file contains:
tab1|\ |

Cause

It is expected behavior as NULL cannot be represented by "", because IBM Informix considers this an empty string internally.

Resolving the problem

Use one of the following workarounds to get || instead of |\ | :


1) SELECT NULL::CHAR FROM table


or

2) SELECT CAST(NULL AS CHAR) FROM table



This simply defines the data type of the result set to return to the client.

Example:

unload to tab1.unl select tabname,CAST(NULL AS CHAR) from systables where tabid = 99;

1 row(s) unloaded.

> !cat t1.unl
VERSION||

or

> unload to t1.unl select tabname,NULL::CHAR from systables where tabid = 99;

1 row(s) unloaded.

> !cat tab1.unl
VERSION||





3) Another option would be to create a stored procedure which returns a CHAR and inside return the NULL value. Something like:

CREATE PROCEDURE ret_null() RETURNING CHAR;
RETURN NULL;
END PROCEDURE;

SELECT ret_null() FROM table



But this is more complex and less efficient.


Rate this page:

(0 users)Average rating

Add comments

Document information


More support for:

Informix Servers

Software version:

11.5, 11.7, 12.1

Operating system(s):

AIX, HP-UX, Linux, Solaris, Windows

Software edition:

Developer, Enterprise, Growth, Innovator, Ultimate, Workgroup

Reference #:

1637592

Modified date:

2013-08-15

Translate my page

Machine Translation

Content navigation