Menu
I have a mysql query that uses union to join multiple queries into one result set. the query works perfectly.
When I want to use the same query to create a view I then get an error message:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'union select hulaminloadnumber2,deliveryid,drop1customer from localjhb where hul' at line 2
my query that works perfectly is:
select hulaminloadnumber1,deliveryid,drop1customer from localjhb where hulaminloadnumber1>0
union
select hulaminloadnumber2,deliveryid,drop1customer from localjhb where hulaminloadnumber2>0
union
select hulaminloadnumber3,deliveryid,drop1customer from localjhb where hulaminloadnumber3>0
union
select hulaminloadnumber4,deliveryid,drop1customer from localjhb where hulaminloadnumber4>0
union
select hulaminloadnumber5,deliveryid,drop1customer from localjhb where hulaminloadnumber5>0
my query to create the view is:
create view View_LoadvsCustomer as (
select hulaminloadnumber1,deliveryid,drop1customer from localjhb where hulaminloadnumber1>0
union
select hulaminloadnumber2,deliveryid,drop1customer from localjhb where hulaminloadnumber2>0
union
select hulaminloadnumber3,deliveryid,drop1customer from localjhb where hulaminloadnumber3>0
union
select hulaminloadnumber4,deliveryid,drop1customer from localjhb where hulaminloadnumber4>0
union
select hulaminloadnumber5,deliveryid,drop1customer from localjhb where hulaminloadnumber5>0)
this produces the below error in PHPMyadmin:
The queries are all from a badly designed table so there should be no formatting or collation issues. Can a view be created against a union query?
Any advice is appreciated.
Have a GREAT weekend, Ryan
566
The basic syntax for creating a view in MySQL is as follows: CREATE VIEW [db_name.] view_name [(column_list)] AS select-statement; [db_name.] is the name of the database where your view will be created; if not specified, the view will be created in the current database.
You can use UNION if you want to select rows one after the other from several tables or several sets of rows from a single table all as a single result set. UNION is available as of MySQL 4.0.
By default, a new view is created in the default database. To create the view explicitly in a given database, use db_name. view_name syntax to qualify the view name with the database name: CREATE VIEW test.
Remove the parenthesis from view definition. You have hit this server bug.
182
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
