On 06-Nov-2013 04:22 -0800, rob@xxxxxxxxx wrote:
Too bad you can't do something like this
CREATE VIEW QS36F/JIMMYV (MYAMT) AS
SELECT cast(substr(f00002,1,10) as dec(5,2)) as myamt
FROM qs36f/jimmy
-- JIMMY in QS36F not valid for operation. (SQL7008)
If the character string result of the SUBSTR scalar expression was
not always a string of alphanumerics representing a valid numeric
literal\constant [i.e. optional sign followed by three or fewer digits,
followed by an optional decimal separator and more consecutive digits if
that separator was included, followed only by blanks], then that casting
is not going to function. But most likely the physical numeric data in
the S/36 file would not be in that [external\visible] string
representation, rather the numeric data would be stored in one of the
internal representations: INTEGER, PACKED, or ZONED.
Of course casting a packed field from a substring could be fun.
The mapping from a substring of a binary string of data in the
internal Packed BCD form, into a value of a numeric data type with an
SQL expression has been documented many times on this list and
elsewhere; albeit often somewhat incomplete, no matter where. Although
not possible to encapsulate the SELECT in a VIEW, a SQL SELECT can still
operate against the program-described file. Rather than including links
to some old messages, I will include an example below:
Given there is a binary string of data that represents data for a
DEC(19,2) data type value, in the first 10-bytes of the column F00002,
then the following expression would likely suffice; i.e. while ignoring
an invalid sign nibble produces a positive result rather than an error,
invalid digits remain a potential issue:
select
cast( ( Dec( left( hex( substr(F00002, 1, 10) ), 19 ), 19)
* case when
right(hex( substr(F00002, 1, 10) ), 1 ) IN ('D', 'B')
then -1. /* 0xD & 0xB as nibbles -> BCD negative */
else 1. /* assume, ignoring baddta: BCD positive */
end
) / 100.
as decimal(19, 2) )
from jimmy
Or to encapsulate the above expression in a function, a SQL scalar
UDF, for example supporting only up to 31 digits and leaving both the
division to the invoker in order to effect a desired scale and casting
for the desired precision and scale... the following two requests, for
example:
create function VCtoDec31( s varchar(16) )
returns dec(31)
language sql /* could add begin\end and return null on error */
return /* expression could change to _fix_ invalid digits too */
dec( left( hex( s ), int( length( s ) * 2 - 1 ) ), 31 )
* case when right( hex( s ), 1 ) in ('D', 'B') then -1. else 1. end
select
cast( VCtoDec31( substr(F00002, 1, 10) ) / 100.
as decimal(19, 2) )
from jimmy
midrange.com
