Results 1 to 9 of 9
  1. #1
    Join Date
    Apr 2008
    Posts
    5

    Unanswered: How to Return null when 0 row are selected

    Hi all,

    I want to my select statement to return 1 row(NULL) if 0 rows are selected.
    I can't use dual and I can only use simple sql(result should be from one select query).

    Example Query:
    select name
    from employee
    where id=1;

    Result:
    0 rows selected

    Wanted Result:
    1 row selected

    ------------------------------------------------------------------

    They query below returns a 0 as the result.

    select count(*)
    from employees
    where employee_id=1;

    I have the idea of using count, so it count is 0, assign the value to null. However, the syntax is wrong as I can't put count in decode.

    SQL Error: ORA-00937: not a single-group group function

    Any pointers are appreciated.

    Thank you for your time.

    select decode(count(employee_id), 0, null, employee_id) from
    (select employee_id
    from employees
    where employee_id=1);
    Last edited by uncorn23; 04-02-08 at 00:52.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >I have the idea of using count, so it count is 0, assign the value to null.
    You obviously are totally & 100% clueless about "NULL".
    NULL is not something to which anything can be assigned.

    Perhaps you should research NVL & DECODE

    From http://download.oracle.com/docs/cd/B....htm#sthref736

    Nulls Indicate Absence of Value
    A null is the absence of a value in a column of a row.
    Nulls indicate missing, unknown, or inapplicable data.
    A null should not be used to imply any other value, such as zero.
    Last edited by anacedent; 04-02-08 at 00:42.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Apr 2008
    Posts
    5
    I can use decode to assign null to the output in this case.

    Sample query
    select decode(employee_id, 100, NULL, employee_id)
    from employees
    where employee_id=100;

    Result
    DECODE(EMPLOYEE_ID,100,NULL,EMPLOYEE_ID)
    ----------------------------------------


    1 rows selected

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >I can use decode to assign null to the output in this case.
    But why do you want/need to do so?
    Have you solved your problem?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Apr 2008
    Posts
    5
    The reason of this is because

    the query composed is like this

    select t1.date,
    t2.date,
    t3.date,
    t4.date
    from
    (select date from employees WHERE <condition>)t1 ,
    (select date from employees WHERE <condition>)t2,
    (select date from employees WHERE <condition>)t3,
    (select date from employees WHERE <condition>)t4 ;

    t1,t2,t3 and t4 can either return a date or nothing.

    In this case for example t1, t2 and t3 return a date individually but t4 return no rows.

    This will cause the whole select statement to return no row.

    I would like t4 to return a NULL if no rows are selected in t4 because of the <condition> so that my query as a whole will return
    --------------------------------------
    t1.date t2.date t3.date t4.date
    date date date

    1 row selected
    --------------------------------------

    instead of

    --------------------------------------
    t1.date t2.date t3.date t4.date

    0 row selected
    --------------------------------------

    Maybe there is a better way to engineer the query. Any pointers are appreciated. Thank you for your time.

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    What business problem are you trying to solve?
    How would an independent observer conclude you had succeeded?

    From where I sit, you have a SQL statement which you have concluded to be a solution; but not quiet a complete one.

    You have NOT provided enough input or actual requirements for any one else to independently solve the same problem you want solved.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  7. #7
    Join Date
    Apr 2008
    Posts
    5
    I have not gotten the query which works the way I wanted it to be. That was why I posted the question here.

  8. #8
    Join Date
    Feb 2008
    Posts
    26
    Hello !

    In fact, there is an exception when no data is found during SELECT in Oracle whereas in other dbms, they return NULL.
    The solution that I use:
    Original query : SELECT name FROM table1 WHERE mat = 1

    1)
    SELECT (SELECT count(name) FROM table1 WHERE mat = 1) FROM DUAL

    in a procedure
    SELECT (SELECT count(name) FROM table1 WHERE mat = 1) into v_count FROM DUAL

    2)
    SELECT name into v_name FROM table1 WHERE mat = 1;
    EXCEPTION
    WHEN NO_DATA_FOUND THEN
    v_name = NULL?

  9. #9
    Join Date
    Apr 2008
    Posts
    5
    Thank you to everyone who replied.
    I got he solution that I wanted, thanks to John Spencer.

    Solution
    SELECT CASE WHEN location_id = 1 and
    flag_a = 'A' and
    flag_b = 'B' THEN date END date1,
    CASE WHEN location_id = 2 and
    flag_a = 'F' and
    flag_b = 'B' THEN date END date2,
    CASE WHEN location_id = 3 and
    flag_a = 'A' and
    flag_b = 'K' THEN date END date3,
    CASE WHEN location_id = 4 and
    flag_a = 'A' and
    flag_b = 'Z' THEN date END date1,
    FROM employees WHERE location_id IN (1, 2, 3, 4) and
    flag_a IN ('A', 'F') and
    flag_b IN ('B', 'K', 'Z')

    assuming below is my original query

    select t1.date,
    t2.date,
    t3.date,
    t4.date
    from
    (select date from employees
    WHERE where location_id = 1
    and flag_A = 'A'
    and flag_b = 'B')t1 ,


    (select date from employees
    WHERE where location_id = 2
    and flag_A = 'F'
    and flag_b = 'B')t2,

    (select date from employees WHERE
    where location_id = 3
    and flag_A = 'A'
    and flag_b = 'K')t3,

    (select date from employees
    WHERE where location_id = 4
    and flag_A = 'A'
    and flag_b = 'Z')t4 ;

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •