Hello again,

I still have the same problems with my cursor.

For my understanding, is this a proper solution?

First I fill the cursor by selecting (FOR select ... INTO ... DO begin
... end) values into the cursor.
Next I give ids (still in first "DO begin ... end") into 3 different
cursors (one after another), which insert/update the same table.

It looks like this:

begin // selecting all affected customers
for
select ID
from tab2
where ...
into :V_ID
DO
begin // do every step for each customer
begin // inserting primary row
insert into tab1
...
values
...
end

begin // updating primary row
for
select ...
from tab3
where ID = :V_ID
into ...
do
begin
update tab1
set ...
where ID = :V_ID;
end
end

begin // updating primary row again
for
select ...
from tab4
where ID = :V_ID
into ...
DO
begin
update tab1
set ...
where ID = :V_ID;
end
end

begin // inserting secondary row (calculated values from
primary rows) in tab1
for
select ...
from tab1
where ID = :V_ID
into ...
do
begin
insert into tab1
...
values
...;
end
end
end
end

If I specify the customer in the first select, this proc works fine. As
soon as I want to select any customer nothing happens.

May I need a SUSPEND somewhere?

Thanks again and alot!

Nicolas

>"select all values" means removing "and crit1 = '1002'".
>
>If I run this select on the database I get exactly the values I need.
>
>Thanks!
>
>Nicolas
>
>How
>
>Helen Borrie schrieb:
>
>
>
>>At 01:06 PM 7/03/2005 +0100, you wrote:
>>
>>
>>
>>
>>
>>>Hi,
>>>
>>>IÂ?ve got problems using a cursor.
>>>
>>>Syntax is like:
>>>
>>>FOR select a.field1, a.field2, b.field3
>>>
>>>
>>>
>>>
>>>from tab1 a, tab2 b
>>
>>
>>
>>
>>>where a.id = b.id
>>>and crit1 = '1002'
>>>and ...
>>>group by a.field1, a.field2, b.field3
>>>into :v_field1, v:field2, :v_field3 DO
>>>
>>>begin
>>>insert into new_tab
>>>(field1, field2, field3)
>>>values
>>>(:v_field1, v:field2, :v_field3);
>>>end
>>>end
>>>
>>>As long as crit1 is given thereÂ?s no problem. As soon as I select all
>>>values this proc doesnÂ?t work anymore and nothing is written into new_tab.
>>>
>>>Any idea??
>>>
>>>
>>>
>>>
>>What do you mean by "all values"? Do you actually mean "all rows", i.e. you
>>remove all criteria?
>>
>>Advice: use the explicit join syntax. It is a lot easier to troubleshoot
>>-- and also a bit more likely that you will remember to use fully qualified
>>names for EVERYTHING in join sentences:
>>
>>FOR select a.field1, a.field2, b.field3
>> from tab1 a
>> join tab2 b
>> ON a.id = b.id
>>/*
>> WHERE a.crit1 = '1002'
>> and ...<more qualified criteria>
>>*/
>>group by a.field1, a.field2, b.field3
>>into :v_field1, v:field2, :v_field3 DO
>> begin
>> insert into new_tab
>> (field1, field2, field3)
>> values
>> (:v_field1, v:field2, :v_field3);
>> end
>>end
>>
>>./hb
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>>Thanks in advance!
>>>
>>>Nicolas
>>>
>>>
>>>
>>>
>>>Yahoo! Groups Links
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>
>>
>>
>>Yahoo! Groups Links
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>
>
>[Non-text portions of this message have been removed]
>
>
>
>
>Yahoo! Groups Links
>
>
>
>
>
>
>
>
>
>