|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
SQL 2005 Compatability in a Viewbox. One thing that I have noticed is that one of our DB's is still at 65 for compatibility. I have now migrated the DB and found that certain things do not look the same as they did before. Here is an example of a view that is no longer working properly: SELECT dbo.tblLibraryBookCopies.AccessionNumber, CONVERT(varchar, dbo.tblLibraryBookCopies.AccessionNumber) + ' - ' + dbo.tblLibraryBooks.Title + ' (Copy ' + CONVERT(varchar, dbo.tblLibraryBookCopies.Copy) + CASE WHEN tblLibraryBookCopies.Volume IS NOT NULL THEN ', Vol. ' + tblLibraryBookCopies.Volume ELSE NULL END + CASE WHEN tblLibraryBookStatusTypes.BookStatus IS NOT NULL THEN ', Status: ' + CONVERT(varchar, tblLibraryBookStatusTypes.BookStatus) ELSE NULL END + ')' AS Info FROM dbo.tblLibraryBooks INNER JOIN dbo.tblLibraryBookCopies ON dbo.tblLibraryBooks.BookID = dbo.tblLibraryBookCopies.BookID LEFT OUTER JOIN dbo.tblLibraryBookStatusTypes ON dbo.tblLibraryBookCopies.BookStatusID = dbo.tblLibraryBookStatusTypes.BookStatusID If someone could suggest how to make this sql statement more compatible with the new version of SQL I would greatly appreciate it. Thanks, Tony What does "is no longer working" mean? Are you getting an error? If so,
what error? The T-SQL looks fine to me, but it's hard to tell what to look for without some more information. -- Show quoteAdam Machanic Pro SQL Server 2005, available now http://www.apress.com/book/bookDisplay.html?bID=457 -- "Tony" <t***@optics.arizona.edu> wrote in message news:%23%23pRb8hEGHA.1088@tk2msftngp13.phx.gbl... >I have started to migrate our SQL 2000 tables and views to our new SQL 2005 >box. One thing that I have noticed is that one of our DB's is still at 65 >for compatibility. I have now migrated the DB and found that certain things >do not look the same as they did before. Here is an example of a view that >is no longer working properly: > > SELECT dbo.tblLibraryBookCopies.AccessionNumber, CONVERT(varchar, > dbo.tblLibraryBookCopies.AccessionNumber) > + ' - ' + dbo.tblLibraryBooks.Title + ' (Copy ' + > CONVERT(varchar, dbo.tblLibraryBookCopies.Copy) > + CASE WHEN tblLibraryBookCopies.Volume IS NOT NULL > THEN ', Vol. ' + tblLibraryBookCopies.Volume ELSE NULL > END + CASE WHEN tblLibraryBookStatusTypes.BookStatus > IS NOT NULL THEN ', Status: ' + CONVERT(varchar, > tblLibraryBookStatusTypes.BookStatus) ELSE NULL END + > ')' AS Info > FROM dbo.tblLibraryBooks INNER JOIN > dbo.tblLibraryBookCopies ON > dbo.tblLibraryBooks.BookID = dbo.tblLibraryBookCopies.BookID LEFT OUTER > JOIN > dbo.tblLibraryBookStatusTypes ON > dbo.tblLibraryBookCopies.BookStatusID = > dbo.tblLibraryBookStatusTypes.BookStatusID > > If someone could suggest how to make this sql statement more compatible > with the new version of SQL I would greatly appreciate it. > > Thanks, > Tony > The view works fine until it reaches the CASE statements. The information is
there is just that i cnat figure how to word the sql statement to make it work. Show quote "Adam Machanic" <amachanic@hotmail._removetoemail_.com> wrote in message news:%238pT2NiEGHA.1028@TK2MSFTNGP11.phx.gbl... > What does "is no longer working" mean? Are you getting an error? If so, > what error? > > The T-SQL looks fine to me, but it's hard to tell what to look for without > some more information. > > > -- > Adam Machanic > Pro SQL Server 2005, available now > http://www.apress.com/book/bookDisplay.html?bID=457 > -- > > > "Tony" <t***@optics.arizona.edu> wrote in message > news:%23%23pRb8hEGHA.1088@tk2msftngp13.phx.gbl... >>I have started to migrate our SQL 2000 tables and views to our new SQL >>2005 box. One thing that I have noticed is that one of our DB's is still >>at 65 for compatibility. I have now migrated the DB and found that certain >>things do not look the same as they did before. Here is an example of a >>view that is no longer working properly: >> >> SELECT dbo.tblLibraryBookCopies.AccessionNumber, CONVERT(varchar, >> dbo.tblLibraryBookCopies.AccessionNumber) >> + ' - ' + dbo.tblLibraryBooks.Title + ' (Copy ' + >> CONVERT(varchar, dbo.tblLibraryBookCopies.Copy) >> + CASE WHEN tblLibraryBookCopies.Volume IS NOT NULL >> THEN ', Vol. ' + tblLibraryBookCopies.Volume ELSE NULL >> END + CASE WHEN tblLibraryBookStatusTypes.BookStatus >> IS NOT NULL THEN ', Status: ' + CONVERT(varchar, >> tblLibraryBookStatusTypes.BookStatus) ELSE NULL END >> + ')' AS Info >> FROM dbo.tblLibraryBooks INNER JOIN >> dbo.tblLibraryBookCopies ON >> dbo.tblLibraryBooks.BookID = dbo.tblLibraryBookCopies.BookID LEFT OUTER >> JOIN >> dbo.tblLibraryBookStatusTypes ON >> dbo.tblLibraryBookCopies.BookStatusID = >> dbo.tblLibraryBookStatusTypes.BookStatusID >> >> If someone could suggest how to make this sql statement more compatible >> with the new version of SQL I would greatly appreciate it. >> >> Thanks, >> Tony >> > > I still don't quite understand what your problem is. Can you provide DDL,
sample data, and sample output, as described in the following article: http://www.aspfaq.com/etiquette.asp?id=5006 -- Show quoteAdam Machanic Pro SQL Server 2005, available now http://www.apress.com/book/bookDisplay.html?bID=457 -- "Tony" <t***@optics.arizona.edu> wrote in message news:uBuXTRiEGHA.1124@TK2MSFTNGP10.phx.gbl... > The view works fine until it reaches the CASE statements. The information > is there is just that i cnat figure how to word the sql statement to make > it work. > > > "Adam Machanic" <amachanic@hotmail._removetoemail_.com> wrote in message > news:%238pT2NiEGHA.1028@TK2MSFTNGP11.phx.gbl... >> What does "is no longer working" mean? Are you getting an error? If so, >> what error? >> >> The T-SQL looks fine to me, but it's hard to tell what to look for >> without some more information. >> >> >> -- >> Adam Machanic >> Pro SQL Server 2005, available now >> http://www.apress.com/book/bookDisplay.html?bID=457 >> -- >> >> >> "Tony" <t***@optics.arizona.edu> wrote in message >> news:%23%23pRb8hEGHA.1088@tk2msftngp13.phx.gbl... >>>I have started to migrate our SQL 2000 tables and views to our new SQL >>>2005 box. One thing that I have noticed is that one of our DB's is still >>>at 65 for compatibility. I have now migrated the DB and found that >>>certain things do not look the same as they did before. Here is an >>>example of a view that is no longer working properly: >>> >>> SELECT dbo.tblLibraryBookCopies.AccessionNumber, CONVERT(varchar, >>> dbo.tblLibraryBookCopies.AccessionNumber) >>> + ' - ' + dbo.tblLibraryBooks.Title + ' (Copy ' + >>> CONVERT(varchar, dbo.tblLibraryBookCopies.Copy) >>> + CASE WHEN tblLibraryBookCopies.Volume IS NOT NULL >>> THEN ', Vol. ' + tblLibraryBookCopies.Volume ELSE NULL >>> END + CASE WHEN >>> tblLibraryBookStatusTypes.BookStatus IS NOT NULL THEN ', Status: ' + >>> CONVERT(varchar, >>> tblLibraryBookStatusTypes.BookStatus) ELSE NULL END >>> + ')' AS Info >>> FROM dbo.tblLibraryBooks INNER JOIN >>> dbo.tblLibraryBookCopies ON >>> dbo.tblLibraryBooks.BookID = dbo.tblLibraryBookCopies.BookID LEFT OUTER >>> JOIN >>> dbo.tblLibraryBookStatusTypes ON >>> dbo.tblLibraryBookCopies.BookStatusID = >>> dbo.tblLibraryBookStatusTypes.BookStatusID >>> >>> If someone could suggest how to make this sql statement more compatible >>> with the new version of SQL I would greatly appreciate it. >>> >>> Thanks, >>> Tony >>> >> >> > > Do you wnat the info from the three different tabels that make up the view??
Show quote "Adam Machanic" <amachanic@hotmail._removetoemail_.com> wrote in message news:OUp0WUiEGHA.4036@TK2MSFTNGP09.phx.gbl... >I still don't quite understand what your problem is. Can you provide DDL, >sample data, and sample output, as described in the following article: > > http://www.aspfaq.com/etiquette.asp?id=5006 > > > -- > Adam Machanic > Pro SQL Server 2005, available now > http://www.apress.com/book/bookDisplay.html?bID=457 > -- > > > "Tony" <t***@optics.arizona.edu> wrote in message > news:uBuXTRiEGHA.1124@TK2MSFTNGP10.phx.gbl... >> The view works fine until it reaches the CASE statements. The information >> is there is just that i cnat figure how to word the sql statement to make >> it work. >> >> >> "Adam Machanic" <amachanic@hotmail._removetoemail_.com> wrote in message >> news:%238pT2NiEGHA.1028@TK2MSFTNGP11.phx.gbl... >>> What does "is no longer working" mean? Are you getting an error? If >>> so, what error? >>> >>> The T-SQL looks fine to me, but it's hard to tell what to look for >>> without some more information. >>> >>> >>> -- >>> Adam Machanic >>> Pro SQL Server 2005, available now >>> http://www.apress.com/book/bookDisplay.html?bID=457 >>> -- >>> >>> >>> "Tony" <t***@optics.arizona.edu> wrote in message >>> news:%23%23pRb8hEGHA.1088@tk2msftngp13.phx.gbl... >>>>I have started to migrate our SQL 2000 tables and views to our new SQL >>>>2005 box. One thing that I have noticed is that one of our DB's is still >>>>at 65 for compatibility. I have now migrated the DB and found that >>>>certain things do not look the same as they did before. Here is an >>>>example of a view that is no longer working properly: >>>> >>>> SELECT dbo.tblLibraryBookCopies.AccessionNumber, CONVERT(varchar, >>>> dbo.tblLibraryBookCopies.AccessionNumber) >>>> + ' - ' + dbo.tblLibraryBooks.Title + ' (Copy ' + >>>> CONVERT(varchar, dbo.tblLibraryBookCopies.Copy) >>>> + CASE WHEN tblLibraryBookCopies.Volume IS NOT >>>> NULL THEN ', Vol. ' + tblLibraryBookCopies.Volume ELSE NULL >>>> END + CASE WHEN >>>> tblLibraryBookStatusTypes.BookStatus IS NOT NULL THEN ', Status: ' + >>>> CONVERT(varchar, >>>> tblLibraryBookStatusTypes.BookStatus) ELSE NULL >>>> END + ')' AS Info >>>> FROM dbo.tblLibraryBooks INNER JOIN >>>> dbo.tblLibraryBookCopies ON >>>> dbo.tblLibraryBooks.BookID = dbo.tblLibraryBookCopies.BookID LEFT OUTER >>>> JOIN >>>> dbo.tblLibraryBookStatusTypes ON >>>> dbo.tblLibraryBookCopies.BookStatusID = >>>> dbo.tblLibraryBookStatusTypes.BookStatusID >>>> >>>> If someone could suggest how to make this sql statement more compatible >>>> with the new version of SQL I would greatly appreciate it. >>>> >>>> Thanks, >>>> Tony >>>> >>> >>> >> >> > > > Do you wnat the info from the three different tabels that make up the Yes, and data too. In short, everything that is needed to create the > view?? problem from Query Analyzer/SSMS along your expected results. For example: USE tempdb GO CREATE TABLE MyTable(Col1 int) GO INSERT INTO MyTable VALUES(1) GO CREATE VIEW MyView AS SELECT Col1 FROM MyTable GO SELECT Col1 FROM MyView GO --cleanup DROP VIEW MyView DROP VIEW MyTable GO Expected results: Col1 ----------- 1 -- Show quoteHope this helps. Dan Guzman SQL Server MVP "Tony" <t***@optics.arizona.edu> wrote in message news:%23tyrJciEGHA.2648@TK2MSFTNGP11.phx.gbl... > Do you wnat the info from the three different tabels that make up the > view?? > > > "Adam Machanic" <amachanic@hotmail._removetoemail_.com> wrote in message > news:OUp0WUiEGHA.4036@TK2MSFTNGP09.phx.gbl... >>I still don't quite understand what your problem is. Can you provide DDL, >>sample data, and sample output, as described in the following article: >> >> http://www.aspfaq.com/etiquette.asp?id=5006 >> >> >> -- >> Adam Machanic >> Pro SQL Server 2005, available now >> http://www.apress.com/book/bookDisplay.html?bID=457 >> -- >> >> >> "Tony" <t***@optics.arizona.edu> wrote in message >> news:uBuXTRiEGHA.1124@TK2MSFTNGP10.phx.gbl... >>> The view works fine until it reaches the CASE statements. The >>> information is there is just that i cnat figure how to word the sql >>> statement to make it work. >>> >>> >>> "Adam Machanic" <amachanic@hotmail._removetoemail_.com> wrote in message >>> news:%238pT2NiEGHA.1028@TK2MSFTNGP11.phx.gbl... >>>> What does "is no longer working" mean? Are you getting an error? If >>>> so, what error? >>>> >>>> The T-SQL looks fine to me, but it's hard to tell what to look for >>>> without some more information. >>>> >>>> >>>> -- >>>> Adam Machanic >>>> Pro SQL Server 2005, available now >>>> http://www.apress.com/book/bookDisplay.html?bID=457 >>>> -- >>>> >>>> >>>> "Tony" <t***@optics.arizona.edu> wrote in message >>>> news:%23%23pRb8hEGHA.1088@tk2msftngp13.phx.gbl... >>>>>I have started to migrate our SQL 2000 tables and views to our new SQL >>>>>2005 box. One thing that I have noticed is that one of our DB's is >>>>>still at 65 for compatibility. I have now migrated the DB and found >>>>>that certain things do not look the same as they did before. Here is an >>>>>example of a view that is no longer working properly: >>>>> >>>>> SELECT dbo.tblLibraryBookCopies.AccessionNumber, CONVERT(varchar, >>>>> dbo.tblLibraryBookCopies.AccessionNumber) >>>>> + ' - ' + dbo.tblLibraryBooks.Title + ' (Copy ' >>>>> + CONVERT(varchar, dbo.tblLibraryBookCopies.Copy) >>>>> + CASE WHEN tblLibraryBookCopies.Volume IS NOT >>>>> NULL THEN ', Vol. ' + tblLibraryBookCopies.Volume ELSE NULL >>>>> END + CASE WHEN >>>>> tblLibraryBookStatusTypes.BookStatus IS NOT NULL THEN ', Status: ' + >>>>> CONVERT(varchar, >>>>> tblLibraryBookStatusTypes.BookStatus) ELSE NULL >>>>> END + ')' AS Info >>>>> FROM dbo.tblLibraryBooks INNER JOIN >>>>> dbo.tblLibraryBookCopies ON >>>>> dbo.tblLibraryBooks.BookID = dbo.tblLibraryBookCopies.BookID LEFT >>>>> OUTER JOIN >>>>> dbo.tblLibraryBookStatusTypes ON >>>>> dbo.tblLibraryBookCopies.BookStatusID = >>>>> dbo.tblLibraryBookStatusTypes.BookStatusID >>>>> >>>>> If someone could suggest how to make this sql statement more >>>>> compatible with the new version of SQL I would greatly appreciate it. >>>>> >>>>> Thanks, >>>>> Tony >>>>> >>>> >>>> >>> >>> >> >> > > Without really knowing what you're looking for, change:
CONVERT(varchar, dbo.tblLibraryBookCopies.AccessionNumber) + ' - ' + dbo.tblLibraryBooks.Title + ' (Copy ' + CONVERT(varchar, dbo.tblLibraryBookCopies.Copy) + CASE WHEN tblLibraryBookCopies.Volume IS NOT NULL THEN ', Vol. ' + tblLibraryBookCopies.Volume ELSE NULL END + CASE WHEN tblLibraryBookStatusTypes.BookStatus IS NOT NULL THEN ', Status: ' + CONVERT(varchar, tblLibraryBookStatusTypes.BookStatus) ELSE NULL END + ')' AS Info To: CONVERT(varchar, dbo.tblLibraryBookCopies.AccessionNumber) + ' - ' + dbo.tblLibraryBooks.Title + ' (Copy ' + CONVERT(varchar, dbo.tblLibraryBookCopies.Copy) + CASE WHEN tblLibraryBookCopies.Volume IS NOT NULL THEN ', Vol. ' + tblLibraryBookCopies.Volume ELSE '' END + CASE WHEN tblLibraryBookStatusTypes.BookStatus IS NOT NULL THEN ', Status: ' + CONVERT(varchar, tblLibraryBookStatusTypes.BookStatus) ELSE '' END + ')' AS Info Show quote "Tony" <t***@optics.arizona.edu> wrote in message news:%23%23pRb8hEGHA.1088@tk2msftngp13.phx.gbl... >I have started to migrate our SQL 2000 tables and views to our new SQL 2005 >box. One thing that I have noticed is that one of our DB's is still at 65 >for compatibility. I have now migrated the DB and found that certain things >do not look the same as they did before. Here is an example of a view that >is no longer working properly: > > SELECT dbo.tblLibraryBookCopies.AccessionNumber, CONVERT(varchar, > dbo.tblLibraryBookCopies.AccessionNumber) > + ' - ' + dbo.tblLibraryBooks.Title + ' (Copy ' + > CONVERT(varchar, dbo.tblLibraryBookCopies.Copy) > + CASE WHEN tblLibraryBookCopies.Volume IS NOT NULL > THEN ', Vol. ' + tblLibraryBookCopies.Volume ELSE NULL > END + CASE WHEN tblLibraryBookStatusTypes.BookStatus > IS NOT NULL THEN ', Status: ' + CONVERT(varchar, > tblLibraryBookStatusTypes.BookStatus) ELSE NULL END + > ')' AS Info > FROM dbo.tblLibraryBooks INNER JOIN > dbo.tblLibraryBookCopies ON > dbo.tblLibraryBooks.BookID = dbo.tblLibraryBookCopies.BookID LEFT OUTER > JOIN > dbo.tblLibraryBookStatusTypes ON > dbo.tblLibraryBookCopies.BookStatusID = > dbo.tblLibraryBookStatusTypes.BookStatusID > > If someone could suggest how to make this sql statement more compatible > with the new version of SQL I would greatly appreciate it. > > Thanks, > Tony > Thanks that was the solution i was looking for.
Tony Show quote "Raymond D'Anjou" <rdanjou@canatradeNOSPAM.com> wrote in message news:OLtREZiEGHA.740@TK2MSFTNGP12.phx.gbl... > Without really knowing what you're looking for, change: > CONVERT(varchar, dbo.tblLibraryBookCopies.AccessionNumber) > + ' - ' > + dbo.tblLibraryBooks.Title > + ' (Copy ' > + CONVERT(varchar, dbo.tblLibraryBookCopies.Copy) > + CASE WHEN tblLibraryBookCopies.Volume IS NOT NULL > THEN ', Vol. ' + tblLibraryBookCopies.Volume > ELSE NULL END > + CASE WHEN tblLibraryBookStatusTypes.BookStatus IS NOT NULL > THEN ', Status: ' + CONVERT(varchar, > tblLibraryBookStatusTypes.BookStatus) ELSE > NULL END > + ')' AS Info > > To: > CONVERT(varchar, dbo.tblLibraryBookCopies.AccessionNumber) > + ' - ' > + dbo.tblLibraryBooks.Title > + ' (Copy ' > + CONVERT(varchar, dbo.tblLibraryBookCopies.Copy) > + CASE WHEN tblLibraryBookCopies.Volume IS NOT NULL > THEN ', Vol. ' + tblLibraryBookCopies.Volume > ELSE '' END > + CASE WHEN tblLibraryBookStatusTypes.BookStatus IS NOT NULL > THEN ', Status: ' + CONVERT(varchar, > tblLibraryBookStatusTypes.BookStatus) ELSE > '' END > + ')' AS Info > > "Tony" <t***@optics.arizona.edu> wrote in message > news:%23%23pRb8hEGHA.1088@tk2msftngp13.phx.gbl... >>I have started to migrate our SQL 2000 tables and views to our new SQL >>2005 box. One thing that I have noticed is that one of our DB's is still >>at 65 for compatibility. I have now migrated the DB and found that certain >>things do not look the same as they did before. Here is an example of a >>view that is no longer working properly: >> >> SELECT dbo.tblLibraryBookCopies.AccessionNumber, CONVERT(varchar, >> dbo.tblLibraryBookCopies.AccessionNumber) >> + ' - ' + dbo.tblLibraryBooks.Title + ' (Copy ' + >> CONVERT(varchar, dbo.tblLibraryBookCopies.Copy) >> + CASE WHEN tblLibraryBookCopies.Volume IS NOT NULL >> THEN ', Vol. ' + tblLibraryBookCopies.Volume ELSE NULL >> END + CASE WHEN tblLibraryBookStatusTypes.BookStatus >> IS NOT NULL THEN ', Status: ' + CONVERT(varchar, >> tblLibraryBookStatusTypes.BookStatus) ELSE NULL END >> + ')' AS Info >> FROM dbo.tblLibraryBooks INNER JOIN >> dbo.tblLibraryBookCopies ON >> dbo.tblLibraryBooks.BookID = dbo.tblLibraryBookCopies.BookID LEFT OUTER >> JOIN >> dbo.tblLibraryBookStatusTypes ON >> dbo.tblLibraryBookCopies.BookStatusID = >> dbo.tblLibraryBookStatusTypes.BookStatusID >> >> If someone could suggest how to make this sql statement more compatible >> with the new version of SQL I would greatly appreciate it. >> >> Thanks, >> Tony >> > > "Tony" <t***@optics.arizona.edu> wrote in message Look up "concat_null_yields_null" in BOL but I don't know if SQL 2005 news:OXw8RfiEGHA.1424@TK2MSFTNGP12.phx.gbl... > Thanks that was the solution i was looking for. > > Tony handles this differently. Suggestions for next time you post: It's much easier for us to find a solution when you explain what the problem is, don't just say "It doesn't work". It's also easier to spot a problem when you format the SQL statement correctly.  
Other interesting topics
|
|||||||||||||||||||||||
