Use of having with additional select

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?

Re: Use of having with additional select

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

Re: Use of having with additional select

> I can't test because I have no access to your tables and test data, but
> try if the followiing does what you need:

Table CLPF08 sample data and relevant field information:

RAMO POLIZA CERVEH CODCOB MONTO1
31 6100265 2 1 20.15
31 6100265 2 2 30.10
31 6100265 2 3 15.02

Table ARYSAUTO sample data and relevant field information:

CERVEH PRIMA
2 65.26

My query shows a 0.01 monetary units of difference, since
20.15+30.10+15.02 is not exactly 65.26

The idea is to add all of MONTO1 in CLPF08 and compare that sum with
the lone value in table ARYSAUTO.
My query works, but using the HAVING statement to show up only those
records that have certain difference (we only need big differences,
not measly cents) doesn't work.

Your query executed, but it showed bizarre amounts.

Thanks for your help, i hope that this information is enough to fix
this

Re: Use of having with additional select

On Wed, 23 Apr 2008 13:49:49 -0700 (PDT), Seguros Catatumbo wrote:

>
>> I can't test because I have no access to your tables and test data, but
>> try if the followiing does what you need:
>
>Table CLPF08 sample data and relevant field information:
>
>RAMO POLIZA CERVEH CODCOB MONTO1
>31 6100265 2 1 20.15
>31 6100265 2 2 30.10
>31 6100265 2 3 15.02
>
>Table ARYSAUTO sample data and relevant field information:
>
>CERVEH PRIMA
> 2 65.26
>
>My query shows a 0.01 monetary units of difference, since
>20.15+30.10+15.02 is not exactly 65.26

Hi Seguros,

I expect that this is a rounding issue. What data types are the various
columns?

>Thanks for your help, i hope that this information is enough to fix
>this

No, it isn't. As I already stated in my previous reply, I need to know
the table structure ===> as a CREATE TABLE statement <===. I failed to
include that you also need to include all the constraints, properties,
and indexes (you may omit irrelevant extra columns though).

I also need the sample data ===> as INSERT statements <===. And I need
to know the expected results based on the sample data given.

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

Re: Use of having with additional select

I agree with Hugo that the information you provided is not sufficient to
give you a good answer. Here is just a guess based on your explanation and
expected results:

SELECT C.cerveh, C.monto1, A.prima
FROM Arysauto AS A
JOIN (SELECT ramo, cerveh, poliza, stcdcb,
SUM(monto1) AS monto1
FROM Clpf08
GROUP BY ramo, cerveh, poliza, stcdcb) AS C
ON A.cerveh = C.cerveh
WHERE C.ramo = 31
AND C.poliza = 6100265
AND C.stcdcb = ' '
AND EXISTS (SELECT *
FROM Clpf07 AS B
WHERE B.ramo = C.ramo
AND B.poliza = C.poliza
AND B.cerveh = C.cerveh
AND B.actret <> 'R')
AND C.monto1 - A.prima > 0;

HTH,

Plamen Ratchev
http://www.SQLStudio.com