Re: Select into problem

Jaap W. van Dijk wrote:
> On 12 Dec 2006 13:32:50 -0800, "Jun" <frankjunli_at_gmail.com> wrote:
>
> >Hi Guys,
> >
> > I have a question about PL/SQL code
> >
> > I have the following select into statement :
> >
> > SELECT STUD_AGE INTO v_cHP_MaxAge FROM STUDENT WHERE GDU =
> >'015346001002';
> >
> > I know It has no value returned but it give a no_data_found error.
> > How do I escape that error without using exception?
> > Is there any way to force the query return some value instead of an error?
> >
> >Thanks in advance
> >
> select (select STUD_AGE FROM STUDENT WHERE GDU = '015346001002')
> INTO v_cHP_MaxAge
> FROM DUAL;
>
> This will be null if the inner select returns no records and throw an
> error when it returns more than one.
>
> Regards, Jaap.

Or I guess

SELECT MAX(stud_age) INTO v_chp_maxage
FROM students
WHERE gdu = 015346001002

which deals with NO_DATA_FOUND. However then you wouldn't know if there were more studs than expected, so you could go one hack further with

SELECT MAX(stud_age), COUNT(*)
INTO v_chp_maxage, v_stud_count
FROM students
WHERE gdu = 015346001002;

Possibly though it would be better to use exceptions for what they were designed to do. Received on Fri Dec 15 2006 - 17:32:11 CST