2. PHPRunner General questions
  3. [SOLVED]  UNION ALL and Subquery of MySQL view
This topic is locked
[SOLVED]

 UNION ALL and Subquery of MySQL view

 		3/5/2014 10:52:58 AM
PHPRunner General questions
F
 F5447 author

Hi Expert,
To bad is that PHPRunner does not support UNION ALL (Actually it supports, for first time saving, second saving, PHPRunner removes the UNION ALL).
I have the following UNION ALL Query:

SELECT ID, DRAWDATE, DRAWNO, AGENT, DIGIT, COUNT(*) AS OCCURRENCE FROM (
SELECT ID, DRAWDATE, DRAWNO, AGENT, D1 AS DIGIT FROM Table1

UNION ALL

SELECT ID, DRAWDATE, DRAWNO, AGENT, D2 AS DIGIT FROM Table1

UNION ALL

SELECT ID, DRAWDATE, DRAWNO, AGENT, D3 AS DIGIT FROM Table1

UNION ALL

SELECT ID, DRAWDATE, DRAWNO, AGENT, D4 AS DIGIT FROM Table1
) AS BaseView
GROUP BY DIGIT, AGENT

ORDER BY OCCURRENCE DESC, AGENT


I tried to transform to MySQL view, but sadly to say is that MySQL return the error:

SQL Error (1349): View's SELECT contains a subquery in the FROM clause
Question:

Any idea how to transform the above queries to join query?
My Expected result is ...



DIGIT OCCURRENCE AGENT DRAWDATE DRAWNO

0 15 Agent 1 30/1/2014 M030/14

1 14 Agent 1 30/1/2014 M030/14

9 12 Agent 1 30/1/2014 M030/14

3 11 Agent 1 30/1/2014 M030/14

2 9 Agent 1 30/1/2014 M030/14

4 8 Agent 1 30/1/2014 M030/14

7 7 Agent 1 30/1/2014 M030/14

8 6 Agent 1 30/1/2014 M030/14

6 6 Agent 1 30/1/2014 M030/14

5 4 Agent 1 30/1/2014 M030/14
S
 		Stucco 3/5/2014

Can you create a PHPRunner View or a MySQL View of the FROM subquery instead of building it within a subquery?
F
 		F5447 author 3/5/2014

Thanks .. Stucco
Got ... but, if possible, I would like to enhance the result page further as follows:

DIGIT OCCURRENCE AGENT DRAWDATE DRAWNO D1 D2 D3 D4

0 15 Agent 1 30/1/2014 M030/14 7 3 1 4

1 14 Agent 1 30/1/2014 M030/14 3 3 6 2

9 12 Agent 1 30/1/2014 M030/14 0 4 4 4

3 11 Agent 1 30/1/2014 M030/14 2 5 4 0

2 9 Agent 1 30/1/2014 M030/14 0 0 3 6

4 8 Agent 1 30/1/2014 M030/14 4 2 2 0

7 7 Agent 1 30/1/2014 M030/14 4 3 0 0

8 6 Agent 1 30/1/2014 M030/14 0 0 3 3

6 6 Agent 1 30/1/2014 M030/14 1 1 0 4

5 4 Agent 1 30/1/2014 M030/14 2 2 0 0


Wonder this can be done or not ...
Admin 3/5/2014

I guess you need to extend your subqueries retrieving all four variables (D1, D2, D3 and D4) instead of just one.
A
 		Anapolis 3/6/2014

I know UNION ALL is not sustainable written in the Query Builder for a page or Custom View but I am wondering if you are only trying to do a Visual Report, so to speak, if UNION ALL is also broken when written in the Events view?
Or, if UNION ALL is not supported as a custom query in Events does not the ability to create additional files that are exported in the OUTPUT/ folder open up the possibility to create all of the PHP code that would support the UNION ALL queries as a new function that can be called from within a List page, for instance?
I suppose it would have to be given its own Paging and Export functions, too, since I am guessing PHPRunner's Paging and Export would not apply to a custom function that operates independently of PHPRunner.
I am deep into a project on a deadline so I will not have time to check this out before opening my mouth and sticking my foot in here!