Re: non-constant comparisons in where clauses

>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.

try something like this...

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 > (select 3*sa2.sales99 from v_sales sa2 where
sa2.custno = sa.custno);

I'm not sure of your exact datastructures, but in the sub-query, you need to find the same customer and get the 1999 data and compare it to the 1998 from the 'main' query.

Hope that helps a little
Dan Hekimian-Williams Received on Sun Aug 30 1998 - 01:14:23 CDT