Re: SQL: how to filter out spaces in fields defined as packed numeric

On 11 Feb 2013 09:05, rob@xxxxxxxxx wrote:

Jeff Young on 02/11/2013 11:45 AM wrote:

Try this:
SELECT f1.f2.etc.
from Datafile
where substr(OrderQty, 1, 1) = x'40'
Replace the f1,f2,etc with field names from the file, but not the
OrderQty which SQL will see as invalid data.

Does SQL support SUBSTR on an numeric?

Yes. That is available via *implicit cast* to character [since v5r2 IIRC]. Of course CAST scalar or any of the casting scalars CHAR, VARCHAR, or DIGITS could effect that explicitly.... and to ensure changes to OrderQty precision and scale do not impact the expression, the OrderQty column would best be cast to a known precision and scale for most string manipulations; the effective left() would be OK without a fixed precision, except that the even-precision versus odd-precision Packed presents its own issue.

You could write a UDF to read this column
select columna, columnb, myRpgUdfToConvertThisToNumeric(columnc)...

The column is already numeric in the given scenario.

Hopefully that won't process it before it passes it out to the UDF.

If columnc in the above UDF expression is a numeric with invalid decimal data, the SQL will diagnose the problem before the UDF ever gets invoked. Although I do recall some changes discussed, I think IBM i 7.1, with how decimal data errors are handled or how some control is offered. The HEX() scalar has always been required to reference a column with bad [decimal] data without getting an error.

<<SNIP>>
If you can overlay the numeric with a character by the use of a view
perhaps you clean it up with a common table expression first.

A VIEW only allows CAST from valid types and representations; i.e. there is no "overlay" capability to reference as effectively untyped some bytes of the row data. And again, a UDF has the issue that the data must be valid, unless HEX() scalar is used to pass the data. Unfortunately there is no RAW() scalar to give the raw code points for the internal representation without either of type validation for the column data or having to pass the data as a string in hex notation.

I do not know if field procedures could assist in any way; I would have to read about them again, because I have no practical experience using them.

Not that you want to hear this but I just have to pipe in that if
you created this file with SQL's DDL instead of DDS you wouldn't be
having this issue.

That... Or fix the data and any programs that cause the invalid decimal data to at least reduce the number of affected rows.