Re: Seems to be perfect.. but fails

Your code has two problems:

1. Your function can return only one value, but Select...Into can return multiple rows, so if it finds more than one row, your function is dead. You should write a cursor to ensure only one value: Function .... is Cursor C is Select.... from.... where...; (Note no INTO). Begin open C; Fetch C into SWAP; close C; return SWAP; End;
2. You have coded an exception clause, and stuffed your RETURN statement inside of it. If your select actually finds something, then no RETURN is executed. In most cases, you NEVER need to code an Exception clause.

Steve Cosner

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... but
> first I will let you know that I am passing 4 paramaters (ie.
> 212,818,212,457)... in thorough testing... these parameters are
> being passed correctly and assigned to the proper variables... This
> code works UNTIL it actually finds a positive match.... I have even
> manually entered the "s" values... ran the sql script against the
> dbase and it returned the value it should have
> (ie. SNAOTXIRDS8)... so the FROM and WHERE clause executes
> fine.. where it fails is trying to put this value into the SWAP
> variable.... I even tried "SELECT '12345678911' into SWAP" so that I
> know it is 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;
Received on Fri Aug 28 1998 - 10:55:38 CDT