DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Procedure help

  1. #1
    Join Date
    Feb 2002
    Posts
    267

    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.

  2. #2
    Join Date
    Jan 2003
    Location
    Hull, UK
    Posts
    220
    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

  3. #3
    Join Date
    Jan 2003
    Location
    Hull, UK
    Posts
    220

  4. #4
    Join Date
    Jan 2004
    Posts
    5
    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

  5. #5
    Join Date
    Feb 2002
    Posts
    267
    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 ?

  6. #6
    Join Date
    May 2001
    Location
    San Francisco, California
    Posts
    511
    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
  •  


Click Here to Expand Forum to Full Width