I am trying to run the following SQL code in Aqua Data Studio for an Informix Database:
DEFINE testVal integer
FOREACH WITH HOLD
DELETE FROM data_values
WHERE data_Val = testVal
I would also like to display the value of 'testVal' every iteration (or maybe every Nth iteration).
For the life of me I can’t get it to work. I’ve looked on your web site and on-line but I have found nothing helpful.
Any help would be greatly appreciated.
You probably want to do this with a stored procedure. Informix allows you to trace statements but will probably give you more information then you want. I would suggest creating another table and adding information to that table about the records that you are deleting. You can even set an interval so that every x row will get inserted. Here is a store procedure that I put together as an example. Notice the interval passed to the store procedure is 5. Also, I commented out the trace. If you do use the trace, the file will be located on the server and not locally.
Hope that helps,
CREATE PROCEDURE test_stored_proc(action_interval integer)DEFINE testVal float;DEFINE increment integer;DEFINE action integer;LET increment = 1;LET action = action_interval;-- SET DEBUG FILE TO 'c:/users/tom/atrace.txt';-- TRACE ON;-- TRACE 'Start'; FOREACH SELECT total_due INTO testVal FROM informix.bistudio_example IF (MOD(increment, action) = 0) THEN INSERT INTO informix.test_sp_output(c1) VALUES(testVal); LET increment = 1; ELSE LET increment = increment + 1; END IF END FOREACH-- TRACE OFF;END PROCEDUREGOEXECUTE PROCEDURE test_stored_proc(5)GOSELECT * FROM informix.test_sp_output
I appreciate the response, but a stored procedure seemed like overkill. While the example I provided was stripped down, I just need to run it once. Is there no way to run SQL code like this on ADS for an Informix DB?