Re: SQL Default Question (Resent)

From: JustAnotherDBA <jadba_at_bellsouth.net>
Date: Thu, 27 Feb 2003 20:04:12 -0600
Message-ID: <0cz7a.69963$KZ1.5199_at_fe04.atl2.webusenet.com>


Sorry for the sending so many repeats of my last message.

This is what I was trying to say the other day......

DECLARE
CURSOR num1_csr IS
  SELECT a FROM T1 ;

CURSOR num2_csr IS
  SELECT b FROM T2 ;

NUM1 number ;
NUM2 number ;
Total number ;

BEGIN

  • Get NUM1 OPEN num1_csr ; FETCH num1_csr INTO NUM1 ; If num1_csr%NOTFOUND or NUM1 is Null Then NUM1 := 0 ; End If ; CLOSE num1_csr ;
  • Get NUM2 OPEN num2_csr ; FETCH num2_csr INTO NUM2 ; If num2_csr%NOTFOUND or NUM2 is Null Then NUM2 := 0 ; End If ; CLOSE num2_csr ;
  • Do calculation Total := NUM1 + NUM2 ;

"abc" <abc_at_abc.com.au> wrote in message news:b3hcvh$rrm$1_at_mws-stat-syd.cdn.telstra.com.au...
> Hi All,
>
> I have a PL/SQL to do following calculation:
>
> Total = NUM1 + NUM2;
>
> SELECT a INTO NUM1 FROM T1;
> SELECT b INTO NUM2 FROM T2;
>
> As b has no return row therefore NUM2 is undefined. Total as a result
is
> also undefined (PL/SQL crashed with "No defined value ...")
>
> Is there any way to return b as ZERO if no row returned ???
>
> I have tried
>
> SELECT NVL(
> (SELECT b INTO NUM2 FROM T2), 0) FROM DUAL;
>
> But PL/SQL did not allow me to use SELECT ...(SELECT !
>
> SELECT NVL(NUM2,0) FROM DUAL doesn't work.
>
>
>
>
>
Received on Fri Feb 28 2003 - 03:04:12 CET

Original text of this message