Re: Query packed data?

On 23-Feb-2018 15:53 -0700, Charles Wilt wrote:

On 23-Feb-2018 15:33 -0700, Justin Taylor wrote:

I have a program-described PF that contains packed decimal data.
Our Windows manager insists it can be queried directly. The only
way I know how to read packed decimal data is with RPGLE I-specs.
Is there something I'm missing?

https://www.mcpressonline.com/programming/sql/techtip-accessing-packed-data-in-flat-files

SELECT
SUBSTR(CUSTFLAT,4,30)
, DECIMAL(SUBSTR(HEX(SUBSTR(CUSTFLAT,1,3)),1,5),5,0)
* CASE
WHEN SUBSTR(HEX(SUBSTR(CUSTFLAT,3,1)),2,1) ='F'
THEN 1
ELSE -1
END
FROM CUSTFLAT

Of course this gives you a number without decimals...

I'd suggest creating a view over the PF...

An typically offered incomplete, if not also pervasively incorrect "solution"; correct results depend on the (preferred positive and alternative negative representations used for the) origins of the data.

And rather than a VIEW directly coding for each occurrence, the [corrected] long/complex expression, create a SQL FUNCTION to generically perform the conversion; e.g. an example of an invocation mimicking the above example might be coded:

SELECT RawToNumericString(CUSTFLAT, 4, 30)
FROM CUSTFLAT