Creating MySQL View using UNION
Solution 1
You might want to swith the order of userid and location in the second select. The column names should match 1 to 1 in all selects of the union.
EDIT : For query browser , as this points out "To create a view from a query, you must have executed the query successfully. To be more precise, the view is created from the latest successfully executed query, not necessarily from the query currently in the Query Area"
so you need to execute the query first before you create the view in query browser.
The error is from the query browser and not mysql.
Solution 2
You have different types being unioned into the same column. (The names can be different, but the types have to be the same, or at least auto-castable.) But as @Learning points out, it looks like you've twisted the SELECT column enumerations.
Just in case, the proper syntax (which worked for me) is
CREATE VIEW myView
AS
SELECT ...
![Admin](/assets/logo_square_200-5d0d61d6853298bd2a4fe063103715b4daf2819fc21225efa21dfb93e61952ea.png)
Admin
Updated on July 09, 2022Comments
-
Admin almost 2 years
I am trying to create a view for the following query.
SELECT DISTINCT products.pid AS id, products.pname AS name, products.p_desc AS description, products.p_loc AS location, products.p_uid AS userid, products.isaproduct AS whatisit FROM products UNION SELECT DISTINCT services.s_id AS id, services.s_name AS name, services.s_desc AS description, services.s_uid AS userid, services.s_location AS location, services.isaservice AS whatisit FROM services
But not able to do so. I am using MySql query browser. The error I am getting is:
A view can only be created from an active resultset of SELECT command
Can someone please help me with this?
-
Learning over 15 yearsif column names are different what column name would be reported when we do a select * from the view?
-
dkretz over 15 yearsThe first one. Try it and see (which is what I did.)
-
Admin over 15 yearsThe UNION part is working fine.. But I when I try to create a view for this UNION. I'm getting the following error: A view can only be created from an active resultset of SELECT command I amd using MySql query browser
-
Learning over 15 yearsoh .. ok. Sybase is less forgiving. Thanks for the clarification.
-
dkretz over 15 yearsAre you able to create a view at all? Try it with just "CREATE VIEW vWhatever AS SELECT pid FROM products" and work forward from there.
-
dkretz over 15 years@Learning, are you trying these suggestions? I didn't need to execute the query first. I executed some other query, then executed "CREATE VIEW ... " and it worked fine.
-
dkretz over 15 yearsOK, just add pieces from there until you know which part makes it fail.
-
Learning over 15 yearsno I'm not. I do not have access to mysql. I'm just trying to help.
-
dkretz over 15 yearsYour link suggests you might be right - I don't have that query browser, but it works ok in sqlyog.