Question

Why does this view work with a compatibility level of 65 but not when 80 is
selected?
SELECT AccessionNumber, Info = CONVERT(varchar, AccessionNumber) + ' - '
+ tblLibraryBooks.Title + ' (Copy ' + CONVERT(varchar,
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 +
')'
FROM tblLibraryBooks INNER JOIN
tblLibraryBookCopies ON tblLibraryBooks.BookID =
tblLibraryBookCopies.BookID LEFT OUTER JOIN
tblLibraryBookStatusTypes ON
tblLibraryBookCopies.BookStatusID = tblLibraryBookStatusTypes.BookStatusID
Thanks,
Tony
What is the error you are getting?
|||Your logic depends on certain connection settings (whose defaults have
apparently changed between versions) - a string + null = NULL. Therefore,
the answer is to concatenate an empty string, not NULL.
"Tony Schlak" <tony@.optics.arizona.edu> wrote in message
news:%23FC5uPzhFHA.2156@.TK2MSFTNGP14.phx.gbl...
> Why does this view work with a compatibility level of 65 but not when 80
is
> selected?
> SELECT AccessionNumber, Info = CONVERT(varchar, AccessionNumber) + ' -
'
> + tblLibraryBooks.Title + ' (Copy ' + CONVERT(varchar,
> 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
+
> ')'
> FROM tblLibraryBooks INNER JOIN
> tblLibraryBookCopies ON tblLibraryBooks.BookID =
> tblLibraryBookCopies.BookID LEFT OUTER JOIN
> tblLibraryBookStatusTypes ON
> tblLibraryBookCopies.BookStatusID = tblLibraryBookStatusTypes.BookStatusID
>
> Thanks,
> Tony
>
|||Unfortunately, this has already been answered in .programming. Please do
not independently post the same message to multiple newsgroups. You only
increase the work for you and everyone who donates their time to help you.
"Scott Morris" <bogus@.bogus.com> wrote in message
news:uzXZFv6hFHA.1052@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> Your logic depends on certain connection settings (whose defaults have
> apparently changed between versions) - a string + null = NULL. Therefore,
> the answer is to concatenate an empty string, not NULL.
> "Tony Schlak" <tony@.optics.arizona.edu> wrote in message
> news:%23FC5uPzhFHA.2156@.TK2MSFTNGP14.phx.gbl...
> is
' -[vbcol=seagreen]
> '
tblLibraryBookStatusTypes.BookStatus[vbcol=seagreen]
END[vbcol=seagreen]
> +
tblLibraryBookStatusTypes.BookStatusID
>