On 11 Feb 2013 09:05, rob@xxxxxxxxx wrote:
Jeff Young on 02/11/2013 11:45 AM wrote:
Does SQL support SUBSTR on an numeric?
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.
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.
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
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.