>A.field1 and field2 are the intern project number, b.fielda and
b.fieldb are the extern ordernumber year and number.
>Sum(A.field3) where a.field1 = condition1 and a.field2 = condition2
>In the same statement I would include
>Sum(b.fieldc) where b.fielda = a.field4 and b.fieldb = a.field5
>It is possible without cte?
These seems like two completely different queries with no correlation,
so my guess is that the answer is no, you need a cte or something
similar (e.g. execute block).
However, if A has a 1:many correlation to B, and you only want to count
those B's that also match condition1 and condition2, then something like
select sum(A.field3), sum((select sum(b.fieldc) from B where b.fielda =
a.field4 and b.fieldb = a.field5)) --you need double parenthesis and sum
from A
where a.field1 = :condition1 and a.field2 = :condition2
could be a solution (make sure to test so that you don't get double or
triple of what you want from sum(B) if some records of A had the same
value for field1, field2, field4 and field5).
I doubt this is a noticable optimization compared to cte or execute
block, I think of it as an alternative suitable in some cases, but I
neither expect it to be superior nor inferior in those cases.
HTH,
Set
++
Visit http://www.firebirdsql.org and click the Documentation item
on the main (top) menu. Try FAQ and other links from the left-side menu there.
Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/
++
Yahoo Groups Links
<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/
<*> Your email settings:
Individual Email | Traditional
<*> To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)
<*> To change settings via email:
firebird-support-dig...@yahoogroups.com
firebird-support-fullfeatu...@yahoogroups.com
<*> To unsubscribe from this group, send an email to:
firebird-support-unsubscr...@yahoogroups.com
<*> Your use of Yahoo Groups is subject to:
https://info.yahoo.com/legal/us/yahoo/utos/terms/