InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 19 Jun 2013 11:26:58 GMT20http://www.sqlservercentral.com/Forums/Topic1400685-392-1.aspx[quote][b]dan-572483 (1/2/2013)[/b][hr]If all the queries in combined by the UNION statement are quering the same table (or differenet tables but the coumn names are the same), then you shoudn't need to use AS <header>, correct?[/quote]You don't need the AS header in SQL Server at all. SQL gets the result column names from the [i]first[/i] query in a UNION. The others can have different column names -- or no column names at all.[code="sql"]SELECT 1 AS a, 2 AS bunion allselect 3 as c, 4 as dunion allselect 5 as e, 6 as f union allselect 7, 8[/code]Wed, 02 Jan 2013 12:39:25 GMTScottPletcherhttp://www.sqlservercentral.com/Forums/Topic1400685-392-1.aspxIf all the queries in combined by the UNION statement are quering the same table (or differenet tables but the coumn names are the same), then you shoudn't need to use AS <header>, correct?Wed, 02 Jan 2013 10:55:56 GMTdan-572483http://www.sqlservercentral.com/Forums/Topic1400685-392-1.aspx[quote][b]CELKO (12/27/2012)[/b][hr][quote]All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists."![/quote]The term for this is "union compatible" and it means that both tables in a set operation have to have the same number of columns and those columns have to match by data type and by position. Further more, the result of a set operation is a table whose columns have no names. This means that you need to write an AS clause to name them. (<table expr #1> [UNION | INTERSECT | EXCEPT] [ALL] <table expr #2>) [AS] <table name> (<column list>)[/quote]No you don't for SQL Server. The first query provides all the column names.Mon, 31 Dec 2012 14:31:13 GMTScottPletcherhttp://www.sqlservercentral.com/Forums/Topic1400685-392-1.aspxThank You For Repaly!!!!!Fri, 28 Dec 2012 08:52:36 GMThpatelchttp://www.sqlservercentral.com/Forums/Topic1400685-392-1.aspx[quote]All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists."![/quote]The term for this is "union compatible" and it means that both tables in a set operation have to have the same number of columns and those columns have to match by data type and by position. Further more, the result of a set operation is a table whose columns have no names. This means that you need to write an AS clause to name them. (<table expr #1> [UNION | INTERSECT | EXCEPT] [ALL] <table expr #2>) [AS] <table name> (<column list>)Thu, 27 Dec 2012 17:23:09 GMTCELKOhttp://www.sqlservercentral.com/Forums/Topic1400685-392-1.aspxThank You!!!Thu, 27 Dec 2012 11:10:19 GMThpatelchttp://www.sqlservercentral.com/Forums/Topic1400685-392-1.aspxError's pretty clear. When using a union, intersect or except all the queries must have the same number of columnsThu, 27 Dec 2012 11:06:31 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1400685-392-1.aspxThanks for Replay but now i am getting this Error"Msg 205, Level 16, State 1, Procedure XXX#, Line 4All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists."Thanks!!!!Thu, 27 Dec 2012 11:00:57 GMThpatelchttp://www.sqlservercentral.com/Forums/Topic1400685-392-1.aspxALTER VIEW <view name>AS<select statement here>GOThu, 27 Dec 2012 10:05:34 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1400685-392-1.aspxHii am trying to use design mode. but it is throwing error . is there other wayt to add column in exiting view ?Thanks,HirenThu, 27 Dec 2012 09:53:35 GMThpatelc