On 25-Mar-2012 09:16 , Roger O'Connor wrote:
I have a CHARACTER field which contains ABCUUUnnn
UUU is a UCS-2 set of characters
nnn is packed decimal value

The string in HEX: C1C2C300410042004300001F

I need to make substr(field,4,6) displayable by having SQL convert
the UCS-2 characters to ‘ABC’.

The original assumption is flawed. The data is not type "CHAR". Instead the data is a raw byte stream data, e.g. as type BINARY, or FOR BIT DATA might describe. The SQL has no inherent capability to cast from a lie about the data into legitimately typed data, nor any ability to cast via direct-map from binary data to a data type that would be represented internally by that raw\unmapped data. That string of raw byte-stream data also can not be initially identified as GRAPHIC :-( both because of the odd number of bytes preceding the true UCS2 data, and GRAPHIC does not honor a FOR BIT DATA clause into which the data can be CAST with a CCSID [as can be done with CHAR].

One solution is to write an external function that just re-represents the unmodified raw data as though that byte-stream data is typed-as-desired; i.e. copy raw bytes from input to output location [as defined by the parameter style], with a shift adjustment to the 2-byte variable length of the output.


create function Raw2VG1K /* RawToVarGraphicUCS2_1Kinput */
( rawinpfbd varchar(1000) )
returns vargraphic(500) ccsid 13488
language RPGLE /* a HLL supporting VarGraphic */
deterministic no sql returns null on null input
parameter style sql
specific Raw2VG1K
external name SomeLib/SomePgm /* or srvpgm(Proc) */
with raw (s) as
(select /* mimic the un-described 12 bytes of /record?/ data */
cast(x'C1C2C300410042004300001F' as char(12) for bit data)
from qsys2/qsqptabl
select cast(substr(s, 1, 3) as char(3) ccsid 37) as c3
, cast(raw2vg1k(substr(s, 4, 6)) as vargraphic(3)) as u3
, cast(left(hex(substr(s, 10, 3)), 5) * case when
right(hex(substr(s, 10, 3)), 1) in ('B','D')
then -1 else 1 end as dec(5, 0)) as p5
from raw
; -- report follows; run in a job with a non-hex CCSID
C3 U3 P5
******** End of data ********


Effecting similar [an external UDF to re-represent packed BCD raw data as DECIMAL] for the packed BCD value, instead of coding the HEX() and CASE may be preferable. And even possibly for the CHAR portion.

Regards, Chuck

This thread ...

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2020 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].