trouble with table-valued function

SQL Apprentice Question
Weird problem. I have a table-valued function (not inline) called
SalesByKeyword which returns a table of sales stats for products
containing a keyword/phrase in their descriptions. The function works
fine when I pass a constant into it as the keyword, but I'm trying to
have it evaluate for each row of the join. ST04.SimpleText is the
keyword for each row.

I get the error 'Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "ST04.SimpleText" could not be bound.'


SELECT TOP 50 LTRIM(RTRIM(LOWER(ST04.SimpleText))) AS [Text],
COUNT(*) AS [Count],
CONVERT(smallint, CONVERT(decimal(7, 0), SUM(ResultCount)) /
CONVERT(decimal(7, 0), COUNT(*))) AS AvgResults,
SBK.OrderCount,
SBK.LineCount,
SBK.TotalValue
FROM wsite.dbo.ST04_Search ST04
LEFT OUTER JOIN wsite.dbo.SalesByKeyword(ST04.SimpleText,
'05/16/2006', '05/17/2006') AS SBK
ON (SBK.Keyword = ST04.SimpleText)
WHERE (
(ST04.SimpleText IS NOT NULL) AND
(ST04.Created >= '05/16/2006') AND
(ST04.Created < '06/17/2006') AND
(ST04.StartRec = 1)
)
GROUP BY LTRIM(RTRIM(LOWER(ST04.SimpleText))), SBK.OrderCount,
SBK.LineCount, SBK.TotalValue
ORDER BY [Count] DESC, [Text]


Why, why!!!



Celko Answers


>> I have a table-valued function (not inline) called SalesByKeyword which returns a table of sales stats for products containing a keyword/phrase in their descriptions. The function works fine when I pass a constant into it as the keyword, but I'm trying to have it evaluate for each row of the join. ST04.SimpleText is the keyword for each row. <<


I would dump the proprietary table-valued function in favor of real
SQL. All you are going to do is sink further into a proprietary hole
that cannot be ported, maintained or optimized

You might want to use ISO-8601 format dates, CAST() instead of
CONVERT(), etc. so later programmers can read and maintain your code.


Question: why are you trimming and CASTING () data from a table?
Answer: the tables have no constraints or proper data types. You need
to fix that as soon as possible, or you will spend your life writing
these convolved queries.


And finally, what is this supposed to do? The average of something
related to a keyword set is weird. I woudl have expected to see
something like "WHERE O.product_descrip LIKE '%' + K.keyword +'%'"
from your narrative.