I need to create view but my query has one subquery!

omid020 · January 10, 2010, 2:23pm

I know that MySQL has restriction on creating views for those queries that have subqueries like my query but could you please help me to rewrite this query to resolve this issue ? What should I do to create view from this query :

SELECT leafCode
     , colorsCode
     , COALESCE(SUM(`INPUT`), 0) AS `INPUT`
     , COALESCE(SUM(`OUTPUT`), 0) AS `OUTPUT`
     , COALESCE(SUM(`INPUT`), 0) - COALESCE(SUM(`OUTPUT`), 0) AS `INVENTORY`
  FROM ( SELECT rs_leaves.leafCode
              , rs_colors.colorsCode
              , SUM(receipt_details.recAmount) AS `INPUT`
              , NULL AS `OUTPUT`
           FROM receipt_details
         INNER JOIN rs_leaves
             ON rs_leaves.leafID = receipt_details.leafName
         INNER JOIN rs_colors
             ON rs_colors.colorsID = receipt_details.leafColor
         GROUP BY rs_leaves.leafCode
              , rs_colors.colorsCode
         UNION ALL
         SELECT rs_leaves.leafCode
              , rs_colors.colorsCode
              , NULL AS `INPUT`
              , SUM(assign_details.assAmount) AS `OUTPUT`
           FROM assign_details
         INNER JOIN rs_leaves
             ON rs_leaves.leafID = assign_details.leafName
         INNER JOIN rs_colors
             ON rs_colors.colorsID = assign_details.leafColor
         GROUP BY rs_leaves.leafCode
              , rs_colors.colorsCode
       ) AS humpty
GROUP BY leafCode
     , colorsCode

r937 · January 10, 2010, 2:49pm

what version of mysql are you running?

views aren’t supported prior to version 5.0

and yes, views can include subqueries

by the way, that query looks ~awfully~ familiar, especially “humpty” – ain’t he cute?

omid020 · January 10, 2010, 3:40pm

Wow , thats a big chance :good: , our world is very small :lympics: Rudy , if you remember above code is your idea on tek-tips.com forum : http://www.tek-tips.com/viewthread.cfm?qid=1584918 Last week I found that I have problem with logging into tek-tips.com and unfortunately tek-tips.com moderator didnt reply to my questions about this problem , so it seems I should forget tek-tips.com forever :injured::sick: but I`m happy that I can meet you everywhere SQL lives

I read some sources about views but none of them gave me hint about my issue . Also I didn`t find similar sample .

Now I`m using MySQL 5.0.51b .

Regards

r937 · January 10, 2010, 7:18pm

have you run that query by itself? does it work? if so, you should be able to create a view on it

omid020 · January 11, 2010, 4:27am

Yes, I can run it by itself but when I want to create view in MySQL Query Browser application , I receive this error :
Script line: 1 View’s SELECT contains a subquery in the FROM clause

And this is (view) query :

CREATE VIEW `final-vision2`.`VIEW-Q` AS
SELECT leafCode
     , colorsCode
     , COALESCE(SUM(`INPUT`), 0) AS `INPUT`
     , COALESCE(SUM(`OUTPUT`), 0) AS `OUTPUT`
     , COALESCE(SUM(`INPUT`), 0) - COALESCE(SUM(`OUTPUT`), 0) AS `INVENTORY`
  FROM ( SELECT rs_leaves.leafCode
              , rs_colors.colorsCode
              , SUM(receipt_details.recAmount) AS `INPUT`
              , NULL AS `OUTPUT`
           FROM receipt_details
         INNER JOIN rs_leaves
             ON rs_leaves.leafID = receipt_details.leafName
         INNER JOIN rs_colors
             ON rs_colors.colorsID = receipt_details.leafColor
         GROUP BY rs_leaves.leafCode
              , rs_colors.colorsCode
         UNION ALL
         SELECT rs_leaves.leafCode
              , rs_colors.colorsCode
              , NULL AS `INPUT`
              , SUM(assign_details.assAmount) AS `OUTPUT`
           FROM assign_details
         INNER JOIN rs_leaves
             ON rs_leaves.leafID = assign_details.leafName
         INNER JOIN rs_colors
             ON rs_colors.colorsID = assign_details.leafColor
         GROUP BY rs_leaves.leafCode
              , rs_colors.colorsCode
       ) AS humpty
GROUP BY leafCode
     , colorsCode

Also MySQL manual confirm above error :

A view definition is subject to the following restrictions:

*The SELECT statement cannot contain a subquery in the FROM clause.
*The SELECT statement cannot refer to system or user variables.
*Within a stored program, the definition cannot refer to program parameters or local variables.
*The SELECT statement cannot refer to prepared statement parameters.

It seems I should change my code structure but could I change my code structure and get same result ?

r937 · January 11, 2010, 9:42am

i was not aware of that restriction, sorry

looks like you will not be able to declare a view on the entire query

here’s an idea… can you declare a view for the subquery itself?

omid020 · January 11, 2010, 12:56pm

Yes , I Can ; This is view query related to subquery :

CREATE VIEW `final-vision2`.`VIEW-Q` AS
SELECT rs_leaves.leafCode
              , rs_colors.colorsCode
              , SUM(receipt_details.recAmount) AS `INPUT`
              , NULL AS `OUTPUT`
           FROM receipt_details
         INNER JOIN rs_leaves
             ON rs_leaves.leafID = receipt_details.leafName
         INNER JOIN rs_colors
             ON rs_colors.colorsID = receipt_details.leafColor
         GROUP BY rs_leaves.leafCode
              , rs_colors.colorsCode
         UNION ALL
         SELECT rs_leaves.leafCode
              , rs_colors.colorsCode
              , NULL AS `INPUT`
              , SUM(assign_details.assAmount) AS `OUTPUT`
           FROM assign_details
         INNER JOIN rs_leaves
             ON rs_leaves.leafID = assign_details.leafName
         INNER JOIN rs_colors
             ON rs_colors.colorsID = assign_details.leafColor
         GROUP BY rs_leaves.leafCode
              , rs_colors.colorsCode;

And this is result of view :
±---------±-----------±------±-------+
| leafCode | colorsCode | INPUT | OUTPUT |
±---------±-----------±------±-------+
| A | 1014 | 200 | NULL |
| A | 1032 | 30 | NULL |
| B | 2000 | 112 | NULL |
| E | 1032 | 34 | NULL |
| I | 2000 | 36 | NULL |
| I | 6028 | 8 | NULL |
| A | 1014 | NULL | 24 |
| A | 1020 | NULL | 30 |
| B | 2000 | NULL | 8 |
| E | 1032 | NULL | 12 |
| I | 1014 | NULL | 20 |
| I | 2000 | NULL | 6 |
| N | 1020 | NULL | 5 |
±---------±-----------±------±-------+

r937 · January 11, 2010, 1:11pm

okay, now can you create this view –

CREATE VIEW `final-vision2`.`[COLOR="Blue"]VIEW-Q2[/COLOR]` AS
SELECT leafCode
     , colorsCode
     , COALESCE(SUM(`INPUT`), 0) AS `INPUT`
     , COALESCE(SUM(`OUTPUT`), 0) AS `OUTPUT`
     , COALESCE(SUM(`INPUT`), 0) - COALESCE(SUM(`OUTPUT`), 0) AS `INVENTORY`
  FROM `final-vision2`.`[COLOR="Red"]VIEW-Q[/COLOR]`
GROUP BY leafCode
     , colorsCode

omid020 · January 11, 2010, 1:34pm

excellent , works perfectly .
Thanks