Re: Why can I CAST from 65535 to 37, but not from 65535 to 1200 or 13488?

On 05-Aug-2015 07:55 -0600, Charles Wilt wrote:

Was trying to help this poster to Stack Exchange..
[https://stackoverflow.com/questions/31819088/db2-sql-interpret-a-field-as-other-ccsid]

I created a test file with a single field named test with a CCSID
of 13488.

The DB is perfectly happy with this:

SELECT hex(substr(test,7,3))
, cast(substr(test,7,3) as char (3) CCSID 37)
FROM wiltc/test

But this:

SELECT hex(substr(test,1,6))
, cast(substr(test,1,6) as nchar)
FROM wiltc/test

returns SQL0332 - Character conversion between CCSID 65535 and CCSID
1200 not valid

This also returns an SQL0332

SELECT hex(substr(test,1,6))
, cast(substr(test,1,6) as char (6) CCSID 13488)
FROM wiltc/test


It seems to me that if the DB is going to allow any cast from 65535,
it should allow them all.

The /default encoding/ is EBCDIC, so the /hex/ into EBCDIC is given a pass, whereas the request to go from /hex/ to ASCII is not; formally, probably both should be disallowed. Either doubly cast, or create an effective direct-map external UDF that does nothing with the data and merely assigns the RETURNS type.

For the double-map, the second vs the first of these two:

• cast(test as graphic(6) ccsid 13488 )

• cast(cast(test as char(6) ccsid 37) as graphic(6) ccsid 13488 )

Sadly there is still no /direct-map/ Scalar Function modeled after the CAST function as a non-standard but extremely useful feature long lacking in the SQL for remapping effectively un-described [or multi-described] /legacy/ data.

FWiW I recall [perhaps incorrectly,] reading something about relaxing of the rules for binary to ASCII; thinking probably that was in IBM i 7.2 however. I did not check the supported mappings table in the docs, but there is such a table that should have been updated if there was a change.