Re: Select into problem

From: Maxim Demenko <mdemenko_at_arcor.de>
Date: Wed, 13 Dec 2006 15:28:32 +0100
Message-ID: <45800E10.1040503@arcor.de>

Michael O'Shea schrieb:
> Mladen Gogala wrote:
>

>> On Tue, 12 Dec 2006 13:32:50 -0800, Jun wrote:
>>
>>> SELECT STUD_AGE INTO v_cHP_MaxAge FROM STUDENT WHERE GDU =
>>> '015346001002';
>> SELECT STUD_AGE INTO v_cHP_MaxAge FROM (
>>   SELECT STUD_AGE FROM STUDENT WHERE GDU = '015346001002'
>>   UNION
>>   SELECT -1 AS STUD_AGE FROM DUAL
>> );
>>
>> That will never throw "NO_DATA_FOUND" exception.
>>
>> --
>> http://www.mladen-gogala.com

>
> Mladen Gogala wrote:
>

>> That will never throw "NO_DATA_FOUND" exception.

>
> Of course you are right Mladen. But if GDU '015346001002' is present in
> table STUDENT, your query will return two rows meaning a "fetch
> returned more than requested number of rows" error. The OP will have
> to limit the rows returned to one, and the right one.
>
> Regards,
> Mike
>
> TESSELLA Michael.OShea_at_tessella.com
> __/__/__/ Tessella Support Services plc
> __/__/__/ 3 Vineyard Chambers, ABINGDON, OX14 3PX, England
> __/__/__/ Tel: (44)(0)1235-555511 Fax: (44)(0)1235-553301
> www.tessella.com Registered in England No. 1466429
>

But to restrict it to only one row is not a big deal:

SELECT STUD_AGE INTO v_cHP_MaxAge FROM (

   SELECT STUD_AGE FROM STUDENT WHERE GDU = '015346001002'    UNION
   SELECT -1 AS STUD_AGE FROM DUAL
   ORDER BY DECODE(STUD_AGE,-1,1,0)
)
WHERE ROWNUM <=1;

Best regards

Maxim Received on Wed Dec 13 2006 - 08:28:32 CST