1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Hw to fetch all records when no values been selected?!

Discussion in 'SQL PL/SQL' started by Vicky, Jul 3, 2014.

  1. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    I have a query to find selected record according to the value which comes, and all records when no value comes.,.

    when value comes.,

    select * from employees where emp_name upper(nvl('(value)%', '%'));

    It works perfectly when valuie cmes.,.

    when value doesn't come., it's not working at all.,

    select * from employees where emp_name upper(nvl('%', '%'));

    I've also tried decode, but nthng wrks.,

    Could U tel me wat's the prblm with the query?!
     
  2. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Hi Vicky,

    Can u explain clearly?

    What value comes?
     
  3. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    Yes, as queried above by Jagadekara, it's not clear what you mean by :

    If I try:

    Code (SQL):
    SELECT * FROM employees WHERE emp_name  LIKE UPPER(nvl('&v%', '%'));
    it works fine, i.e., if you input something for variable "v" then you get only those records starting with the input value; if you don't enter anything for "v", then you get all records in employees table.

    You can even simplify it - no need to put NVL as the 1st argument will never be NULL !

    Code (SQL):
    SELECT * FROM employees WHERE emp_name LIKE UPPER('&v%');
     
  4. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    The value comes frm screen, which is send by Java Developers,..

    In the place of (value), some value comes from the screen.,

    Whn the value comes, it works,

    Whn the value doesn't come frm the screen,

    it's not workng.,.,
     
  5. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    Try this one:

    Code (SQL):
    SELECT * FROM employees WHERE emp_name LIKE UPPER(:v||'%');
    where you can replace the ":v" with your Java variable.

    You usually do something like:

    Code (SQL):
    ResultSet rset = stmt
            .executeQuery("SELECT * FROM employees WHERE emp_name LIKE upper('" + VALUE + "'%')");
     
  6. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    Thanks Rajen.,

    But is'nt possible to dotat in the query itself,




    for exmpl: take Emp_name as Robin.,

    while the value comes,

    select * from employees where emp_name like upper(nvl('(Robin)%', '%'));

    It's fetch the correct record.,

    but while no value cmes,

    select * from employees where emp_name like nvl('%','%' )

    it's fetchng all records excluding emp_name having 'null' as values.,

    Why it's omitting records having 'null' as values even on using '%' to fetch all records.,.
     
  7. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    Ok Vicky, I see your point now :) !

    The issue is with existence of NULL values in emp_name.
    As you may know now, comparing with NULL will always return false (even condition NULL = NULL will return false).
    So, you'll never get the employees with emp_name NULL with the LIKE operator or comparing '%' to NULL (although I admit, it's quite strange that in EMPLOYEES table you have employees who don't have a name !!).

    The only way to do it is by using the "IS NULL" condition or NVL on emp_name, i.e., you'll have to add the following condition in your query:

    Code (SQL):
    SELECT * FROM employees WHERE emp_name  LIKE UPPER(:v||'%') OR (:v IS NULL AND emp_name IS NULL);
    OR

    Code (SQL):
    SELECT * FROM employees WHERE NVL(emp_name,'%')  LIKE UPPER(:v||'%');
     
    Vicky likes this.