When doing a describe command on a select query, the result data
type in a VARCHAR2 enabled database may be VARCHAR instead of
the expected CHAR.
Example:
----------------------------------------------------------------
C:\>db2 create table test(c1 int)
DB20000I The SQL command completed successfully.
>> A) Output from the describe select command with the issue:
C:\>db2 "describe select 'Y' as flag1, case when 1 = 1 then 'Y'
else 'N' end as flag2 from test"
Column Information
Number of columns: 2
SQL type Type length Column name
Name
length
-------------------- -----------
------------------------------ -----------
452 CHARACTER 1 FLAG1
5
448 VARCHAR 1 FLAG2
5
>> B) Expected output :
C:\>db2 "describe select 'Y' as flag1, case when 1 = 1 then 'Y'
else 'N' end as flag2 from test"
Column Information
Number of columns: 2
SQL type Type length Column name
Name length
-------------------- -----------
------------------------------ -----------
452 CHARACTER 1 FLAG1
5
452 CHARACTER 1 FLAG2
5
The effects of setting the varchar2_compat database
configuration parameter to ON are as follows.
Rules for result data types involving character strings are
modified as follows:
If one operand is... And the other operand is... The
data type
of the result is...
CHAR(x) CHAR(x) CHAR(x)
CHAR(x) CHAR(y) VARCHAR(z) where z = max(x,y)
Rules for result data types involving graphic strings are
modified as follows:
If one operand is... And the other operand is... The
data type
of the result is...
GRAPHIC(x) GRAPHIC(x) GRAPHIC(x)
GRAPHIC(x) GRAPHIC(y) VARGRAPHIC(z) where z = max(x,y)
Rules for result data types involving character and graphic
strings are modified as follows:
If one operand is... And the other operand is... The
data type
of the result is...
GRAPHIC(x) CHAR(y) VARGRAPHIC(z) where z = max(x,y)
The functions that return character string arguments, or that
are based on parameters with character string data types, also
treat empty string CHAR, NCHAR, VARCHAR, or NVARCHAR values as
null values. Special considerations apply for some functions
when the varchar2_compat database configuration parameter is set
to ON, and these are listed here.
DECODE: If the first result expression is an untyped null it
is assumed to be VARCHAR(0). If the first result expression is
CHAR or GRAPHIC, it is promoted to VARCHAR or VARGRAPHIC.
GREATEST: If the first expression is CHAR or GRAPHIC, it is
promoted to VARCHAR or VARGRAPHIC.
LEAST: If the first expression is CHAR or GRAPHIC, it is
promoted to VARCHAR or VARGRAPHIC.
NVL: If the first expression is CHAR or GRAPHIC, it is
promoted to VARCHAR or VARGRAPHIC.
NVL2: If the result expression is an untyped null it is
assumed to be VARCHAR(0). If the result expression is CHAR or
GRAPHIC, it is promoted to VARCHAR or VARGRAPHIC. |
