Subquery Hell - Need Help
-
Ok here goes, I have the following script.....that for some reason and Im obviously totally blind.....but it wont work. The application we are building needs to fire out a couple of letters etc when certain conditions are met. I am using SQL Reporting services for this, but due to field lengths etc its not advisable to use textboxes for some of the fields. The solution has been to code all the text into an SQL statement.
I think I possibly have just got my selects things around the wrong way.....but I keep getting Invalid Column Name for TotalAnimalsSlaughtered.
Any help would be greatly appreciated.
Select 'Dear' + ' ' + p.FullLegalName as Expr1, 'RE: SLAUGHTER OF REACTORS FROM YOUR' + ' ' + upper(stli.Name) + ' ' + 'HERD NUMBER' + ' ' + CONVERT(varchar(255), h.Id) as Expr2, 'This letter is to confirm that' + ' ' + TotalAnimalsSlaughtered + ' ' + stli.Name + ' ' + 'on your property which have tested positive for bovine tuberculosis have been classified as Tb Reactors and are now required to be slaughtered under Section 122 1(b) of the Biosecurity Act 1993.' as Expr3,
(SELECT tpp.Id AS TestPositiveProfileId, p.FullAddressName, a.AddressLine1, a.AddressLine2, a.Town, a.PostCode, p.FullLegalName, h.Id AS HerdID,
ISNULL(tpp.NumberOfAdultFemales, 0) + ISNULL(tpp.NumberOfAdultMales, 0) + ISNULL(tpp.NumberOfR2yrFemales, 0)
+ ISNULL(tpp.NumberOfR2yrMales, 0) + ISNULL(tpp.NumberOfR1yrFemales, 0) + ISNULL(tpp.NumberOfR1yrMales, 0)
+ ISNULL(tpp.NumberOfOtherFemales, 0) + ISNULL(tpp.NumberOfOtherMales, 0) AS TotalAnimalsSlaughtered, tpp.SlaughterNotificationComment, stli.Name AS SpeciesName
FROM TestPositiveProfile tpp
INNER JOIN TestRequest tr ON tr.Id = tpp.TestRequestId
INNER JOIN Herd h ON h.Id = tr.HerdId
INNER JOIN HerdTypeLookupItem htli ON htli.Id = h.HerdTypeLookupItemId
INNER JOIN SpeciesTypeLookupItem stli ON stli.Id = htli.SpeciesTypeLookupItemId
INNER JOIN Person p ON p.Id = tpp.HerdContactPersonId
INNER JOIN Address a ON a.ParentObjectId = p.Id AND a.ParentObjectTypeInd = 'P'
WHERE (a.DeletedDate IS NULL) AND (a.AddressTypeLookupItemId = 1)
-
I don't see why you are using a subquery. I would remove the "(Select " right after Expr3. This looks like a simple join to me.
Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor -
Your right it is a simple join, but somehow I need to get the total of animals slaughtered into my letter and as thats doing a calculation to get it it doesn't work when I put TotalAnimalsSlaughtered into the first select (get Invalid Column Name).
I thought it would be easier to do a select to get all the required data from the database then do another select to create my letter with all the fields retrieved in the inner select. If this is wrong please point me in a better direction. Cheers for quick reply tho.
-
I agree with Kathi.
SELECT 'Dear' + ' ' + p.FullLegalName as Expr1 , 'RE: SLAUGHTER OF REACTORS FROM YOUR' + ' ' + upper(stli.Name) + ' ' + 'HERD NUMBER' + ' ' + CONVERT(varchar(255), h.Id) as Expr2 , 'This letter is to confirm that' + ' ' + CAST(ISNULL(tpp.NumberOfAdultFemales, 0) + ISNULL(tpp.NumberOfAdultMales, 0) + ISNULL(tpp.NumberOfR2yrFemales, 0) + ISNULL(tpp.NumberOfR2yrMales, 0) + ISNULL(tpp.NumberOfR1yrFemales, 0) + ISNULL(tpp.NumberOfR1yrMales, 0) + ISNULL(tpp.NumberOfOtherFemales, 0) + ISNULL(tpp.NumberOfOtherMales, 0) as varchar(10)) + ' ' + stli.Name + ' ' + 'on your property which have tested positive for bovine ' + 'tuberculosis have been classified as Tb Reactors and are now required to be ' + 'slaughtered under Section 122 1(b) of the Biosecurity Act 1993.' as Expr3 , tpp.Id AS TestPositiveProfileId , p.FullAddressName , a.AddressLine1 , a.AddressLine2 , a.Town , a.PostCode , p.FullLegalName , h.Id AS HerdID , ISNULL(tpp.NumberOfAdultFemales, 0) + ISNULL(tpp.NumberOfAdultMales, 0) + ISNULL(tpp.NumberOfR2yrFemales, 0) + ISNULL(tpp.NumberOfR2yrMales, 0) + ISNULL(tpp.NumberOfR1yrFemales, 0) + ISNULL(tpp.NumberOfR1yrMales, 0) + ISNULL(tpp.NumberOfOtherFemales, 0) + ISNULL(tpp.NumberOfOtherMales, 0) AS TotalAnimalsSlaughtered , tpp.SlaughterNotificationComment , stli.Name AS SpeciesName FROM TestPositiveProfile tpp INNER JOIN TestRequest tr ON tr.Id = tpp.TestRequestId INNER JOIN Herd h ON h.Id = tr.HerdId INNER JOIN HerdTypeLookupItem htli ON htli.Id = h.HerdTypeLookupItemId INNER JOIN SpeciesTypeLookupItem stli ON stli.Id = htli.SpeciesTypeLookupItemId INNER JOIN Person p ON p.Id = tpp.HerdContactPersonId INNER JOIN Address a ON a.ParentObjectId = p.Id AND a.ParentObjectTypeInd = 'P' WHERE (a.DeletedDate IS NULL) AND (a.AddressTypeLookupItemId = 1)--------------------
Colt 45 - the original point and click interface
-
wonderful....thanks so much....didn't know about the Cast function. Cheers so much now Im on a roll with all my other letters. Very much appreciated.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply