| Renditions and Conditions [message #36074] |
Fri, 02 November 2001 11:08  |
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  |
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;
----------------------------------------------------------------------
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
