OK got the message, thanks. I actually deliberately left off the ALL. I guess I'd have seen my mistake when I finally got round to executing with Visual Explain, which would certainly have advised me the same. Wouldn't it?!
-----Message d'origine-----
De : midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] De la part de Elvis Budimlic
Envoyé : mercredi 6 août 2008 16:43
À : 'Midrange Systems Technical Discussion'
Objet : RE: SQL : subtotals and total
I said it before number of times and I'll keep repeating it till I'm blue in the face. For performance reasons, use UNION ALL instead of simply UNION, whenever you can (i.e. result sets are identical with either version).
Version without the ALL qualifier performs DISTINCT processing unconditionally.
It's just a good habit to get into, and you'll notice that in Charles'
example he's used the ALL qualifier.
HTH, Elvis
Celebrating 11-Years of SQL Performance Excellence on IBM i, i5/OS and OS/400 www.centerfieldtechnology.com
-----Original Message-----
Subject: RE: SQL : subtotals and total
Great thanks Alan,
Now I have
SELECT
SPTFCD, '10' AS CODE_STATISTIQUE, SPRDCD, SSLMNV, SUM(SOCCNB) AS PROPOSITIONS FROM SVQSTS WHERE SRBQCD = 'TOT2'
AND SOCCNB <> 0
GROUP BY SPTFCD, SPRDCD, SSLMNV
union
SELECT
SPTFCD, '10' AS CODE_STATISTIQUE, SPRDCD, ' ', SUM(SOCCNB) AS PROPOSITIONS FROM SVQSTS WHERE SRBQCD = 'TOT2'
AND SOCCNB <> 0
GROUP BY SPTFCD, SPRDCD
Which gives me ( I still have to verify ! ) subtotals by SSLMNV followed by a grand total.
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit:
http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a moment to review the archives at
http://archive.midrange.com/midrange-l.
midrange.com
