CHAR vs CAST DB2 UDB v8.1.9 Linux

[Bob Stearns]

Vol. 1 of SQL REFERENCE contains the following statement in the
description of CHAR:

Note: The CAST expression can also be used to return a string expression.

I was trying it in order to get rid of leading zeroes when I got the
surprising result that my result set was empty. The only change in the
select was to change

CHAR(t4.dosage)||' '||t5.dosage_units AS dose,

to

CAST(t4.dosage AS CHAR)||' '||t5.dosage_units AS dose,

I realized that this was (maybe) a syntax error since no length was
supplied, but the result is still surprising.

[Knut Stolze]

Bob Stearns wrote:

Vol. 1 of SQL REFERENCE contains the following statement in the
description of CHAR:

Note: The CAST expression can also be used to return a string expression.

I was trying it in order to get rid of leading zeroes when I got the
surprising result that my result set was empty. The only change in the
select was to change

CHAR(t4.dosage)||' '||t5.dosage_units AS dose,

to

CAST(t4.dosage AS CHAR)||' '||t5.dosage_units AS dose,

I realized that this was (maybe) a syntax error since no length was
supplied, but the result is still surprising.

If you say CHAR (or CHARACTER), then this is equivalent to CHAR(1), i.e. a
string with exactly one character. You need to provide the length in the
CAST.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

[Bob Stearns]

Knut Stolze wrote:

Bob Stearns wrote:

Vol. 1 of SQL REFERENCE contains the following statement in the
description of CHAR:

Note: The CAST expression can also be used to return a string expression.

I was trying it in order to get rid of leading zeroes when I got the
surprising result that my result set was empty. The only change in the
select was to change

CHAR(t4.dosage)||' '||t5.dosage_units AS dose,

to

CAST(t4.dosage AS CHAR)||' '||t5.dosage_units AS dose,

I realized that this was (maybe) a syntax error since no length was
supplied, but the result is still surprising.

If you say CHAR (or CHARACTER), then this is equivalent to CHAR(1), i.e. a
string with exactly one character. You need to provide the length in the
CAST.

But why does it cause the result set to be empty with no error?

[Knut Stolze]

Bob Stearns wrote:

Knut Stolze wrote:

Bob Stearns wrote:

Vol. 1 of SQL REFERENCE contains the following statement in the
description of CHAR:

Note: The CAST expression can also be used to return a string expression.

I was trying it in order to get rid of leading zeroes when I got the
surprising result that my result set was empty. The only change in the
select was to change

CHAR(t4.dosage)||' '||t5.dosage_units AS dose,

to

CAST(t4.dosage AS CHAR)||' '||t5.dosage_units AS dose,

I realized that this was (maybe) a syntax error since no length was
supplied, but the result is still surprising.

If you say CHAR (or CHARACTER), then this is equivalent to CHAR(1), i.e.
a
string with exactly one character. You need to provide the length in the
CAST.

But why does it cause the result set to be empty with no error?

Well, I get a warning message:

$ db2 "values cast(decimal(12.3, 5, 3) as char) || ' '"

1
--
1
SQL0445W Value "12.300 " has been truncated. SQLSTATE=01004

1 record(s) selected with 1 warning messages printed.

Maybe your application does not handle warning messages properly or not at
all?

--
Knut Stolze
DB2 Information Integration Development
IBM Germany