Re: CASE expression

From: Jaap W. van Dijk <j.w.vandijk.removethis_at_hetnet.nl>
Date: Wed, 06 Dec 2006 10:58:28 GMT
Message-ID: <4576a02f.390390@news.hetnet.nl>

On 5 Dec 2006 17:19:15 -0800, "April" <amknight_at_gmail.com> wrote:

>As part of a somewhat simple SELECT statement, I am attempting the case
>expression below on an Oracle server and am getting an Oracle error
>stating "Error signaled in parallel query server. Invalid number".
>Here's the expression:
>
>CASE
>WHEN VARIABLE_A = 'R' THEN 0
>WHEN VARIABLE_A = 'C' THEN
>NVL(NUMBER_1,0)+NVL(NUMBER_2,0)
>END AS TOTAL
>
>
>The idea is I only want to sum numbers 1 and 2 when variable A = 'C'.
>If its 'R' then I want TOTAL to be 0. Am I missing something?
>

You or Oracle are trying to convert a non-NUMBER column to NUMBER. The most likely cause of the error is that NUMBER_1 or NUMBER_2 are not of NUMBER datatype. Because you are adding them in your statement (or because the second argument of the NVL's is NUMBER, I'm not sure) Oracle will try to convert them to NUMBER, and for at least one record the contents is nonnumeric.

The next likely cause is that VARIABLE_A is NUMBER... (so ORACLE tries to convert 'R' and 'C' to NUMBER)

Jaap. Received on Wed Dec 06 2006 - 04:58:28 CST