We have an informix database supplied by the vendor. I attempted to create a simple stored procedure (see code below). Unfortunately I only got an syntax error. We do not know if the user supplied by the vendor has sufficient rights to create a stored procedure. I am using TOAD to execute the code. Any suggestions?
CREATE PROCEDURE SP_TUL_TEST_Query
FOREACH
select csqname
, loggedinagents
, availableagents
, unavailableagents
, callswaiting
, case when oldestcontact = 0 then 0
else ROUND(oldestcontact / 1000,0)
end as oldestcontact
, totalcalls
, callshandled
, callsabandoned
, callsdequeued
, case when avgtalkduration = 0 then 0
else Round(avgtalkduration / 1000,0)
end as avgtalkduration
, case when avgwaitduration = 0 then 0
else ROUND(avgwaitduration / 1000,0)
end as avgwaitduration
, case when longesttalkduration = 0 then 0
else ROUND(longesttalkduration / 1000,0)
end as longesttalkduration
, case when longestwaitduration = 0 then 0
else ROUND(longestwaitduration / 1000,0)
end as longestwaitduration
, enddatetime
, workingagents
, talkingagents
, reservedagents
, startdatetime
from RtCSQsSummary;
END FOREACH
END PROCEDURE;
CREATE PROCEDURE SP_TUL_TEST_Query
FOREACH
select csqname
, loggedinagents
, availableagents
, unavailableagents
, callswaiting
, case when oldestcontact = 0 then 0
else ROUND(oldestcontact / 1000,0)
end as oldestcontact
, totalcalls
, callshandled
, callsabandoned
, callsdequeued
, case when avgtalkduration = 0 then 0
else Round(avgtalkduration / 1000,0)
end as avgtalkduration
, case when avgwaitduration = 0 then 0
else ROUND(avgwaitduration / 1000,0)
end as avgwaitduration
, case when longesttalkduration = 0 then 0
else ROUND(longesttalkduration / 1000,0)
end as longesttalkduration
, case when longestwaitduration = 0 then 0
else ROUND(longestwaitduration / 1000,0)
end as longestwaitduration
, enddatetime
, workingagents
, talkingagents
, reservedagents
, startdatetime
from RtCSQsSummary;
END FOREACH
END PROCEDURE;