-
Procedure help
Hi,
I have two tables
Emp_main
Emp_id----Dept---Region
Emp_info
Emp_id---T1_Date-----Account---Customer
We have a front end search Screen, which has to display the count of employees, based on certain conditons, say where Account=xyz or
Customer='abc' or T1_Date between
I am planning to have a Procedure with all the possible search parameters as IN parameters and have a single query as follows.
select count(emp_id)
from emp_main a, emp_info b
where t1_Date between and
and Account=
and customer=
and a.emp_id=b.emp_id
Now my problem is, if the front end user wants to search only based on T1_date, rest of the IN parameters will be null.
Say account,customer will be null.
Some other user may search with with all the values for all the conditions.
How can i handle this situation, when he searches based on only one value.
can some body guide me.
Regards
Sonia
Last edited by Sonia; 01-28-2004 at 05:25 AM.
-
Hi,
I think u need to use dynamic sql. depending on the parameters passed u need to build ur sql stmt and then execute it...
Srini
-
-
Based on the query you typed, it looks like your query string is a concatenation with the values entered, in which case you're already usign dynamic SQL. If not, then instead you could do something like this:
select count(emp_id)
from emp_main a, emp_info b
where (t1_Date between _start and _end
or _start is null or _end is null)
and (Account= _account or _account is null)
and (customer= _customer or _customer is null)
and a.emp_id=b.emp_id
-
jabberwocky ,
Thx for the reply,
But if i have to search based on ACCOUNT and CUSTOMER,
and i am sending an IN parameter, i want the cout to be based on both.
But if Customer is NULL and Account has a value, then i want to exclude that in the count.
But the count is added for NULL as well....
how do i handle this ?
-
What is your frontend? Is it forms?
Remember the Golden Rule - He who has the gold makes the rules!
===================
Kris109
Ph.D., OCP 8i, 9i, 10g, 11g DBA
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|