SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Enthusiast
    Join Date
    Jul 2010
    Posts
    41
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    myphpadmin - problem creating VIEW

    I am trying to create a UNION ALL VIEW on one table and creating, in effect, two records from each original record.

    I can create a view with each of the SELECT portions but when I try to create the VIEW with the UNION ALL query I get a message telling me that I do not have access to this page.

    Simple Views can be created without any problems.

    Any ideas anybody?

    Thanks

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,246
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    try this --
    Code:
    CREATE VIEW myview
    AS
    SELECT * FROM (
    SELECT humpty AS egg
      FROM eggs
    UNION ALL
    SELECT dumpty AS egg
      FROM eggs
    ) AS u
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast
    Join Date
    Jul 2010
    Posts
    41
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I adapted your method for a simple VIEW and got the following error message:

    #1349 - View's SELECT contains a subquery in the FROM clause

    I tried to create a VIEW using half of my query:

    CREATE VIEW transunion AS SELECT transactions.transid AS transid,transactions.creditacc AS acc,transactions.debitacc AS otheracc, 'C' AS type,transactions.value AS value,transactions.transdate AS transdate,transactions.narrativeid AS narrativeid from transactions

    and that was fine but when I tried to use a UNION query as follows:

    CREATE VIEW transunion1 AS SELECT transactions.transid AS transid,transactions.creditacc AS acc,transactions.debitacc AS otheracc, 'C' AS type,transactions.value AS value,transactions.transdate AS transdate,transactions.narrativeid AS narrativeid from transactions union all select transactions.transid AS transid,transactions.debitacc AS acc,transactions.creditacc AS otheracc,'D' AS type,transactions.value AS value,transactions.transdate AS transdate,transactions.narrativeid AS narrativeid from transactions

    I get the following message:

    Access Denied
    You are not allowed to see the page.


    The syntax is fine I beleive as it is based upon my original UNION ALL query.

    Any more ideas?

    Thanks

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,246
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    what version are you on? i created a view in 5.5 from a union query easily

    try this --
    Code:
    CREATE VIEW transunion1 
    AS 
    SELECT transid 
         , creditacc AS acc
         , debitacc AS otheracc
         , 'C' AS type
         , value 
         , transdate 
         , narrativeid 
      from transactions 
    union all 
    select transid 
         , debitacc AS acc
         , creditacc AS otheracc
         , 'D' AS type
         , value 
         , transdate 
         , narrativeid 
      from transactions
    by the way, "you are not allowed to see the page" doesn't sound like a mysql error to me
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Enthusiast
    Join Date
    Jul 2010
    Posts
    41
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    That worked for me too.

    I tried removing the table names from my SQL and it worked, put them back, which was my original SQL, and it also worked.

    I do not understand but at least I have my VIEW now.

    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
  •