Incorrect syntax near the keyword 'AS'

  • 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;
  • 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;
  • 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 2 (of 2 total)

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