Incorrect syntax near the keyword 'AS'

Question

Post reply

Incorrect syntax near the keyword 'AS'

basmster

SSC Enthusiast

Points: 173

May 15, 2021 at 6:08 am

#3883722

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;

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

frederico_fonseca SSCoach Points: 16154 More actions · Answer 1

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;

basmster SSC Enthusiast Points: 173 More actions · Answer 2

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!