non-constant comparisons in where clauses

From: Kevin Skaalrud <kevin_skaalrud_at_neptune.serca.com>
Date: Thu, 27 Aug 1998 11:42:14 -0700
Message-ID: <35E5A886.56862C16@neptune.serca.com>

I am having difficulty getting a query to work the way I expect.

Here is (roughly) the query:

select cm.name, cm.desc, sa.sales98, sa.sales99 from v_sales sa, cm
where (cm.active != 'I') and (sa.sales98 > (sa.sales99 * 3));

The problem is with the "sales98 >" part as I can replace this equation with one that uses constants.

Essentially, what I am looking for are customers whose sales have declined to roughly 33% of a year ago.

I have tried replacing the "(sa.sales99 * 3)" with "(decode(sa.sales99,0,0,(sales99*3)))" with exactly the same results.

Are we not allowed to perform this kind of function in where clauses?

Kevin Skaalrud Received on Thu Aug 27 1998 - 13:42:14 CDT