|
|
Posted by Erland Sommarskog on 06/21/06 21:51
Beowulf (beowulf_is_not_here@hotmail.com) writes:
> I'm updating a report to be "multi-language" capable. Previously,
> any items that had text associated with them were unconditionally
> pulling in the English text. The database has always been capable of
> storing multiple languages for an item, however.
>
> Desired output:
> Given the test data below, I'd like to get the following results
Thanks for the extensive repro!
Then again, the fix is simple:
> CREATE FUNCTION Annotated_Check (@Lang_ID int) RETURNS TABLE AS RETURN (
> SELECT tblCheck.Item_ID, tblCheck.CheckDescr AS Condition,
> tblQuestionText.QuestionText
> FROM tblCheck LEFT OUTER JOIN tblQuestionText ON (tblCheck.Item_ID =
> tblQuestionText.Item_ID)
> WHERE ((tblQuestionText.LanguageReference = @Lang_ID) OR
> (tblQuestionText.LanguageReference IS NULL))
> )
Change WHERE to AND and skip last condition on IS NULL:
CREATE FUNCTION mytestfunc (@Lang_ID int) RETURNS TABLE AS RETURN (
SELECT C.Item_ID, C.CheckDescr AS Condition, QT.QuestionText
FROM tblCheck C
LEFT JOIN tblQuestionText QT ON C.Item_ID = QT.Item_ID
AND QT.LanguageReference = @Lang_ID
)
This is a classic error on the left join operator - yes, I did it
too! But once you understand it, it's apparent:
The whole FROM JOIN forms a table which is then filtered by WHERE.
In this case the LEFT JOIN as you had written it, never produced
any rows with NULL in the QuestionText columns, as there was a match
for all. But when you move the condition on language to the ON
clause, you only get a matych if the language is the desired one.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|