Post reply

Migration from SQL 2000 to SQL 2005

  • June 2, 2010 at 9:21 am

    #221695

    Am new to SQL..

    We are migrating from SQL 2000 to SQL 2005. Can any one help me in the below script.

    Please help me how to rewrite the following script.

    select 'Past Year',

    t.Industry,

    t.BusinessLine,

    t.Segment,

    TotalNetInvestmentAmount,

    TotalCount,

    ISNULL(WatchNetInvestmentAmount,0) WatchNetInvestmentAmount,

    ISNULL(WatchCount,0)WatchCount ,

    ISNULL(InventoryNetInvestmentAmount,0)InventoryNetInvestmentAmount ,

    ISNULL(InventoryCount,0) InventoryCount

    from LastTotalSummary t,

    LastWatchSummary wa,

    LastInventorySummary i

    where t.Industry *= wa.Industry

    and t.BusinessLine *= wa.BusinessLine

    and t.Segment *= wa.Segment

    and t.Industry *= i.Industry

    and t.BusinessLine *= i.BusinessLine

    and t.Segment *= i.Segment

    Thanks in advance....

  • June 2, 2010 at 10:09 am

    #1175406

    Does this not work in SQL 2005?

    It's old style SQL, not ANSI, so you ought to rewrite it.

    Instead of listing each table and then having a comma you would want to separate the tables by "inner join" or "left outer join" or "right outer join" and include an ON clause.

    Something like this

    from LastTotalSummary t

    left outer join LastInventorySummary i

    on t.Industry *= wa.Industry

    left outer join LastWatchSummary wa

    on t.BusinessLine = wa.BusinessLine

    You replace the *= with a left outer join. If you have more than on criteria, you can include it with an AND in the ON clause.

    http://www.sqlservercentral.com/articles/Miscellaneous/joinsclarified/1333/

  • June 2, 2010 at 11:55 am

    #1175482

    Thanks a lot Steve..It worked..

Viewing 3 posts - 1 through 3 (of 3 total)

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