Hello,

I want to join two views with following SQL code but receive error
message "Invalid expression near 'sum(SpPajView.kiek)/count(distinct
SpPajView.sp_id' ". I use ASA 6.03
If I remove 'distinct'  from this line error message doesn't appear
but result is multiplied by rows number. Maybe there's another way to
solve that?

alter view dba.sumos
  as select Sp.id,Sp.raj_id,
    sum(SpLikView.kiek)/count(distinct SpLikView.sp_id),
    sum(SpPajView.kiek)/count(distinct SpPajView.sp_id) from
    dba.Sp left outer join
    dba.SpLikView on Sp.id = SpLikView.sp_id left outer join
    dba.SpPajView on Sp.id = SpPajView.sp_id
    group by Sp.id,Sp.raj_id

Thank you for any help.

====================
Sigitas Galinis
software engineer
AB "Anyksciu vynas"
si...@anvynas.lt
====================

Left outer join guarantees that record from left table will be in result set
regardless of existence of match in right table(s). But if right table has
more than one match, it duplicates left record for each match. One solution
is to make two views, first is the join that returns distinct records,
second does the aggregate.
Cleaner solution is to modify join condition to match only one record from
right table.
You could also try AVG function or ("inner join" union "the rest").....
--
----------------------
Regards
Robert Cerny
Remove both qwe when replying
email: robert.qwe.ce...@neosys.xrs.qwe.si

No questions via email, unless explicitly invited.

<3912baa6.82650...@newsgroups.borland.com>...