Myphpadmin - problem creating VIEW

Databases
1

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

try this –

CREATE VIEW myview
AS
SELECT * FROM (
SELECT humpty AS egg
  FROM eggs
UNION ALL
SELECT dumpty AS egg
  FROM eggs
) AS u
3

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

what version are you on? i created a view in 5.5 from a union query easily

try this –

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

5

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