asked on

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)

petr_hlucin

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

knightEknight

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)

anillucky31

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

anillucky31

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

melli111

ASKER

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

anillucky31

can you share your full query so that we can see the loop?

melli111

ASKER

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.

melli111

ASKER

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)

melli111

ASKER

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

ASKER CERTIFIED SOLUTION

anillucky31

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

melli111

ASKER

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

anillucky31

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

melli111

ASKER

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.