SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Enthusiast omid020's Avatar
    Join Date
    Feb 2008
    Posts
    65
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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 :

    Code:
    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

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,014
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    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?
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast omid020's Avatar
    Join Date
    Feb 2008
    Posts
    65
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    by the way, that query looks ~awfully~ familiar, especially "humpty" -- ain't he cute?
    Wow , that`s a big chance , our world is very small
    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 didn`t reply to my questions about this problem , so it seems I should forget tek-tips.com forever 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

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,014
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    have you run that query by itself? does it work? if so, you should be able to create a view on it
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Enthusiast omid020's Avatar
    Join Date
    Feb 2008
    Posts
    65
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    have you run that query by itself? does it work? if so, you should be able to create a view on it
    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 :
    Code:
    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 ?

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,014
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    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?
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Enthusiast omid020's Avatar
    Join Date
    Feb 2008
    Posts
    65
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    here's an idea... can you declare a view for the subquery itself?
    Yes , I Can ; This is view query related to subquery :
    Code:
    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 |
    +----------+------------+-------+--------+

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,014
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    okay, now can you create this view --
    Code:
    CREATE VIEW `final-vision2`.`VIEW-Q2` 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`.`VIEW-Q`
    GROUP BY leafCode
         , colorsCode
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Enthusiast omid020's Avatar
    Join Date
    Feb 2008
    Posts
    65
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    excellent , works perfectly .
    Thanks


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •