|
|
Posted by Beowulf on 06/23/06 15:09
Erland Sommarskog wrote:
>> <snip>
>> 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.
Wow. My assumption was that I was going to have to get into some heavy
duty SQL hackery, but it really is quite simple. This even works
correctly if there actually is Spanish text. I had come up with
something of a workaround that would return NULL for me for other
languages if the only text was English, but it returned multiple records
if there was English and Spanish.
Thanks so much for the reply.
Navigation:
[Reply to this message]
|