CODEWITHSUNDEEP
sqloracle-databaseora-01722
M. Phys
M. Phys

Reputation: 147

ORA-01722: invalid number, but removing DECODE statement returns results

I have a SQL query (below) where I'm getting an ORA-01722: invalid number error. Pretty common.

SELECT pt.DISPLAYNAME PARAM_NAME,
  DECODE(rp.PARAMTYPE, 'POVLOC', l.PARTNAME, p.PARAM_VALUE) PARAM_VALUE
FROM schema2.OTHER_PARAMS p
JOIN schema2.TPARAMETER rp
  ON rp.R_ID = 10230
 AND p.PARAM_NAME = rp.PARAMNAME
JOIN schema2.TPARAMETER_TL pt
  ON rp.PARAMID = pt.PARAMID
 AND pt.LANGUAGE = 'en'
LEFT JOIN schema2.TPARTITION l
  ON l.PARTITION = p.PARAM_VALUE
WHERE p.F_ID = 3669
ORDER BY rp.SEQ

Once I remove the DECODE statement and just have the PARAMTYPE column I get results. 1 column with PARAM_NAME (DISPLAYNAME column), the other with PARAM_VAL (PARAMTYPE column). All the datatypes in the DECODE are Varchar, so there are no conversions from number to char or vice versa. That's usually the most common reason this invalid number error occurs.

If it's not clear from above, what I want to do is insert some if/then logic on the PARAMTYPE column to update it. I was thinking about using a CASE statement for the PARAMTYPE variable, but I'm not sure what the best way to go about it is.

Upvotes: 0

Views: 1467

Answers (1)

camoreira
camoreira

Reputation: 26

If your DECODE result values have both number and varchar, then you should force the first one to be a varchar to avoid convertion errors. In your case, just replace l.PARTNAME by TO_CHAR(l.PARTNAME).

Upvotes: 1

Related Questions