Error with syntax using ;With

  • What is wrong with my syntax?

    IF ( (@Client = 'Winco Foods'

    AND EXISTS ( ;

    WITH XMLNAMESPACES('http://www.w3.org/2001/XMLSchema-instance' AS ns)

    SELECT AuditNbr ,

    VndNbr ,

    VndName ,

    CompanyCode,

    AuditorID ,

    ClmAmt ,

    PRGCode ,

    [Year] ,

    ClmNbr

    FROM ClaimSummary_Temp

    WHERE (SELECT AuditNbr ,

    VndNbr ,

    VndName ,

    CompanyCode ,

    AuditorID ,

    ClmAmt ,

    PRGCode ,

    [Year] ,

    ClmNbr FOR xml path('row'),

    elements xsinil ,

    type

    )

    .value('count(//*[local-name() != "colToIgnore"]/@ns:nil)', 'int') > 0 ) )

    BEGIN

    -- NULL values exists in the required fields. Delete the records in the temp table.

    IF @Client = 'Winco Foods'

    TRUNCATE TABLE ClaimSummary_Winco

    IF @Client = 'Superstore Industries'

    TRUNCATE TABLE ClaimSummary_SSI

    IF @Client = 'Stater Bros'

    TRUNCATE TABLE ClaimSummary_StaterBros

    INSERT INTO ImportStatus

    (

    TableName ,

    Client ,

    AuditYear ,

    AuditNumber,

    ImpStatus ,

    ImpDesc

    )

    VALUES

    (

    @TableName ,

    @Client ,

    @AuditYear ,

    @AuditNumber,

    'Fail' ,

    'NULL Values exists in the Required Fields'

    )

    END

  • Semicolons are statement terminators not initiators.

    I'm not sure if you can use a WITH inside an IF condition.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • There seem to be numerous syntax errors with this part:

    IF ( (

    @Client = 'Winco Foods'

    AND EXISTS ( ;

    WITH XMLNAMESPACES('http://www.w3.org/2001/XMLSchema-instance' AS ns)

    SELECT

    AuditNbr, VndNbr, VndName, CompanyCode, AuditorID, ClmAmt, PRGCode, [Year], ClmNbr

    FROM ClaimSummary_Temp

    WHERE (SELECT AuditNbr, VndNbr, VndName, CompanyCode, AuditorID, ClmAmt, PRGCode, [Year], ClmNbr

    FOR xml path('row'), elements xsinil, type).value('count(//*[local-name() != "colToIgnore"]/@ns:nil)', 'int') > 0

    ) )

    BEGIN

    Two right brackets are missing and the construct is a mix of an existence check and a check for something > 0. Try deconstructing the statement and reassembling it. I think the xmlnamespaces clause should come before the IF.

    Can't you use a more conventional WHERE clause?

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • I do have another way which is working for me (below). I just wanted to try this other way to see if works more efficient.

    IF (

    (@Client = 'Winco Foods'

    AND EXISTS

    ( SELECT 1

    FROM ClaimSummary_Winco CSW

    WHERE CSW.AuditNbr is null

    OR CSW.VndNbr is null

    OR CSW.VndName is null

    OR CSW.CompanyCode is null

    OR CSW.AuditorID is null

    OR CSW.ClmAmt is null

    OR CSW.PRGCode is null

    OR CSW.[Year] is null

    OR CSW.ClmNbr is null

    OR CSW.CompanyCode is null

    )

    )

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

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