Informix stored procedure
Sorry Informix newbie !!!
I need to create a stored procedure, but keep getting a syntax error.
Error message :
Error: A syntax error has occurred. (State:37000, Native Code: FFFFFF37)
The code is :
create procedure test()
DECLARE lv_tk CHAR(15),
ln_tk_count INT
declare get_timekeeper CURSOR FOR
SELECT tkinit
FROM timekeep
WHERE tktmdate IS NULL
foreach get_timekeeper INTO lv_tk
SELECT COUNT(*)
INTO lv_tk_count
FROM bo_calendar boc
WHERE NOT EXISTS ( SELECT 1
FROM timecard tc1
WHERE tc1.ttk = lv_tk
AND tc1.tworkdt = boc.date)
AND boc.date <= today
AND boc.day_name NOT IN ('Sunday','Saturday')
AND boc.bank_holiday IS NULL
AND boc.date > '01-05-'||YEAR(TODAY)
INSERT INTO BO_missing_timesheets
VALUES (tk.tkinit,ln_tk_count)
let lv_tk_count = 0
let lv_tk = NULL
END foreach
END PROCEDURE
Can anybody spot what I have done wrong?
I thought there should be execute commands in it, but when I add then - I
get lots of the same syntax error message.
Thanks.
Try to replace DECLARE with DEFINE
jimbomed wrote:
> Sorry Informix newbie !!!
>
> I need to create a stored procedure, but keep getting a syntax error.
>
> Error message :
> Error: A syntax error has occurred. (State:37000, Native Code: FFFFFF37)
>
> The code is :
> create procedure test()>
> DECLARE lv_tk CHAR(15),
> ln_tk_count INT
>
> declare get_timekeeper CURSOR FOR
> SELECT tkinit
> FROM timekeep
> WHERE tktmdate IS NULL>
> foreach get_timekeeper INTO lv_tk
> SELECT COUNT(*)
> INTO lv_tk_count
> FROM bo_calendar boc
> WHERE NOT EXISTS ( SELECT 1
> FROM timecard tc1
> WHERE tc1.ttk = lv_tk
> AND tc1.tworkdt = boc.date)
> AND boc.date <= today
> AND boc.day_name NOT IN ('Sunday','Saturday')
> AND boc.bank_holiday IS NULL
> AND boc.date > '01-05-'||YEAR(TODAY)>
> INSERT INTO BO_missing_timesheets
> VALUES (tk.tkinit,ln_tk_count)>
> let lv_tk_count = 0
> let lv_tk = NULL
> END foreach
> END PROCEDURE
>
> Can anybody spot what I have done wrong?
> I thought there should be execute commands in it, but when I add then - I
> get lots of the same syntax error message.
>
> Thanks.
>
>
jimbomed napisaďż˝(a):
> Sorry Informix newbie !!!
>
> I need to create a stored procedure, but keep getting a syntax error.
>
> Error message :
> Error: A syntax error has occurred. (State:37000, Native Code: FFFFFF37)
>
> The code is :
> (...)
> Can anybody spot what I have done wrong?
> I thought there should be execute commands in it, but when I add then - I
> get lots of the same syntax error message.
>
> Thanks.
>
>
Have you spent any time reading docs? Below there is your procedure, but
you have to define tk record.
create procedure test()
define lv_tk CHAR(15);
define lv_tk_count INT;
foreach get_timekeeper for
SELECT tkinit
INTO lv_tk
FROM timekeep
WHERE tktmdate IS NULL
SELECT COUNT(*)
INTO lv_tk_count
FROM bo_calendar boc
WHERE NOT EXISTS ( SELECT 1
FROM timecard tc1
WHERE tc1.ttk = lv_tk
AND tc1.tworkdt = boc.date)
AND boc.date <= today
AND boc.day_name NOT IN ('Sunday','Saturday')
AND boc.bank_holiday IS NULL
AND boc.date > '01-05-'||YEAR(TODAY);
INSERT INTO BO_missing_timesheets
VALUES (tk.tkinit,lv_tk_count);
let lv_tk_count = 0;
let lv_tk = NULL;
END foreach;
END PROCEDURE;
Why do you select "1" in your exists clause. Informix ignores the
SELECT part when doing the EXISTS. So "*" is just as good in this case
and is the correct idiom for SQL EXISTS clauses.
"into lv_tk_count" looks like it should be ln_tk_count
You should be able to do this "procedure" with 1 SQL. You just do a
query joining timekeep with timecard in the exists clause which is
essentially what you are doing with the "tc1.ttk = lv_tk", group by
tkinit and insert the resulting rows into bo_missing_timesheets.
The following should do the trick or something similar if I left out an
important detail because I don' t care enough to waste more than 15
minutes ;-).
insert into bo_missing_timesheetsselect
tk.tkinit,
count(*)
from
bo_calendar boc,
timekeep tk
where
not exists(
select * from timecard tc1
where tc1.ttk = tk.tkinit
and tc1.tworkdt = boc.date)
and boc.date <= today
AND boc.day_name NOT IN ('Sunday','Saturday')
AND boc.bank_holiday IS NULL
AND boc.date > '01-05-'||YEAR(TODAY)
and tk.tktmdate is null
group bytk.tkinit
Plus you have the errors that claus mentioned. The informix/IBM website
has good online documentation.