Prev: Replication Cold Start
Next: Problem with to_date-function on AIX
From: du_bing on 12 Mar 2010 12:00
Hi,

The following statement was intended to get total count from each
section first and then get the total counts of all sections. The
union part works and returns. But 'select sum(used)' does not. The
error message can be found at the end of this message. Appreciate any
help! Thanks.

------
used
47
73
-----

select sum(used)
from (
select count(*) as "used"
from ur.agncy
where agncy_id >= '2283' and agncy_id <= '2329'

union

select count(*) as "used"
from ur.agncy
where agncy_id >= '2922' and agncy_id <= '2994'
)

An error occurred while running the query.

Illegal symbol "<EMPTY>", Some symbols that might be legal are:
CORRELATION NAME.

(SQL code = -104, SQL state =42601)
From: Tonkuma on 12 Mar 2010 13:40
> Illegal symbol "<EMPTY>", Some symbols that might be legal are:
> CORRELATION NAME.
>
> (SQL code = -104, SQL state =42601)
Perhaps you are using older version of DB2.
The reason of the message would be missing correlation clause.

Try to add "AS q" like in the following example.
("q" could be any valid correlation-name.)

select sum(used)
from (
select count(*) as "used"
from ur.agncy
where agncy_id >= '2283' and agncy_id <= '2329'
union
select count(*) as "used"
from ur.agncy
where agncy_id >= '2922' and agncy_id <= '2994'
) AS q


From: Tonkuma on 12 Mar 2010 14:00
> select sum(used)
> from (
> select count(*) as "used"
> from ur.agncy
> where agncy_id >= '2283' and agncy_id <= '2329'
> union
> select count(*) as "used"
> from ur.agncy
> where agncy_id >= '2922' and agncy_id <= '2994'
> ) AS q
Some issues.
1) Possible syntax error.
SELECT SUM(used) should be SELECT SUM("used")
or
remove double quotations from as "used" in subquery.

2) Possible incorrect result.
UNION should be UNION ALL.
If first and second select in subquery returned same count,
final result SUM(used) would return the count.
(Not two times of the count.)

3) You can use BETWEEN expression.

Here is an example of rewrite.
SELECT SUM(used)
FROM (
SELECT COUNT(*) as used
FROM ur.agncy
WHERE agncy_id BETWEEN '2283' AND '2329'
UNION ALL
SELECT COUNT(*) as used
FROM ur.agncy
WHERE agncy_id BETWEEN '2922' AND '2994'
) AS q
;


From: Tonkuma on 12 Mar 2010 14:05
> Here is an example of rewrite.
> SELECT SUM(used)
>   FROM (
>        SELECT COUNT(*) as used
>          FROM ur.agncy
>         WHERE agncy_id BETWEEN '2283' AND '2329'
>        UNION ALL
>        SELECT COUNT(*) as used
>          FROM ur.agncy
>         WHERE agncy_id BETWEEN '2922' AND '2994'
>        ) AS q
> ;
Or, simply...
SELECT COUNT(*)
FROM ur.agncy
WHERE agncy_id BETWEEN '2283' AND '2329'
OR agncy_id BETWEEN '2922' AND '2994'
;


From: Tonkuma on 12 Mar 2010 14:20
>> to get total count from each section first and then get the total counts of all sections <<

Get "count from each section" and "total counts" at once.

SELECT CASE GROUPING(section)
WHEN 1 THEN
'All sctions'
ELSE section
END AS section
, SUM(used) AS count
FROM (
SELECT 'Section one'
, COUNT(*)
FROM ur.agncy
WHERE agncy_id BETWEEN '2283' AND '2329'
UNION ALL
SELECT 'Section two'
, COUNT(*)
FROM ur.agncy
WHERE agncy_id BETWEEN '2922' AND '2994'
) AS q (section , used)
GROUP BY
ROLLUP(section)
;
 |  Next  |  Last
Pages: 1 2
Prev: Replication Cold Start
Next: Problem with to_date-function on AIX