"Exists" gives you a trappable error. Encapsulate the statement into its own
block and use an exception to branch your logic from there. An equivalent to
this is opening an implicit cursor and trapping the NO_DATA_FOUND.
Otherwise, you would have to construct some spaghetti SQL to obtain a
TRUE/FALSE/NULL decision, like decodes or case statements. Depends on your
needs, but that could save you time.
"Sandy Murdock" <jmurdock_at_dialoguewizard.com> wrote in message
news:ac4a5663.0111301520.3dfb14e5_at_posting.google.com...
> I am having trouble.
>
> I am trying to write a query into a package which will use the
> 'Exists' clause.
>
> I am trying to use parameters to decide if a row matching the one I am
> about to enter exists, ie:
>
> I have a number of courses, I want to be able to run the same 'course'
> using different dates, so if the course code exists, and it matches
> the date that I am about to insert, I know it is a duplicate, and I
> want to prevent that.
>
> I have tried the following:
>
> select 1 into varExists from Dual
> where Exists (select max(CourseID) from sch_Course_Table where
> CourseNumber = varCourseNumber and deptCodeId =varDeptCodeID and
> season=varseason and courseYear=varcourseYear);
>
> This works fine as long as the query returns a row - however, whenever
> the query inside the exists statement does not contain rows - the
> package/procedure stops with a 'No Data Found' error.
>
> The purpose of this is to allow branching logic, if exists is true -
> update, if exists is false insert.
>
> If exists is false it dies.
>
> What am I doing wrong??
>
> Thank you.
Received on Fri Nov 30 2001 - 17:42:49 CST
