Question & Answer
Question
You write a query to select default literal values from the sysdefaults table. The string returned from the query contains two substrings, separated by a space. The first substring is unreadable and the second is the ASCII default value that you expect. How can you write standard SQL queries involving only the ASCII part of the default field?
Cause
The default field of the sysdefaults table contains two substrings for most data types when the record type is literal (L). The first substring contains a 6-bit representation of the default value and this string is not usable in standard SQL queries. This value is separated by a space from the ASCII representation of the default value. As an example, consider the MONEY data type and a default value:
col1 MONEY(10, 2) DEFAULT 0.00
A SELECT on the default column returns the following, which consists of two substrings:
gAAAAAAA 0.00
By contrast, the default field for CHAR, NCHAR, NVARCHAR, VARCHAR, LVARCHAR and BOOLEAN data types contains only the ASCII string representation. As an example, consider the VARCHAR data type and a default value:
col2 VARCHAR(20) DEFAULT 'null_value'
A SELECT on the default column returns this:
null_value
Answer
You will need a stored procedure to process the data in the default field, returning the second (ASCII) substring.
Stored procedure
CREATE PROCEDURE second_string(input CHAR(256))
RETURNING CHAR(256);
DEFINE var1 CHAR(256);
DEFINE var2 INT;
LET var1 = input;
LET var2 = 1;
WHILE var2 = 1
IF substr(var1, 0, 1) = ' ' THEN
RETURN substr(var1, 2);
END IF;
LET var1 = substr(var1, 2);
END WHILE;
END PROCEDURE;
The stored procedure, second_string, successfully processes only fields that contain two substrings. Your query should ensure that the procedure is only called when processing data consisting of both the 6-bit and ASCII representations.
Example query using CASE statement and the stored procedure
SELECT sd.default,
CASE
WHEN sc.coltype = 0
OR
sc.coltype = 13
OR
sc.coltype = 15
OR
sc.coltype = 16
OR
sc.coltype = 40
OR
sc.coltype = 41
THEN
sd.default
ELSE
second_string(sd.default)
END
FROM
sysdefaults sd, syscolumns sc
WHERE
sd.type = 'L'
AND sd.tabid = sc.tabid
AND sd.colno = sc.colno;
Related Information
Was this topic helpful?
Document Information
Modified date:
01 June 2022
UID
swg21456069