I want to filter out the rows containing dashes in the following query. The PRMWT column is DECIMAL (9, 3).
Query
SELECT
PPA#,
PRMWT
FROM FUTMODS.SCPMSTQ
WHERE PPA# = '20829161'
Result
PPA# PRMWT
20829161 53.513
20829161 -
20829161 -
20829161 -
20829161 -
20829161 -
If I add the condition AND PRMWT IS NOT NULL, the result is the same and I get this warning:
SQL State: 01565 Vendor Code: 802 Message: [SQL0802] Data conversion or data mapping error. Cause . . . . . : Error type 6 has occurred. Error types and their meanings are: 1 -- Arithmetic overflow. 2 -- Floating point overflow. 3 -- Floating point underflow. 4 -- Floating point conversion error. 5 -- Not an exact result. 6 -- Numeric data that is not valid. 7 -- Double-byte character set (DBCS) or UTF-8 data that is not valid. 8 -- Division by zero. 9 -- Hash value cannot be computed for the requested query. 10 -- User-defined function returned a mapping error. 11 -- Not valid length found in a varying-length column returned from an array result set. 12 -- Result of a concatenation operation on a varying-length field exceeded the maximum allowed length of the result type. If the error occurred when assigning a value to a host variable of a FETCH, embedded SELECT, SET, or VALUES INTO statement, the host variable name is *N and the relative position of the host variable in the INTO clause is 2. If the host variable name is *N, the error occurred when attempting to resolve a search condition. If more than one data mapping error occurred, this is a description of the first error that occurred. For a description of any other data mapping errors, see the previously listed messages in the job log. Recovery . . . : The error was caused by data that was not valid or that was too large. Look at the previously listed messages in the job log (DSPJOBLOG command) or press F10 (Display messages in job log) on this display to determine what row and columns were involved in the error. Correct the data and then try the request again.
Upon closer inspection, I see the column has Nullable = No. Ok, but then why does it have something non-numeric in it (the hyphen)?
I also tried PRMWT > 0, PRMWT > 0.0, PRMWT > '000000.000', PRMWT != '-', PRMWT != '', PRMWT = CAST(0.0 AS DECIMAL(9,3)), CAST(PRMWT AS FLOAT) > 0.0, etc.
I'm lost. What the heck does a hyphen signify in this godforsaken DB engine and how do I operate on it? The OS is version 7.2.
use the hex function to look for decimal data errors in the PRMWT field:
SELECT PPA#, hex(PRMWT) hex_prmwt
FROM FUTMODS.SCPMSTQ
WHERE PPA# = '20829161'
to correct the bad data you can test for specific hex values and use SQL UPDATE to set the field to 0.
update FUTMODS.SCPMSTQ
set PRMWT = 0
where hex(PRMWT) = '404040'
Here is a good article: https://www.itjungle.com/2013/08/07/fhg080713-story02/