Hi Josef,

what I figured out to be quite handy, is to write something like:

select
MASTER.*,
cast(left(S, 10) as bigint),
cast(substring(S from 10 for 10) as bigint),
cast(right(S, 10) as bigint),
from (SELECT MASTER.ID,
(SELECT
lpad(SUM(VALUE1), 10) ||
lpad(MAX(VALUE2), 10) ||
lpad(MIN(VALUE1), 10)
FROM DETAIL
WHERE DETAIL_ID=MASTER.ID) as S
FROM MASTER
WHERE NAME STARTING WITH 'A') s
join MASTER m on m.ID = s.ID

Thomas

Am 18.04.2014 10:49, schrieb Josef Kokeš:

> Hi!
>
> I am struggling with a particular select, trying to get it to execute as
> efficiently as possible. I have tables MASTER(ID, NAME, ...) and
> DETAIL(ID, MASTER_ID, VALUE1, VALUE2, VALUE3). The SELECT I want to get
> would display all fields from MASTER and an aggregation of fields from
> DETAIL, e.g. something like:
>
> SELECT MASTER.*,
> (SELECT SUM(VALUE1) FROM DETAIL WHERE DETAIL_ID=MASTER.ID),
> (SELECT MAX(VALUE2) FROM DETAIL WHERE DETAIL_ID=MASTER.ID),
> (SELECT MIN(VALUE1) FROM DETAIL WHERE DETAIL_ID=MASTER.ID)
> FROM MASTER
> WHERE NAME STARTING WITH 'A'
>
> I would like to replace the three similar subselects with one
> three-field subselect, but my attempts at using CTE rather failed:
>
> WITH DETAIL_AGGREGATE (MASTER_ID, VALUE1, VALUE2, VALUE3)
> AS (SELECT MASTER_ID, SUM(VALUE1), MAX(VALUE2), MIN(VALUE3)
> FROM DETAIL
> GROUP BY MASTER_ID)
> SELECT MASTER.*, DETAIL_AGGREGATE.*
> FROM MASTER
> JOIN DETAIL_AGGREGATE ON MASTER_ID=MASTER.ID
> WHERE NAME STARTING WITH 'A'
>
> This is very slow, presumably because it calculates the aggregates over
> all rows in DETAIL, regardless of whether the rows are actually included
> in the result.
>
> I could probably write a traditional:
>
> SELECT MASTER.ID, MASTER.NAZEV, MASTER.xyz, ...,
> SUM(DETAIL.VALUE1), MAX(DETAIL.VALUE2), MIN(DETAIL.VALUE3)
> FROM MASTER
> LEFT JOIN DETAIL ON DETAIL.MASTER_ID=MASTER.ID
> WHERE MASTER.NAME STARTING WITH 'A'
> GROUP BY 1, 2, 3, ...
>
> But there are specific conditions which make this kind of SELECT rather
> undesirable.
>
> I still think that CTEs are the way to go, but I can't figure out how to
> write them efficiently.
>
> Can anyone help, please?
>
> Thanks,
>
> Josef
>
>
> ------------------------------------
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Visit http://www.firebirdsql.org and click the Resources item
> on the main (top) menu. Try Knowledgebase and FAQ links !
>
> Also search the knowledgebases at http://www.ibphoenix.com
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
> Yahoo Groups Links
>
>
>

--
Mit freundlichen Grüßen,

Thomas Beckmann
Diplom-Informatiker


Wielandstraße 14c • 23558 Lübeck
Tel +49 (22 25) 91 34 - 545 • Fax +49 (22 25) 91 34 - 604
Mail thomas.beckmann@... <mailto:thomas.beckmann@...>

ASSFINET-Logo

*ASSFINET Dienstleistungs-GmbH*
Max-Planck-Straße 14 • 53501 Grafschaft bei Bonn
info@... <mailto:info@...> • www.assfinet.de
<http://www.assfinet.de/>

Geschäftsführer: Dipl. Wirtschaftsinformatiker Marc Rindermann
Registergericht Koblenz HRB 23331

Diese E-Mail enthält vertrauliche Informationen. Wenn Sie nicht der
richtige Adressat sind oder diese E-Mail irrtümlich erhalten haben,
informieren Sie bitte sofort den Absender und vernichten Sie diese Mail.
Das unerlaubte Kopieren sowie die unbefugte Weitergabe dieser Mail ist
nicht gestattet.