Home » SQL & PL/SQL » SQL & PL/SQL » Renditions and Conditions
Renditions and Conditions [message #36074] Fri, 02 November 2001 11:08 Go to next message
George Larry
Messages: 10
Registered: October 2001
Junior Member
Here's my latest problem- by the way... I'd like to thank all of you
that have helped me in the past. Your aid has been invaluable and I
appreciate it.
Now... I want a result set returned from my pl/sql procedures based
on various conditions. I'll spell it out for you:
Using JSP I've created a page where a user can select to have a
listing generated. The user can select a specific person, dept, or
code (or all people, depts, codes) as well as a range of dates.
These variables are passed to the report generation page... and
here'e where I start having trouble.
Because the user can select a specific person or all people the
condition is conditional (does that make sense?). In the past- not
using procedures... I would create a string for the conditions...

something like:
if ( personID != null ) {
conditionString = " WHERE personID = " + personID;
} else {
conditionString = "";
}
query = "SELECT * FROM myTable" + conditionString;

If it's possible to send strings to a pl/sql procedure... I don't
know how to do it.
So my question is... do I have to write 9 statements... for each
possiblity (ie
[[all people, all depts, all codes]]
SELECT * FROM myTable;
[[specific person, all depts, all codes]]
SELECT * FROM myTable WHERE personID = pID;
[[specific person, specific dept, specific code]]
SELECT * FROM myTable WHERE personID = pID AND deptID = dID AND
codeID = cID;
etc...)
...or is there a better way?
I'd appreciate any and all ideas.
Thanks.

----------------------------------------------------------------------
Re: Renditions and Conditions [message #36078 is a reply to message #36074] Fri, 02 November 2001 12:42 Go to previous message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
Build your condition string just like you did in your example (but don't use != when checking for a value, use IS NOT NULL instead).

Then, open your cursor like this:

open mycursor for 'select * from myTable ' || conditionString;

----------------------------------------------------------------------
Previous Topic: Cursed CURSORS...
Next Topic: Re: usage of temporary table in pl/sql procedure
Goto Forum:
  


Current Time: Thu Mar 28 06:11:36 CDT 2024