Troubleshooting
Problem
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.
Was this topic helpful?
Document Information
Modified date:
16 June 2018
UID
swg21637592