0

This one really has me scratching my head. This statement:

SELECT CODE FROM CODES_TABLE WHERE STATUS = 0 AND PERCENT = 30

works just fine, but this one:

SELECT CODE FROM CODES_TABLE WHERE STATUS = 0 AND PERCENT = 20

Gives me the

DB2 SQL Error: SQLCODE=-420, SQLSTATE=22018, SQLERRMC=DECFLOAT, DRIVER=3.57.82

error message, which I understand indicates that the value for PERCENT is malformed. PERCENT has a type of DECIMAL. Anyone have any ideas? 10 also fails, but all numbers above 30 work fine.

Even better, 

SELECT CODE FROM CODES_TABLE WHERE STATUS = 0 AND PERCENT < 31 AND PERCENT > 29 works just fine but

SELECT CODE FROM CODES_TABLE WHERE STATUS = 0 AND PERCENT < 21 AND PERCENT > 19

yields the same error

Keith Engle
  • 1
  • 1
  • 1
  • 5
  • Are you querying two different tables, or just not showing the actual code? You should also provide the table definition. – mustaccio Sep 25 '13 at 01:16
  • Table definition: CODE CHARACTER(15) NOT NULL, PERCENT DECIMAL(5,2) NOT NULL, STATUS CHARACTER(10), USEDATE TIMESTAMP, USEDBY BIGINT, REASON VARCHAR(255), CONSTRAINT P_CODES_TABLE PRIMARY KEY (CODE) – Keith Engle Sep 25 '13 at 01:26
  • 1
    My guess would be that some STATUS values cannot be converted to numbers. This would include spaces, empty strings etc. – mustaccio Sep 25 '13 at 01:55
  • I'll be darned. I was so focused on the PERCENT field I didn't even notice that STATUS is character. As soon as I put the 0 in single quotes, the statement worked fine. Thanks so much! – Keith Engle Sep 25 '13 at 02:02

0 Answers0