-
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
-
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 :p_start and :p_end
or :p_start is null or :p_end is null)
and (Account= :p_account or :p_account is null)
and (customer= :p_customer or :p_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?