Re: Seems to be perfect.. but fails

From: <prochak_at_my-dejanews.com>
Date: Fri, 28 Aug 1998 20:49:37 GMT
Message-ID: <6s7551$b6c$1@nnrp1.dejanews.com>

First off, please keep your lines below 80 chars. in length. Not all news readers can wrap long lines.

In article <sHzF1.401$Iq1.340560_at_news.mci2000.com>,   "Dwight Crane" <dwight.crane_at_mci.com> wrote:
> Ok.. this might take some explaining.. below is the code...

[snip]

> trying to populate with a correct value...
> but it seems to not like the INTO
> ... I am plumb out of IDEAS..
>
> ****************
> FUNCTION LERG_CLLI(sLECNPA IN VARCHAR2,
> sLECNXX IN VARCHAR2,
> sMCINPA IN VARCHAR2,
> sMCINXX IN VARCHAR2)
> RETURN VARCHAR2 IS RET_LERG_CLLI VARCHAR2(11);
> SWAP VARCHAR2 (11);
>
> BEGIN
> SELECT DISTINCT LEC_SWTCH_CLLI INTO SWAP
> FROM CIS_COLLO_T
> WHERE (LEC_NPA = sLECNPA) AND
> (LEC_NXX = sLECNXX) AND
> (MCI_NPA = sMCINPA) AND
> (MCI_NXX = sMCINXX);
> EXCEPTION
> WHEN NO_DATA_FOUND THEN
> null;
> IF SWAP is NULL THEN
> RET_LERG_CLLI :='N/A';
> ELSE RET_LERG_CLLI := SWAP;
> END IF;
> RETURN RET_LERG_CLLI;
> END;
>
> *********************
> Dwight
>

Your IF statement is inside your exception clause. try rearranging it like this:

 FUNCTION LERG_CLLI(sLECNPA IN VARCHAR2,
                    sLECNXX IN VARCHAR2,
                    sMCINPA IN VARCHAR2,
                    sMCINXX IN VARCHAR2)
 RETURN  VARCHAR2 IS
     RET_LERG_CLLI VARCHAR2(11);
     SWAP VARCHAR2 (11);

    BEGIN
      SELECT  DISTINCT LEC_SWTCH_CLLI INTO SWAP
        FROM CIS_COLLO_T
      WHERE (LEC_NPA = sLECNPA) AND
            (LEC_NXX = sLECNXX) AND
            (MCI_NPA = sMCINPA) AND
            (MCI_NXX = sMCINXX);

     IF SWAP is NULL THEN
         RET_LERG_CLLI :='N/A';
     ELSE RET_LERG_CLLI := SWAP;
     END IF;
     RETURN RET_LERG_CLLI;

     EXCEPTION
      WHEN NO_DATA_FOUND THEN
         RETURN null;

 END; Sometimes it just takes another pair of eyes to see the problem. (PS drop me a reply. That code looks very familiar!)

--
Ed Prochak
Magic Interface, Ltd.
440-498-3702

-----== Posted via Deja News, The Leader in Internet Discussion ==----- http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum Received on Fri Aug 28 1998 - 15:49:37 CDT