472,954 Members | 2,034 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,954 software developers and data experts.

Problem using/naming subquery


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

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



select mrnpty "Scheme", mrncru "User", mrnsts "Status", mrnpap
(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.sysdummy1
where mrncrd = curdate()


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.sysdummy1
Sep 18 '08 #1
2 2288
1) You can use column names of subqueries in an expresion, like this:
, (OnCover / Enquiry)*100
/* it would be better to use "OnCover * 100. / Enquiry" to keep the
digits in the fractional part. */
, (SELECT ...
) AS Enquiry
, (SELECT ...
) AS OnCover
FROM ...
) Sub

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

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

4) I wonder that your subselect(s) including "group by" might return
multiple rows and you might get error.
Sep 18 '08 #2
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)
from elinew.mrnquote
where mrncrd = curdate()
and mrnsts IN ('O', 'G')
Sep 18 '08 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

by: André Gasser | last post by:
hello newsgroup, I just discovered a weird effect in my php code. here is the flow of my code: 1. upload jepg file to server 2. create new (empty) jpeg file using imagecreatefromjpeg()...
by: bmiras | last post by:
I've got a problem using urllib2 to get a web page. I'm going through a proxy using user/password authentification and i'm trying to get a page asking for a HTTP authentification. And I'm using...
by: Stephen Last | last post by:
Hi all, I have a problem using FSO with a mapped drive. I have mapped x: on our Intranet server to a folder on one of our file servers. The folder contains user home directories. I want to be...
by: Derek Timothy | last post by:
Hi folks I have strange problem using cmd.exe from asp code I am trying to save the results of an FTP command into a text file. When I run this command from the command line of the web server it...
by: Jacky Zhu | last post by:
Hi all, I am having a problem trying to consume a webservice that is developed on ..Net. I can access it without any problem using a .net client, but when I use a java client (based on Axis...
by: Dani | last post by:
Hi everyone Description of the problem: Using a PreparedStatement to write down an integer (int) plus a timestamp for testing purposes. When read out again the integer looks very different. We...
by: Kent Lewandowski | last post by:
hi all, Recently I wrote some stored procedures using java jdbc code (admittedly my first stab) and then tried to implement the same within java packages (for code reuse). I encountered...
by: Franky | last post by:
Been having a problem using a treeview. Work great the first time the form containing it is entered but not the second time. Took a while to create a small sample that exhibits the problem, but...
by: presencia | last post by:
Hi all, I have a problem using gdb. I am using Eclipse-CDT as a frontend, but since this problem is obviously related to the gcc compiler or the gdb itself, I have reproduced it without Eclipse. ...
by: DJRhino | last post by:
Was curious if anyone else was having this same issue or not.... I was just Up/Down graded to windows 11 and now my access combo boxes are not acting right. With win 10 I could start typing...
by: Aliciasmith | last post by:
In an age dominated by smartphones, having a mobile app for your business is no longer an option; it's a necessity. Whether you're a startup or an established enterprise, finding the right mobile app...
by: tracyyun | last post by:
Hello everyone, I have a question and would like some advice on network connectivity. I have one computer connected to my router via WiFi, but I have two other computers that I want to be able to...
by: giovanniandrean | last post by:
The energy model is structured as follows and uses excel sheets to give input data: 1-Utility.py contains all the functions needed to calculate the variables and other minor things (mentions...
by: NeoPa | last post by:
Introduction For this article I'll be using a very simple database which has Form (clsForm) & Report (clsReport) classes that simply handle making the calling Form invisible until the Form, or all...
by: Teri B | last post by:
Hi, I have created a sub-form Roles. In my course form the user selects the roles assigned to the course. 0ne-to-many. One course many roles. Then I created a report based on the Course form and...
by: nia12 | last post by:
Hi there, I am very new to Access so apologies if any of this is obvious/not clear. I am creating a data collection tool for health care employees to complete. It consists of a number of...
by: NeoPa | last post by:
Introduction For this article I'll be focusing on the Report (clsReport) class. This simply handles making the calling Form invisible until all of the Reports opened by it have been closed, when it...
by: GKJR | last post by:
Does anyone have a recommendation to build a standalone application to replace an Access database? I have my bookkeeping software I developed in Access that I would like to make available to other...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.