Click here to Skip to main content
15,880,134 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi,

i'd like to create a view by "union selecting" a column with the same name in multiple tables and selecting from this view.
I used this tutorial [^] in different way's but it still don't work:

C#
string selectCommandTemperature = "SELECT TOP " + countTempe + " [T/°C] FROM (CREATE VIEW temp AS (SELECT [T/°C] FROM Q3B UNION SELECT [T/°C] FROM Q3D))";

C#
SqlCommand sqlCommandTemperature = new SqlCommand(selectCommandTemperature, sqlConnectionError);

C#
SqlDataReader startPageTemperature = sqlCommandTemperature.ExecuteReader();


this construction cause an SqlException: wrong syntax near 'CREATE'

i'm a sql-noob and never worked with sql before so may you can help me.
Posted

create the view in your database. in your query call the view
 
Share this answer
 
Comments
diialer 5-Aug-11 10:12am    
How can i create the view in the database?
(the database is far away (geographical) and i only've got permissions to select in 2 databases with about 1000 tables each)
Herman<T>.Instance 5-Aug-11 10:14am    
create a view is a sql command. when that is run the view is created and can be used: http://msdn.microsoft.com/en-us/library/ms187956.aspx
diialer 5-Aug-11 10:32am    
just got the rights to create and delete views...
thanks
It doesn't like the fact that you're trying to Create a view, while trying to read from it at the same time. Mine also returns
'CREATE VIEW' must be the first statement in a query batch
.

As digimanus suggests, first create the view, then try to query data from it.


string selectCommandTemperature = "SELECT TOP " + countTempe + " [T/°C] FROM (SELECT [T/°C] FROM Q3B
UNION 
SELECT [T/°C] FROM Q3D) as t";
 
Share this answer
 
v2
Comments
diialer 5-Aug-11 10:24am    
already tried this
string selectCommandTemperature = "CREATE VIEW temp AS (SELECT [T/°C] FROM Q3B UNION SELECT [T/°C] FROM Q3D); SELECT TOP " + countTempe + " [T/°C] FROM temp";
also doesn't work...
UJimbo 5-Aug-11 10:37am    
Try the above as well if you don't want to query without creating a View
diialer 5-Aug-11 10:40am    
no the problem was that i'd no permissions to create views -.-
work with the solution to create a view and select from the view now...
Herman<T>.Instance 5-Aug-11 10:31am    
try string selectCommandTemperature = "CREATE VIEW temp AS (SELECT [T/°C] FROM Q3B UNION SELECT [T/°C] FROM Q3D); "
later you can use the view in the query like SELECT TOP 10 * FROM temp

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900