2. PHPRunner General questions
  3. Charts and UNION queries
This topic is locked

Charts and UNION queries

 		12/9/2008 4:16:35 PM
PHPRunner General questions
D
 dbowen author

I am trying to create a horizontal bar chart using a UNION query:

SELECT 'Cyber Girls' AS section, COUNT(a.heatLevel) AS completed FROM picture a WHERE a.heatLevel > 0 UNION

SELECT 'CGUK' AS section, COUNT(b.heatLevel) AS completed FROM cgukpicture b WHERE b.heatLevel > 0 UNION

SELECT 'Playmate Portfolio' AS section, COUNT(c.heatLevel) AS completed FROM pmppicture c WHERE c.heatLevel > 0 UNION

SELECT 'CGXtra' AS section, COUNT(d.heatLevel) AS completed FROM cgepicture d WHERE d.heatLevel > 0 UNION

SELECT 'Celebrities' AS section, COUNT(e.heatLevel) AS completed FROM celebpicture e WHERE e.heatLevel > 0


and the results:

section completed

======= =========

Cyber Girls 999

CGUK 48

Playmate Portfolio 1

CGXtra 195

Celebrities 0


I am only getting a single bar called 'Cyber Girls' with the 999 completed. Why are the other bars missing?
I am typing the SQL directly into the SQL tab. When I view the SQL in the Query Designer tab it changes my SQL to:

SELECT

'Cyber Girls' AS `section`,

COUNT(heatLevel) AS completed

FROM picture AS a

WHERE heatLevel > 0


which is not what I want... the other 4 sections are missing. It looks like the chart is using this SQL instead of what I typed, whether I type it in directly or view it in the Query Designer.
How can I get the other bars that I want into my chart?
Thanks.
J
 		Jane 12/11/2008

Hi,
unfortunately Query Designer do not supprot UNION.

Use SQL pane to edit your SQL query.
D
 		dbowen author 12/11/2008

Hi,

unfortunately Query Designer do not supprot UNION.

Use SQL pane to edit your SQL query.


I figured out that much. The problem is that even the SQL pane does not preserve my hand typed SQL. How can I get it to keep the code that I typed?
In my mind it is a serious bug when the application changes your custom code to something other than exactly what is typed.
J
 		Jane 12/12/2008

Hi,
just don't use Query Designer and edit SQL queries manually.

I recommend you to create view in the database directly for queries with UNION.
D
 		dbowen author 12/12/2008

Hi,

just don't use Query Designer and edit SQL queries manually.

I recommend you to create view in the database directly for queries with UNION.


I don't think you understand what I'm saying. I AM NOT using the query designer. I AM typing the SQL directly but PHPRunner does not preserve what I typed. I think it is a serious bug if it cannot at least preserve what I typed.
J
 		Jane 12/15/2008

Hi,
unfortunately PHPRunner do not support UNION on the Edit SQL query tab.
C
 		Conrunner 7/19/2009

Hi

I have worked on this particular problem for a long time and have consistently had the same problem as yourself. Graph queries work fine on ONE table, but when you start trying to use UNION queries involving more than one table the only way I have been able to surpass it is to create a VIEW based on the UNION query and then to have the graph look at SELECT * from that VIEW.

It does work. I use SQL Server Management Studio Express to create the view. I have previously noted that ASPRunner seems to have a problem with displaying the results of several queries in "real time" (on the server once you have uploaded the project) despite the fact that it shows the results of the query perfectly when you go to the "Results" pane of the "Query" section of the project when you are building it before you upload it to the server. Create a view - Then get the page to look at that view - It always works - The UNION query is only one example of several where I have noted this problem. At the current time, my rule is - if the query dosen't work in ASPRunner then create a VIEW and try again - It usually does work after this. Hope this helps

Con.