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.
<code>
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
....+....1....+....2..
C3 U3 P5
ABC ABC 1
******** End of data ********
</code>
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
midrange.com
