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

[CRPence <CRPbottle@xxxxxxxxx>]

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.