Problem using/naming subquery

[Steve]

Hi,

I'm trying to write an SQL statement that provides a list of insurance/
product groups and then lists a total and percentage on the same line
e.g.

XX 5 4 80%
XY 10 5 50%

I can get the first 3 columns done ok but can't do the percentage.
The totals are calculated based on subqueries and I've named the
subqueries and would expect to be able to use the value returned in
the percentage calculation except I get the error "Column ONCOVER not
in specified tables."

Can anybody help please? I've attached the SQL below

Thanks

Steve



select mrnpty "Scheme", mrncru "User", mrnsts "Status", mrnpap
"Paperless" ,
(select count(*) from elinew.mrnquote b
where mrncrd = curdate()
and b.mrnsts = 'G'
and a.mrnpol = b.mrnpol
and a.mrnpty = b.mrnpty
and a.mrncru = b.mrncru
group by mrncru, mrnpty, mrnsts, mrnpap) AS Enquiry ,

(select count(*) OncoverTotal from elinew.mrnquote b
where mrncrd = curdate()
and b.mrnsts = 'O'
and a.mrnpol = b.mrnpol
and a.mrnpty = b.mrnpty
and a.mrncru = b.mrncru
group by mrncru, mrnpty, mrnsts, mrnpap) AS OnCover,

((OnCover / Enquiry)*100)

from elinew.mrnquote a, sysibm.sysdummy 1
where mrncrd = curdate()

union

select 'Total', (select count(*) from elinew.mrnquote where mrncrd =
curdate() and mrnsts IN ('O', 'G')),
'Paperless', (select count(*) from elinew.mrnquote where mrncrd =
curdate() and mrnsts IN ('O', 'G') and mrnpap = 'Y'), ' ', ' ', ' '
from sysibm.sysdummy 1

[Tonkuma]

Re: Problem using/naming subquery

1) You can use column names of subqueries in an expresion, like this:
SELECT ...
, (OnCover / Enquiry)*100
/* it would be better to use "OnCover * 100. / Enquiry" to keep the
digits in the fractional part. */
FROM (SELECT ...
, (SELECT ...
...
) AS Enquiry
, (SELECT ...
...
) AS OnCover
FROM ...
...
) Sub

2) It wouldn't need to join sysibm.sysdummy 1 in the clause
"from elinew.mrnquote a, sysibm.sysdummy 1".

3) You might receive incompatible data type error by your UNION.
If so, replace ' ' with CAST(NULL AS INTEGER) or CAST(NULL AS
DECIMAL).

4) I wonder that your subselect(s) including "group by" might return
multiple rows and you might get error.

[Tonkuma]

Re: Problem using/naming subquery

I think that last half of your UNION can be replaced by this.
select 'Total'
, count(*)
, 'Paperless'
, count(CASE WHEN mrnpap = 'Y' THEN 0 END)
, CAST(NULL AS INTEGER)
, CAST(NULL AS INTEGER)
, CAST(NULL AS DECIMAL)
from elinew.mrnquote
where mrncrd = curdate()
and mrnsts IN ('O', 'G')