Thread: No Return??
I'm getting a little frustrated with this problem. Can anyone tell me what is wrong with the following code. I have tested the portions separately and they all work. When I try it as a whole I get the message "control reached end of trigger procedure without RETURN." Any help greatly appreciated. Bob Declare pumpnumber integer; Begin Select count(*) Into pumpnumber From p_id.devices, p_id.processes Where device_number = '11' and p_id.devices.fluid_id = p_id.processes.fluid_id and p_id.processes.ip_op_equipment = 'op'; If pumpnumber = 1 then Update p_id.devices Set number = '#1' From p_id.processes Where p_id.devices.number is null and p_id.devices.device_number = '11' and p_id.devices.fluid_id = p_id.processes.fluid_id and p_id.processes.ip_op_equipment = 'op' ; Else If pumpnumber = 2 Then Update p_id.devices Set number = '#2' From p_id.processes Where p_id.devices.number is null and p_id.devices.device_number = '11' and p_id.devices.fluid_id = p_id.processes.fluid_id and p_id.processes.ip_op_equipment = 'op' ; End If; RETURN NULL; End If; END; I have tried 'Return New' and 'Return Result' without luck, and if I leave off either of the two 'End If ' statements the procedure returns an error. B
On 13/04/2008 21:07, Bob Pawley wrote: > When I try it as a whole I get the message "control reached end of > trigger procedure without RETURN." Hi Bob, If the "IF" branch of the outer IF is executed, then the flow of execution won't hit a RETURN anywhere; I reckon this is what's causing the error. HTH, Ray. ------------------------------------------------------------------ Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland rod@iol.ie Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals ------------------------------------------------------------------
Bob, if pumpnumber not in (1,2) that function does not return anything. > End If; ----> at this end if it ends so you have to return sth. here > END; Harald -- GHUM Harald Massa persuadere et programmare Harald Armin Massa Spielberger Straße 49 70435 Stuttgart 0173/9409607 fx 01212-5-13695179 - EuroPython 2008 will take place in Vilnius, Lithuania - Stay tuned!
On Sunday 13 April 2008 1:07 pm, Bob Pawley wrote: > I'm getting a little frustrated with this problem. > > Can anyone tell me what is wrong with the following code. > > I have tested the portions separately and they all work. > > When I try it as a whole I get the message "control reached end of trigger > procedure without RETURN." > > Any help greatly appreciated. > > Bob > > Declare > pumpnumber integer; > > Begin > > Select count(*) Into pumpnumber From p_id.devices, p_id.processes > Where device_number = '11' > and p_id.devices.fluid_id = p_id.processes.fluid_id > and p_id.processes.ip_op_equipment = 'op'; > > If pumpnumber = 1 then > Update p_id.devices > Set number = '#1' > From p_id.processes > Where p_id.devices.number is null > and p_id.devices.device_number = '11' > and p_id.devices.fluid_id = p_id.processes.fluid_id > and p_id.processes.ip_op_equipment = 'op' ; > > Else If pumpnumber = 2 Then Should be elsif or elseif > Update p_id.devices > Set number = '#2' > From p_id.processes > Where p_id.devices.number is null > and p_id.devices.device_number = '11' > and p_id.devices.fluid_id = p_id.processes.fluid_id > and p_id.processes.ip_op_equipment = 'op' ; > > End If; > RETURN NULL; > End If; Eliminate this End if . > END; > > I have tried 'Return New' and 'Return Result' without luck, and if I leave > off either of the two 'End If ' statements the procedure returns an error. > > B -- Adrian Klaver aklaver@comcast.net
On Sun, Apr 13, 2008 at 01:07:26PM -0700, Bob Pawley wrote: > When I try it as a whole I get the message "control reached end of trigger > procedure without RETURN." I've re-indented the code to make it a bit more obvious what's going on in your old version: > DECLARE > pumpnumber integer; > BEGIN > SELECT count(*) INTO pumpnumber [...] > IF pumpnumber = 1 THEN > UPDATE p_id.devices [...] > ELSE > IF pumpnumber = 2 THEN > UPDATE p_id.devices [...] > END IF; > RETURN NULL; > END IF; > END; > > I have tried 'Return New' and 'Return Result' without luck, and if I leave > off either of the two 'End If ' statements the procedure returns an error. I think you're looking for either "ELSIF" or "ELSEIF", you've got white space between the ELSE and the IF which is introducing a new sub-expression. Either that, or move the RETURN after the final END IF. Sam
--- Bob Pawley <rjpawley@shaw.ca> wrote: > I'm getting a little frustrated with this problem. > > Can anyone tell me what is wrong with the following > code. > > I have tested the portions separately and they all > work. > > When I try it as a whole I get the message "control > reached end of trigger > procedure without RETURN." > > Any help greatly appreciated. > > Bob > > Declare > pumpnumber integer; > > Begin > > Select count(*) Into pumpnumber From p_id.devices, > p_id.processes > Where device_number = '11' > and p_id.devices.fluid_id = p_id.processes.fluid_id > and p_id.processes.ip_op_equipment = 'op'; > > If pumpnumber = 1 then > Update p_id.devices > Set number = '#1' > From p_id.processes > Where p_id.devices.number is null > and p_id.devices.device_number = '11' > and p_id.devices.fluid_id = p_id.processes.fluid_id > and p_id.processes.ip_op_equipment = 'op' ; > > Else If pumpnumber = 2 Then > Update p_id.devices > Set number = '#2' > From p_id.processes > Where p_id.devices.number is null > and p_id.devices.device_number = '11' > and p_id.devices.fluid_id = p_id.processes.fluid_id > and p_id.processes.ip_op_equipment = 'op' ; > > End If; > RETURN NULL; > End If; > END; > > I have tried 'Return New' and 'Return Result' > without luck, and if I leave > off either of the two 'End If ' statements the > procedure returns an error. > Look at your flow control! Your return is within a conditional block. If the condition for your first returns false, flow goes to the very end of the function and reaches "end" without encountering a return statement. Cheers, Ted