SQL "IF" Bug?
Jody Wood asked 5 years ago
I noticed what I would consider a bug yesterday that I worked-around with a SQL Stored procedure. I had this SQL script set to run in Map Pre Tasks.
IF (SELECT COUNT(*) FROM PM_UPLOAD_VIEW WHERE ISNULL(APPROVAL, ”)>”)>0
BEGIN
UPDATE <COMPANY DB>..GL00100
SET ACCTENTR = 1
WHERE ACTINDX = 35
END
The problem is that the IF statement was being ignored. The map would run and the ACCTENTR would be toggled, even when no data exists. Admittedly, I need to do some thinking about how to stop the map in the event no data exists (should be easy with SQL Validate), but it seems bothersome that the IF statement was ignored.
The Workaround: I created a SQL Stored Procedure passing the ACCTENTR and ACTINDX values as variables and the map runs fine, and the IF statement is honored.
Just thought I’d share in case I’m missing something or if others experience the same hiccup.
IF (SELECT COUNT(*) FROM PM_UPLOAD_VIEW WHERE ISNULL(APPROVAL, ”)>”)>0
BEGIN
UPDATE <COMPANY DB>..GL00100
SET ACCTENTR = 1
WHERE ACTINDX = 35
END
The problem is that the IF statement was being ignored. The map would run and the ACCTENTR would be toggled, even when no data exists. Admittedly, I need to do some thinking about how to stop the map in the event no data exists (should be easy with SQL Validate), but it seems bothersome that the IF statement was ignored.
The Workaround: I created a SQL Stored Procedure passing the ACCTENTR and ACTINDX values as variables and the map runs fine, and the IF statement is honored.
Just thought I’d share in case I’m missing something or if others experience the same hiccup.
Answers
Lorren Zemke Staff answered 5 years ago
Jody, What version of SmartConnect are you using? I put the statement above into a SQL Command on the Pre-Map task list and it worked as expected. I used our latest SmartConnect release 20.17.0.17 and a table call PM_UPLOAD_VIEW.
Jody replied 5 years ago Lorren, Thank you for your reply. We are also using 20.17.0.17, but I think something just got sideways in our server because after a reboot, it started working again. You can delete this thread if you want as it is not really useful to the community since the problem was a server issue on our end and not a SmartConnect issue.