I am getting an error stating, ““Code=932, Message=ORA-00932: inconsistent datatypes: expected NUMBER got CHAR,”” when I try to use an SQL statement in PS Query. The following is meant to provide the year of a term. CASE WHEN SUBSTR(B.STRM,3,1) = ‘1’ THEN 20|| SUBSTR(B.STRM,1,2) - 1 ELSE 20|| SUBSTR(B.STRM,1,2) END It sounds like it should be a simple matter of wrapping the TO_NUMBER( ) command around all or part of the expression. I have tried a couple of different approaches with no success. Any ideas on how to trick PS Query into running that expression?
I agree with you Justin that SQL do automatic casting when Math operation is involved. The easiest solution will be:
FROM:
CASE WHEN SUBSTR(B.STRM,3,1) = ‘1’ THEN 20|| SUBSTR(B.STRM,1,2) - 1 ELSE 20|| SUBSTR(B.STRM,1,2) END
CASE WHEN SUBSTR(B.STRM,3,1) = ‘1’ THEN 20|| (SUBSTR(B.STRM,1,2) - 1) ELSE 20|| SUBSTR(B.STRM,1,2) END
Math operation must be performed first before performing concatenation. The problem might be in the sequence how SQL execute each function or operation.
Alright but the OP does have a math operation, so if a character did sneak in he has another problem. I would assess the requirement and base my decision on what is most appropriate.
Personally, I would avoid TO_NUMBER(), especially if there’s any way for non-numeric values to sneak in there.
I would instead use TO_CHAR() on the other side of the fence, since that will always work.
@Jepoy
The problem has nothing to do with the manipulation of the data, but because the Case statement needs to return a character or a number, a single column can never have more than one data type. Individually the SUBSTR & Math manipulation is fine, but once inserted into a CASE statement it fails. This is the issue the OP is having.
For example the following will fail:
SELECT CASE WHEN 1=1 THEN 2001 ELSE ‘2002’ END FROM dual
But this will work:
SELECT CASE WHEN 1=1 THEN 2000 + 1 ELSE ‘2001’ + 1 END FROM dual
The Math operation forces the SQL to see a number in both cases, which is why using TO_NUMBER also works.
Try this:
CASE WHEN SUBSTR(‘2010’,3,1) = ‘1’ THEN 20|| TO_CHAR(TO_NUMBER(SUBSTR(‘2010’,1,2)) - 1) ELSE 20|| SUBSTR(2010,1,2) END
you need to convert the char you got in the line that needs to subtract 1 and return it back to char so concatenation will work.
This may sound odd but it would seem that even though you are dealing with numbers, there are times where the SQL interprets the number as a character.
So because you have a “- 1” operation in the first part of it sees that as a number , but in the ELSE part of the Case Statement it sees a character and the SQL cannot understand this. Try adding a “+ 0” to the ELSE portion.
CASE WHEN SUBSTR(B.STRM,3,1) = ‘1’ THEN 20|| SUBSTR(B.STRM,1,2) - 1 ELSE 20|| SUBSTR(B.STRM,1,2) + 0 END
CASE WHEN SUBSTR(STRM,3,1) = ‘1’ THEN TO_NUMBER(‘20’|| SUBSTR(STRM,1,2)) - 1 ELSE TO_NUMBER(‘20’|| SUBSTR(STRM,1,2)) END
Thanks to a user on another forum for the answer!
