Error with syntax using ;With

Question

Post reply

Error with syntax using ;With

GrassHopper

SSCarpal Tunnel

Points: 4465
More actions
February 26, 2016 at 7:35 am

#325073

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

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

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

Luis Cazares SSC Guru Points: 183698 More actions · Answer 1

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 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

GrassHopper SSCarpal Tunnel Points: 4465 More actions · Answer 3

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

)