Arno Brinkman wrote:

> Hi,
>
>> I want to count all orders and customers and want the sum of all
>> weights and quantities.
>> But weights and quantities should only be counted once per order.
>> I thought this is a standard problem.
>> I cannot use a view because joins and where clause is dynmacily
>> created.
>
> You're joining more tables then the two Customers and Orders tables?
> Looking at you're query i understand you need only those two for these
> results. Could you post the whole query?
>

Whole query looks like:
select count(distinct t_apos.id_apos),count(distinct
t_Apos.f_id_auftrag),count(distinct
f_id_kunde),sum(t_Apos.f_weight),sum(t_apos.f_quantity) FROM t_apos a1 LEFT
JOIN t_auftrag ON f_id_auftrag = id_auftrag JOIN t_lagerposzuord on
id_lagerposzuord = f_id_lagerposzuord JOIN t_ch_apos_zuord ON id_apos =
f_id_apos JOIN t_charge ON f_id_charge = id_charge WHERE
(t_charge.f_id_anlage = 84 AND t_auftrag.f_adatum between '05.10.2004 00:00'
AND '06.11.2004 11:23' ) AND id_apos > 0

This is dynamcily created, so the join and the where clause can look
different for each query.
Problem is the n:n relationship between t_apos,t_ch_apos and t_charge,
because of this i got duplicate lines. (Only difference is a field
f_id_charge that is not relevant for the result)

>> I would need a select on a select(temporary tables), but this seems
>> not to be possible in Firebird.
>
> What you want is derived tables, but that's indeed currently not
> supported. (FB2.0 will have it).
>

Yes this is what i meant.

mfg
Carsten