× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.




As always, thanks for the info.
Frank



-----Original Message-----
From: CRPence <CRPbottle@xxxxxxxxx>
To: midrange-l <midrange-l@xxxxxxxxxxxx>
Sent: Fri, Feb 22, 2013 1:35 pm
Subject: Re: SQL Break/total question


On 22 Feb 2013 08:46, Buck Calabro wrote:
<<SNIP>> But SQL allows us to combine separate SELECTS into one
result set via UNION. The caveat is that each SELECT in the UNION
must have the same columns. Well, the same number and type, but you
get the idea.
Compatible types, and values of those types, eligible to be CAST into
he ¿promoted? type; see type precedence and\or type promotion in docs.
Because the top query is selecting LGSESN and LGDATE, the bottom
query needs the same two columns, but COUNT(*) isn't really either
one. So to make the two SELECTs compatible, I'd add a count to the
top and two dummy columns to the bottom:

SELECT LGSESN , LGDATE , count(*)
FROM anloga
WHERE LGSESN NOT LIKE '%ARCHIVE%'
group by LGSESN, LGDATE
UNION
SELECT '99999' , 0 , count(*)
FROM anloga
WHERE LGSESN NOT LIKE '%ARCHIVE%'
order by 1

The trick with the UNION is the ORDER BY. Since there are multiple
SELECTs being lumped together, the column names aren't valid for the
sort. Use the column numbers instead.
FWiW: The UNION ALL is probably desired versus UNION. The latter is
synonym for UNION DISTINCT. Use of constants [aka literals] is
iscouraged, because the data must be well-understood and presumed; e.g.
s '99999' really always going to sort highest.? The /better/ choice is
he NULL value. The NULL value collates highest, and only the data type
ust be presumed. I also might assign summary "levels" to the query to
stablish order, especially if the column(s) might have the NULL value.
So for example I might code instead, one of the following [if OLAP
uery syntax is not available]:
[code]
SELECT dec(1, 1) as lvl /* level-1 summary; group 1 */
, LGSESN
, LGDATE
, count(*)
FROM anloga
WHERE LGSESN NOT LIKE '%ARCHIVE%'
group by LGSESN, LGDATE
UNION ALL /* do not do DISTINCT processing */
SELECT dec(0, 1) as lvl /* overall summary; group 0 */
, cast(NULL as char) /* typed /like/ LGSESN */
, cast(NULL as date) /* typed /like/ LGDATE */
, count(*)
FROM anloga
WHERE LGSESN NOT LIKE '%ARCHIVE%'
/* implicit GROUP is all rows */
order by 2, 3 /* order by grouped values, and... */
, 1 desc /* overall summary follows summary group 1 */

SELECT dec(2, 1) as lvl /* level-2 summary; group 1 */
, LGSESN
, LGDATE
, count(*)
FROM anloga
WHERE LGSESN NOT LIKE '%ARCHIVE%'
group by LGSESN, LGDATE
UNION ALL /* do not do DISTINCT processing */
SELECT dec(1, 1) as lvl /* level-1 summary; group 1 */
, LGSESN
, cast(NULL as date) /* typed /like/ LGDATE */
, count(*)
FROM anloga
WHERE LGSESN NOT LIKE '%ARCHIVE%'
group by LGSESN
UNION ALL /* do not do DISTINCT processing */
SELECT dec(0, 1) as lvl /* overall summary; group 0 */
, cast(NULL as char) /* typed /like/ LGSESN */
, cast(NULL as date) /* typed /like/ LGDATE */
, count(*)
FROM anloga
WHERE LGSESN NOT LIKE '%ARCHIVE%'
/* implicit GROUP is all rows */
order by 2, 3 /* order by grouped values, and... */
, 1 desc /* overall summary follows summary groups 2 and 1 */
[/code]

As an Amazon Associate we earn from qualifying purchases.

This thread ...

Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.