Re: non-constant comparisons in where clauses

Sorry, it was mainly the where clause that concerned me. (the wierd part is, it appears to compile ok) So here goes...

select cm.name, cm.desc, sa.sales98, sa.sales99,

       sa.sales99 - sa.sales98 diffsales from v_sales sa, cms cm
where (sa.custno = cm.id) and (cm.active != 'I') and (sa.sales98 > (sa.sales99 * 3));

Compiles ok, but results in the runtime error: ORA06502: PL/SQL: numeric or value error.

perhaps this has some bearing, but I am also unable to use a calculated field in the where clause

If I do "...and (sa.sales99 < diffsales)" results in an error: 'diffsales' must be declared.

One thing I should mention, all of this is being done in a cursor in a stored procedure. I know that I have run into some PL/SQL compiler flaws in the past.

Arjan van Bentem wrote:
>
> >from v_sales sa, cm
>
> Where is the tablename which gets the alias cm? Just missing in this message
> I guess (you stated "roughly"...) ? And where is the join for the two
> tables?
>
> >with exactly the same results.
>
> *What* result? Some error message?
>
> >Are we not allowed to perform this kind of function in
> >where clauses?
>
> Hmmm, the where clause seems OK to me.
>
> Arjan.
Received on Thu Aug 27 1998 - 17:45:10 CDT