Thread: No Return??

No Return??

From
Bob Pawley
Date:
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


Re: No Return??

From
Raymond O'Donnell
Date:
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
------------------------------------------------------------------

Re: No Return??

From
"Harald Armin Massa"
Date:
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!

Re: No Return??

From
Adrian Klaver
Date:
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

Re: No Return??

From
Sam Mason
Date:
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

Re: No Return??

From
Ted Byers
Date:
--- 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