May 26, 2005 at 4:53 pm
I am trying to run this select for a report in RS and it keeps giving an error that the column prefix 'a' does not match with a table name or alias named used in the query. I cant for the life of me work out why. ANy help would be grealtly appreciated.
SELECT 'Dear ' + n.FirstName AS Salutation,
'RE: WHOLE HERD TB TEST FOR YOUR CATTLE HERD NUMBER ' + CONVERT(varchar, n.HerdId) AS Regarding,
'This letter is to advise that the above cattle herd is due for a routine Tb test on the following class of animals: ' + n.StockType + ' ' + 'and the type of test is' + n.TestType + '.'AS FirstParagraph,
'You will be contacted by your Tb tester to arrange a suitable test date.' AS SecondParagraph,
'Please telephone the Animal Health Board on 0800 4 TBINFO (0800 482 4636) if you have any further inquiries. It will be helpful if you have this letter to hand when you call. ' AS ThirdParagraph,
n.*
FROM (Select tr.Id as TestRequestId, p.FirstName, p.FullAddressName, a.AddressLine1, a.AddressLine2, a.Town, a.PostCode, p.FullLegalName, h.Id AS HerdID, ttli.Name AS TestType, stli.Name AS Stock
FROM TestRequest tr
INNER JOIN Herd h ON h.Id = tr.HerdId
INNER JOIN HerdTypeLookupItem htli on htli.Id = h.HerdTypeLookupItemId
INNER JOIN StockTypeLookupItem stli ON stli.Id = tr.StockTypeLookupItemId
INNER JOIN TestTypeLookupItem ttli ON ttli.Id = tr.TestTypeLookupItemId
INNER JOIN Person p ON p.Id = dbo.GetHerdContact (tr.Id,'T',cast(convert(varchar(10),getdate(),101) as datetime))
INNER JOIN Address a ON a.ParentObjectId = p.Id AND a.ParentObjectTypeInd = ' P ')
as n
WHERE (a.DeletedDate IS NULL) AND (a.AddressTypeLookupItemId = 1) --AND (tr.Id = @testRequestId)
May 26, 2005 at 9:34 pm
You have 2 FROM statements...
Don't know what this is:
"FROM (Select tr.Id as TestRequestId, p.FirstName, p.FullAddressName, a.AddressLine1, a.AddressLine2, a.Town, a.PostCode, p.FullLegalName, h.Id AS HerdID, ttli.Name AS TestType, stli.Name AS Stock"
**ASCII stupid question, get a stupid ANSI !!!**
May 26, 2005 at 9:37 pm
The alias "a" referenced in the WHERE clause is not valid as there is no table with that alias. "a' is only an alias within the definition of the derived table "n".
You might be able to move everthing in the where clause into the derived table definition as join condtiions.
Edit: You can move the entire where clause to be inside the derived table definition. Also, as there is nothing but a derived table it would seem that it can just be removed and go with joins to all those tables.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply