Jim Horn
asked on
Informix - How to return table from procedure
Hi All
(Warning: SQL expert, Informix N00b)
I'm attempting to create a procedure where it returns a table of data, based on my experience with SQL Server doing the same, and could use some help.
From the '-- up to this point the proc' on down, can someone show me the syntax for a SELECT statement to be returned as a set?
Thanks in advance.
Jim
(Warning: SQL expert, Informix N00b)
I'm attempting to create a procedure where it returns a table of data, based on my experience with SQL Server doing the same, and could use some help.
From the '-- up to this point the proc' on down, can someone show me the syntax for a SELECT statement to be returned as a set?
Thanks in advance.
Jim
CREATE PROCEDURE rpt_sales_distribution (iYear int, iMonth int) AS
BEGIN
define iYear integer = 2014;
define iMonth integer = 4;
define iWeekNumber integer = 1;
define dtStart DATETIME YEAR TO DAY;
define dtEnd DATETIME YEAR TO DAY;
if iWeekNumber = 1 then
let dtStart = to_date(to_char(iYear) || "-" || to_char(iMonth) + || "-01");
let dtEnd = to_date(to_char(iYear) || "-" || to_char(iMonth) + || "-07");
elseif iWeekNumber = 2 then
let dtStart = to_date(to_char(iYear) || "-" || to_char(iMonth) + || "-08") ;
let dtEnd = to_date(to_char(iYear) || "-" || to_char(iMonth) + || "-14");
elseif iWeekNumber = 3 then
let dtStart = to_date(to_char(iYear) || "-" || to_char(iMonth) + || "-15") ;
let dtEnd = to_date(to_char(iYear) || "-" || to_char(iMonth) + || "-21");
elseif iWeekNumber = 4 then
let dtStart = to_date(to_char(iYear) || "-" || to_char(iMonth) + || "-22");
let dtEnd = LAST_DAY(to_date(to_char(iYear) || "-" || to_char(iMonth) + || "-07"));
end if;
-- up to this point the proc saves fine, but I'd like to make it return the below set
select 1 as week_id,
11 as src_id,
'11 SY Agt Portal' as src_name
,count(*) as seg
,sum(a.unitrate) as rev
,round(sum(a.unitrate)/count(*)) as avg
from wsord_det a
,wsord_hdr b
,wsord_tkt c
where b.orddate between dtStart_week1 and dtEnd_week1
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I've moved to a different gig and can't continue this issue, so I'm going to close the question and spread the wealth around. Thanks.
ASKER
The below SQL returns a set
Open in new window
When I try to use variables here's the result..