query limitations in cursors

From: Kevin Skaalrud <kevin_skaalrud_at_neptune.serca.com>
Date: Thu, 30 Jul 1998 11:41:58 -0700
Message-ID: <35C0BE76.391A34AB@neptune.serca.com>

I have a query (year to year - again, thanks Thomas) that functions correctly on its own, but when attached to a cursor, I get compilation errors. Here is the query:

---

select custno, cm.cm_desc description, cm.rep rep, to_char(sum(sell98)) sales98,

 to_char(sum(qty98)) qty98, to_char(sum(margin98)) margin98,
 to_char(sum(stops98)) drops98, to_char(sum(sell99)) sales99,
 to_char(sum(qty99)) qty99, to_char(sum(margin99)) margin99,

to_char(sum(stops99)) drops99,
 to_char(sum(sell99-sell98)) diffsales, to_char(sum(qty99-qty98)) diffqty, to_char(sum(stops99-stops98)) diffdrops from
 (
 select cm_id custno, sell sell98, qty qty98, margin margin98, stops stops98, 1 sell99, 1 qty99, 1 margin99, 1 stops99  from sacmper sacm
 where (sacm.period='1998-04')
 union all
 select cm_id custno, 1, 1, 1, 1, sell, qty, margin, stops  from sacmper sacm
 where (sacm.period='1999-04')
 ), cms cm
where (custno=cm.id) and (cm.rep='100') group by custno, cm.cm_desc, cm.rep;

---

Now, in a stored procedure when I use 'cursor c1 is ...' the compiler chunks on the '(' after the from statement. Anyone have any thoughts.

Kevin. Received on Thu Jul 30 1998 - 13:41:58 CDT