May 15, 2021 at 6:08 am
Hi Everyone.
I'm hoping someone can take a look at my code here to see what I am missing. I'm trying to create a sub-query and name its alias, and then include that alias/column in the outer select query. But when I do that I get the error message Msg 156, Level 15, State 1, Line 7
Incorrect syntax near the keyword 'AS'. If you could take a look that would be awesome. Thanks.
SELECT PK_CertificationData, CertificationDecisionValidFrom AS StatusDate, ValidUntil, CertificationResultText, IsDataLevel, FirstCertificationDate, P1.PK_Person, P1.LastName AS ClientName, P1.Nr1 AS ClientID, PK_ProductToPerson, CertificateGroup,
ProductCategory,ProductID, TradeName, Brand, Scope, InspectionService.Name, ActivatedOn,Attribute2 AS DoNotPublish, Attribute3 AS HighRisk, Free1 AS MonitoredRisk,
Free2 AS SingleIng, P2.LastName AS TechnicalAdministrator
(SELECT Product.Name
FROM Product.AdditionalCategoryToProductToPerson INNER JOIN Product.Product ON PK_Product=FK_Product
INNER JOIN Product.ProductToPerson ON PK_ProductToPerson=FK_ProductToPerson
WHERE ProductToPerson.isDeleted=0 AND AdditionalCategoryToProductToPerson.IsDeleted=0 AND ReasonForInactiveText='active') AS AdditionalCategory
FROM Product.ProductToPerson INNER JOIN Base.Person P1 ON P1.PK_Person=Product.ProductToPerson.FK_Person
INNER JOIN Product.ServiceToProductToPerson ON Product.ServiceToProductToPerson.FK_ProductToPerson=Product.ProductToPerson.PK_ProductToPerson
INNER JOIN Base.InspectionService ON ServiceToProductToPerson.FK_InspectionService=InspectionService.PK_InspectionService
INNER JOIN Product.BrandToProductToPerson ON BrandToProductToPerson.FK_ProductToPerson=ProductToPerson.PK_ProductToPerson
INNER JOIN Certification.CertificationData C1 ON ProductToPerson.PK_ProductToPerson=C1.FK_ProductToPerson
LEFT JOIN Base.Person P2 ON P2.PK_Person=C1.FK_Person_CertificationBody
WHERE ReasonforInactiveText='active' AND InspectionService.Name='NON-GMO Project' AND IsDataLevel=1 AND C1.IsDeleted=0 AND CertificationResultText<>'Organic' AND BrandToProductToPerson.IsDeleted=0 AND P1.IsDeleted=0;
May 15, 2021 at 7:15 am
I would start by giving all tables a short alias (some tables do not have any) and use it in all respective columns so it is clear to anyone which table a column is from.
you are also missing a comma after "P2.lastname as technicaladministrator" - formatting the code helps identify these issues.
regarding the subquery - as alias are missing we don't know if that subquery is related to the main query or how.
SELECT PK_CertificationData
, CertificationDecisionValidFrom AS StatusDate
, ValidUntil
, CertificationResultText
, IsDataLevel
, FirstCertificationDate
, P1.PK_Person
, P1.LastName AS ClientName
, P1.Nr1 AS ClientID
, PK_ProductToPerson
, CertificateGroup
, ProductCategory
, ProductID
, TradeName
, Brand
, Scope
, InspectionService.Name
, ActivatedOn
, Attribute2 AS DoNotPublish
, Attribute3 AS HighRisk
, Free1 AS MonitoredRisk
, Free2 AS SingleIng
, P2.LastName AS TechnicalAdministrator
(SELECT Product.Name
FROM Product.AdditionalCategoryToProductToPerson
INNER JOIN Product.Product
ON PK_Product = FK_Product
INNER JOIN Product.ProductToPerson
ON PK_ProductToPerson = FK_ProductToPerson
WHERE ProductToPerson.isDeleted = 0
AND AdditionalCategoryToProductToPerson.IsDeleted = 0
AND ReasonForInactiveText = 'active') AS AdditionalCategory
FROM Product.ProductToPerson
INNER JOIN Base.Person P1
ON P1.PK_Person = Product.ProductToPerson.FK_Person
INNER JOIN Product.ServiceToProductToPerson
ON Product.ServiceToProductToPerson.FK_ProductToPerson = Product.ProductToPerson.PK_ProductToPerson
INNER JOIN Base.InspectionService
ON ServiceToProductToPerson.FK_InspectionService = InspectionService.PK_InspectionService
INNER JOIN Product.BrandToProductToPerson
ON BrandToProductToPerson.FK_ProductToPerson = ProductToPerson.PK_ProductToPerson
INNER JOIN Certification.CertificationData C1
ON ProductToPerson.PK_ProductToPerson = C1.FK_ProductToPerson
LEFT JOIN Base.Person P2
ON P2.PK_Person = C1.FK_Person_CertificationBody
WHERE ReasonforInactiveText = 'active'
AND InspectionService.Name = 'NON-GMO Project'
AND IsDataLevel = 1
AND C1.IsDeleted = 0
AND CertificationResultText<>'Organic'
AND BrandToProductToPerson.IsDeleted = 0
AND P1.IsDeleted = 0;
May 17, 2021 at 6:14 pm
Hi Frederico_fons.
Ok, so you're saying I should add aliases to all the tables and a comma where you mentioned...and then reupload the code to take a look...
Will start working on that...may take me a day or two.
Thanks for your help!
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy