IBM Support

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

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.

[{"Product":{"code":"SSGU8G","label":"Informix Servers"},"Business Unit":{"code":"BU053","label":"Cloud & Data Platform"},"Component":"--","Platform":[{"code":"PF002","label":"AIX"},{"code":"PF010","label":"HP-UX"},{"code":"PF016","label":"Linux"},{"code":"PF027","label":"Solaris"},{"code":"PF033","label":"Windows"}],"Version":"11.5;11.7;12.1","Edition":"Developer;Enterprise;Growth;Innovator;Ultimate;Workgroup","Line of Business":{"code":"","label":""}}]

Document Information

Modified date:
16 June 2018

UID

swg21637592