Re: Packed Numeric stored in Char field

On 3/27/2024 8:26 AM, Daniel Gross wrote:

[...]
Another idea is using SQLs built in INTERPRET function?

SELECT INTERPRET(idfl03 AS DECIMAL(9, 8) FROM ...

This should also work.

I had not yet encountered the INTERPRET function, so I checked it out. As coded above, it failed:

[SQL0171] Argument 1 of function INTERPRET not valid.

I looked in the IBM i documentation and found this:

string-expression
An expression that returns a built-in binary string or character FOR BIT
DATA string that contains the internal IBM® i representation of
/data-type/. The length of /string-expression/ must follow the rules in
Table 1
<https://www.ibm.com/docs/en/i/7.4?topic=functions-interpret#rbafzscainterpret__rules>.

So I tried again as this:

SELECT INTERPRET(CAST(idfl03 AS CHAR(5) FOR BIT DATA) AS DECIMAL(9, 8) FROM...

That worked.