Post reply

i want to add column in exiting view

  • December 27, 2012 at 9:53 am

    #272064

    Hi

    i am trying to use design mode. but it is throwing error . is there other wayt to add column in exiting view ?

    Thanks,

    Hiren

  • December 27, 2012 at 10:05 am

    #1571929

    ALTER VIEW <view name>

    AS

    <select statement here>

    GO

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • December 27, 2012 at 11:00 am

    #1571945

    Thanks for Replay but now i am getting this Error

    "Msg 205, Level 16, State 1, Procedure XXX#, Line 4

    All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists."

    Thanks!!!!

  • December 27, 2012 at 11:06 am

    #1571946

    Error's pretty clear. When using a union, intersect or except all the queries must have the same number of columns

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • December 27, 2012 at 11:10 am

    #1571948

    Thank You!!!

  • December 28, 2012 at 8:52 am

    #1572140

    Thank You For Repaly!!!!!

  • December 31, 2012 at 2:31 pm

    #1572610

    CELKO (12/27/2012)

    All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists."!

    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>)

    No you don't for SQL Server. The first query provides all the column names.

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • January 2, 2013 at 10:55 am

    #1572990

    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?

  • January 2, 2013 at 12:39 pm

    #1573050

    dan-572483 (1/2/2013)

    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?

    You don't need the AS header in SQL Server at all. SQL gets the result column names from the first query in a UNION. The others can have different column names -- or no column names at all.

    SELECT 1 AS a, 2 AS b

    union all

    select 3 as c, 4 as d

    union all

    select 5 as e, 6 as f

    union all

    select 7, 8

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply