• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 350
  • Last Modified:

SQL query incorrect syntax

I have the following Query I have been staring at for a long time and can't figure out what is wrong with it.  The compiler says the error is near the "ELSE" keyword but I can't see a problem there.

IF EXISTS (SELECT 1 FROM table1 WHERE  Period = @CurrentPeriod AND Acct = @Acct)

UPDATE table1 SET Amount = Amount + @TotalAmount, TotalCostInput = Amount + @TotalAmount

WHERE (DO LIKE @ProjectName + '%') AND (Revision LIKE @Revision + '%') AND (Task LIKE @WBS + '%') AND (Period = @CurrentPeriod) AND (Acct <> 'Direct Labor')

ELSE INSERT INTO table1 (DO, Task, TTitle, Hours, Period, Year)

SELECT @ProjectName, @WBS, @WBSTitle, @Hours, @CurrentPeriod, @ThisYear

FROM table1

WHERE NOT EXISTS(SELECT NULL FROM table1 WHERE Period = @CurrentPeriod  AND Acct = @Acct)
0
melli111
Asked:
melli111
1 Solution
 
petr_hlucinCommented:
Probably you are missing semicolon (;) at the end of the command - see attached code snippet.
IF EXISTS (SELECT 1 FROM table1 WHERE  Period = @CurrentPeriod AND Acct = @Acct) 
UPDATE table1 SET Amount = Amount + @TotalAmount, TotalCostInput = Amount + @TotalAmount 
WHERE (DO LIKE @ProjectName + '%') AND (Revision LIKE @Revision + '%') AND (Task LIKE @WBS + '%') AND (Period = @CurrentPeriod) AND (Acct <> 'Direct Labor');
ELSE 
INSERT INTO table1 (DO, Task, TTitle, Hours, Period, Year)
SELECT @ProjectName, @WBS, @WBSTitle, @Hours, @CurrentPeriod, @ThisYear
FROM table1 
WHERE NOT EXISTS(SELECT NULL FROM table1 WHERE Period = @CurrentPeriod  AND Acct = @Acct);

Open in new window

0
 
knightEknightCommented:
I don't know, it looks good to me, but you might try adding a BEGIN/END:

IF EXISTS (SELECT 1 FROM table1 WHERE  Period = @CurrentPeriod AND Acct = @Acct)
BEGIN
  UPDATE table1 SET Amount = Amount + @TotalAmount, TotalCostInput = Amount + @TotalAmount
  WHERE (DO LIKE @ProjectName + '%') AND (Revision LIKE @Revision + '%') AND (Task LIKE @WBS + '%') AND (Period = @CurrentPeriod) AND (Acct <> 'Direct Labor')
END
ELSE
  INSERT INTO table1 (DO, Task, TTitle, Hours, Period, Year)
0
 
anillucky31Commented:
IF EXISTS (SELECT 1 FROM table1 WHERE  Period = @CurrentPeriod AND Acct = @Acct)
BEGIN
      UPDATE table1 SET Amount = Amount + @TotalAmount, TotalCostInput = Amount + @TotalAmount

      WHERE (DO LIKE @ProjectName + '%') AND (Revision LIKE @Revision + '%') AND (Task LIKE @WBS + '%') AND (Period = @CurrentPeriod) AND (Acct <> 'Direct Labor')

END


ELSE
begin
INSERT INTO table1 (DO, Task, TTitle, Hours, Period, Year)

SELECT @ProjectName, @WBS, @WBSTitle, @Hours, @CurrentPeriod, @ThisYear

FROM table1

WHERE NOT EXISTS(SELECT 1 FROM table1 WHERE Period = @CurrentPeriod  AND Acct = @Acct)

end
0
Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

 
anillucky31Commented:
In actual you need to have Where NOT EXISTS in else statement as you have filtered already
IF EXISTS (SELECT 1 FROM table1 WHERE  Period = @CurrentPeriod AND Acct = @Acct) 
BEGIN
	UPDATE table1 SET Amount = Amount + @TotalAmount, TotalCostInput = Amount + @TotalAmount

	WHERE (DO LIKE @ProjectName + '%') AND (Revision LIKE @Revision + '%') AND (Task LIKE @WBS + '%') AND (Period = @CurrentPeriod) AND (Acct <> 'Direct Labor')

END


ELSE 
begin
INSERT INTO table1 (DO, Task, TTitle, Hours, Period, Year)

VALUES( @ProjectName, @WBS, @WBSTitle, @Hours, @CurrentPeriod, @ThisYear)

 

end

Open in new window

0
 
melli111SharePoint Administrator / DeveloperAuthor Commented:
I discovered that I had the "AND" keyword twice in one spot.  But now, this query runs an infinite loop of writing to teh database.  I don't even see how that is possible
0
 
anillucky31Commented:
can you share your full query so that we can see the loop?
0
 
melli111SharePoint Administrator / DeveloperAuthor Commented:
I just verified by inserting test values into the database that the code calls the Query the correct number of times.  The error is not in the C# code, it is in the query.  I will post the full query.
0
 
melli111SharePoint Administrator / DeveloperAuthor Commented:
IF EXISTS

(SELECT 1 FROM Table1
WHERE FIELD1 LIKE @param1 + '%' AND Field2 LIKE @Field2 + '%' AND Field3 LIKE @param3 + '%' AND Field4 = @CurrentField4 AND Field5 = @Field5)

UPDATE Table1 SET Amount = Amount + @TotalAmount, TotalCostInput = Amount + @TotalAmount, GA =  ((Amount + @TotalAmount) * @GAPct), Subtotal = ((Amount + @TotalAmount) + ((Amount + @TotalAmount) * @GAPct)), Fee = (((Amount + @TotalAmount) + ((Amount + @TotalAmount) * @GAPct)) * @BilledFeePct), TotalCost =  ((((Amount + @TotalAmount) + ((Amount + @TotalAmount) * @GAPct)) * @BilledFeePct) + (((Amount + @TotalAmount) + ((Amount + @TotalAmount) * @GAPct))))

WHERE (FIELD1 LIKE @param1 + '%') AND (Field2 LIKE @Field2 + '%') AND (Field3 LIKE @param3 + '%') AND (Field4 = @CurrentField4) AND (Field5 <> 'Test value')

 ELSE INSERT INTO Table1 (FIELD1, Field3, TTitle, Hours, Field4, Year, Amount, Support, Fringe, Overhead, ODC, Company, Facility, TotalCostInput, GA, Subtotal, Fee, TotalCost, Field5, Field2)
SELECT @param1, @param3, @param3Title, @Hours, @CurrentField4, @ThisYear, @TotalAmount, @Support, @Fringe, @Overhead, @ODC, @Company, @Facility, @TotalCostInput, @GA, @Subtotal, @Fee, @TotalCost, @Field5, @Field2 FROM Table1

WHERE NOT EXISTS(SELECT NULL FROM Table1 WHERE FIELD1 = @param1 AND Field2 = @Field2 AND Field3 = @param3 AND Field4 = @CurrentField4  AND Field5 = @Field5)
0
 
melli111SharePoint Administrator / DeveloperAuthor Commented:
The Query is executing the ELSE INSERT INTO statement with all of the correct values, however it writes the values an infinite number of times
0
 
anillucky31Commented:
use this


IF EXISTS 

(SELECT 1 FROM Table1 
WHERE FIELD1 LIKE @param1 + '%' AND Field2 LIKE @Field2 + '%' AND Field3 LIKE @param3 + '%' AND Field4 = @CurrentField4 AND Field5 = @Field5) 

UPDATE Table1 SET Amount = Amount + @TotalAmount, TotalCostInput = Amount + @TotalAmount, GA =  ((Amount + @TotalAmount) * @GAPct), Subtotal = ((Amount + @TotalAmount) + ((Amount + @TotalAmount) * @GAPct)), Fee = (((Amount + @TotalAmount) + ((Amount + @TotalAmount) * @GAPct)) * @BilledFeePct), TotalCost =  ((((Amount + @TotalAmount) + ((Amount + @TotalAmount) * @GAPct)) * @BilledFeePct) + (((Amount + @TotalAmount) + ((Amount + @TotalAmount) * @GAPct)))) 

WHERE (FIELD1 LIKE @param1 + '%') AND (Field2 LIKE @Field2 + '%') AND (Field3 LIKE @param3 + '%') AND (Field4 = @CurrentField4) AND (Field5 <> 'Test value')

 ELSE INSERT INTO Table1 (FIELD1, Field3, TTitle, Hours, Field4, Year, Amount, Support, Fringe, Overhead, ODC, Company, Facility, TotalCostInput, GA, Subtotal, Fee, TotalCost, Field5, Field2) 
values( @param1, @param3, @param3Title, @Hours, @CurrentField4, @ThisYear, @TotalAmount, @Support, @Fringe, @Overhead, @ODC, @Company, @Facility, @TotalCostInput, @GA, @Subtotal, @Fee, @TotalCost, @Field5, @Field2 )

Open in new window

0
 
melli111SharePoint Administrator / DeveloperAuthor Commented:
Anillucky 31 - so you are saying that if we check to see if the Result set conatins any rows at the beginning of the query, we do not need to check again in the ELSE statement since the ELSE statement triggering tells us that there are no existing rows with the fields already there, so simply perform an INSERT statement
0
 
anillucky31Commented:
yes. because you have already checked that whether rows exists for your input or not. so you need not to check again. just insert the values
0
 
melli111SharePoint Administrator / DeveloperAuthor Commented:
Thank you.  This worked.  I still cannot understand why the INSERT statement was executing an infinite number of times, but it is working correctly now.
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now