On Wed, 23 Apr 2008 12:10:34 -0700 (PDT), Seguros Catatumbo wrote:
>Hello guys, i have this query:
>
>select c8.cerveh, sum(c8.monto1) monto1,
> (select prima from arysauto a where a.cerveh=c8.cerveh)
>priari,
> (sum(c8.monto1)-(select prima from arysauto a
> where a.cerveh=c8.cerveh)) dif
>from clpf08 c8
>where c8.ramo=31 and c8.poliza=6100265 and
> c8.stcdcb=' ' and c8.cerveh in (select cerveh from arysauto)
>and
> exists (select * from clpf07 where ramo=31 and poliza=6100265
>and
> cerveh=c8.cerveh and actret<>'R')
>group by c8.cerveh
>
>
>That query prints the sum of a value and compares it with a
>standalone
>value in another table, and then outputs the difference between those
>2 values.
>
>
>Now i want the same query, but to only show the values that have a
>difference in absolute value over 1 units. So i tried putting at the
>end of the group by the following:
>
>
>having (sum(c8.monto1) - (select prima from arysauto a where
> a.cerveh = c8.cerveh) ) > 0
>
>
>But the query doesn't run with some error that i have another
>function
>inside a funtion. If i remove the select inside the having and just
>put a number it runs, so i am guessing it is the additional select.
>
>
>There must be a way to do this, but i am stuck. Can someone help?
>
Hi Seguros,
I'm not sure why you got that error - I would have expected it to work.
If you could post the table structure (CREATE TABLE statements) and some
sample data (INSERT statements), I could try to reproduce. However, I
think you might be better off rewriting your query to eliminate the
repetition of the subquery.
I can't test because I have no access to your tables and test data, but
try if the followiing does what you need:
SELECT c8.cerveh,
SUM(c8.monto1) AS monto1,
SUM(a.prima) AS priari,
SUM(c8.monto1) - SUM(a.prima) AS dif
FROM clpf08 AS c8
INNER JOIN arysauto AS a
ON a.cerveh = c8.cerveh
WHERE c8.ramo = 31
AND c8.poliza = 6100265
AND c8.stcdcb = ' '
AND EXISTS
(SELECT *
FROM clpf07 AS c7
WHERE c7.ramo = 31
AND c7.poliza = 6100265
AND c7.cerveh = c8.cerveh
AND c7.actret <> 'R')
GROUP BY c8.cerveh;
--
Hugo Kornelis, SQL Server MVP
My SQL Server blog:
http://sqlblog.com/blogs/hugo_kornelis