Hi!

I usually need to do some queries where I need to
compare 2, 3, 4 periods of
large amount of data.
I put an example below, where I need to show all
products codes and the
sales of two periods.
(I need to do LEFT OUTER JOIN because not all products
have sales in both
periods)
The table items have about 5 millions of records,
that's why I looking for a
fast method to do the query.

By now I'm doing something like this inside a SELECT
PROCEDURE:

insert into temp1 (code,sales)
select i.code, sum(i.amount)
from header h
left outer join items i on i.billnumber=h.billnumber
where h.date between dateA1 and dateA2
group by code;

insert into temp2 (code,sales)
select i.code, sum(i.amount)
from header h
left outer join items i on i.billnumber=h.billnumber
where h.date between dateB1 and dateB2
group by code;

for
select p.code, s1.sales, s2.sales
from products p
left outer join temp1 s1 on s1.code=p.code
left outer join temp2 s2 on s2.code=p.code
into :code, :salesA, :salesB
do
suspend;

delete from temp1;
delete from temp2;

------------------------------------------------------------

I would like to create a SP that returns the codes and
sales of a period,
and then, join 2, 3, 4 of this SP in a query, for
example:

CREATE PROCEDURE SP_TEMPDATA (date1 date, date2 date)
returns (code integer,
sales float) AS
begin
for
select i.code, sum(i.amount)
from header h
left outer join items i on i.billnumber=h.billnumber
where h.date between date1 and date2
group by code
into :code, :sales
do suspend;
end

and then a query:

select p.code, s1.sales, s2.sales
from products p
left outer join SP_TEMPDATA(dateA1,dateA2) s1 on
s1.code=p.code
left outer join SP_TEMPDATA(dateB1,dateB2) s2 on
s2.code=p.code

but the problem with this query is that SP_TEMPDATA is
executed for each row
in PRODUCTS (why?, I don't know).
(If you do a INNER JOIN with the example above, it
works, but this doesn't
return what I looking for)

------------------------------------------------------------------

Does anybody knows how to simplify this query, or to
avoid the use of
temporary tables?
any idea???

With best regards
Christian




__________________________________________________
Do you Yahoo!?
HotJobs - Search new jobs daily now
http://hotjobs.yahoo.com/