Hello,
I am trying to use a stored procedure to obtain data from a table
depending on the month. I have a table for each month, so I check
for the month and then use UNION to summarize the sales by hour. If
I test my SQL query, I get the correct data, e.g.
STEVE JONES 2003-11-10 p1 3
STEVE JONES 2003-11-10 p2 1
STEVE JONES 2003-11-10 p3 3

but if I use the stored procedure, then the last record is
duplicated, e.g.
STEVE JONES 2003-11-10 p1 3
STEVE JONES 2003-11-10 p2 1
STEVE JONES 2003-11-10 p3 3
STEVE JONES 2003-11-10 p3 3

Which would be the right way to structure the stored procedure to
avoid the duplication issue.
Thanks.
Horacio C.

SQL query:

select userfname, userlname, sdate, 'a12', count(*) from jour11
where cast(stime as time) >= '00:00' and cast(stime as time)
< '01:00'
and stype not in (6, 8) and sdate = '11/10/2003' group by userfname,
userlname, sdate
UNION
select userfname, userlname, sdate, 'a1 ', count(*) from jour11
where cast(stime as time) >= '01:00' and cast(stime as time)
< '02:00'
and stype not in (6, 8) and sdate = '11/10/2003' group by userfname,
userlname, sdate
UNION
...
select userfname, userlname, sdate, 'p11', count(*) from jour11
where cast(stime as time) >= '23:00' and stype not in (6, 8)
and sdate = '11/10/2003' group by userfname, userlname, sdate

Stored procedure:

if (EXTRACT(MONTH FROM SALEDATE) = 11) then
BEGIN
FOR
select userfname, userlname, sdate, 'a12', count(*) from jour11
where cast(stime as time) >= '00:00' and cast(stime as time)
< '01:00'
and stype not in (6, 8) and sdate = :SALEDATE group by
userfname, userlname, sdate
UNION
select userfname, userlname, sdate, 'a1 ', count(*) from jour11
where cast(stime as time) >= '01:00' and cast(stime as time)
< '02:00'
and stype not in (6, 8) and sdate = :SALEDATE group by
userfname, userlname, sdate
UNION
...
select userfname, userlname, sdate, 'p11', count(*) from jour11
where cast(stime as time) >= '23:00' and stype not in (6, 8)
and sdate = :SALEDATE group by userfname, userlname, sdate
INTO
:USERFNAME, :USERLNAME, :RSALEDATE, :PERIOD, :NSALES
DO SUSPEND;
END