Results 1 to 5 of 5

Thread: Null ???

  1. #1
    Join Date
    Nov 2003
    Posts
    87

    Smile Unanswered: Null ???

    hi,


    SQL> SELECT COUNT(*)
    2 FROM TBSD_PRUD_PARAMS
    3 /

    COUNT(*)
    ----------
    48

    SQL> SELECT COUNT(*)
    2 FROM TBSD_PRUD_PARAMS
    3 WHERE
    4 UPPER(PRUD_VALUE_TYPE) LIKE('%')
    5 AND UPPER(PRUD_PARAM_NAME) LIKE('%')
    6 AND UPPER(PRUD_PARAM_ID) LIKE('%')
    7 /

    COUNT(*)
    ----------
    32


    Here in second query Not counting NULL records.


    SQL> SELECT COUNT(*)
    2 FROM TBSD_PRUD_PARAMS
    3 WHERE
    4 UPPER(PRUD_VALUE_TYPE) LIKE('%') OR PRUD_VALUE_TYPE IS
    NULL
    5 AND UPPER(PRUD_PARAM_NAME) LIKE('%') OR
    PRUD_PARAM_NAME IS NULL
    6 AND UPPER(PRUD_PARAM_ID) LIKE('%')
    7 /

    COUNT(*)
    ----------
    48

    Here in 3rd im getting NULL also.

    But if any condition is there i dont want to get null fields.
    like

    SQL> SELECT COUNT(*)
    2 FROM TBSD_PRUD_PARAMS
    3 WHERE
    4 UPPER(PRUD_VALUE_TYPE) LIKE('C%')
    5 AND UPPER(PRUD_PARAM_NAME) LIKE('%')
    6 AND UPPER(PRUD_PARAM_ID) LIKE('%')
    7 /

    COUNT(*)
    ----------
    10


    So my requirement is to get the NULL records also if the
    condition is null,

    that is second query has to be modified depends on condition.

    Can anybody help?
    thanx .

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    could you please rephrase your question

    there is no such thing as a "null record", only null columns

    your last query, the one that returns 10 rows, looks fine to me

    but it is hard to tell where the 10 came from, because the 2nd and 3rd conditions accept any value, so there's no telling which of the 32 they are

    anyhow despite not understanding your question, i suggest you consider using the NVL function, perhaps something like this --

    ... where upper(nvl(columname,'')) like '%A'
    Last edited by r937; 01-24-04 at 10:08.
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Nov 2003
    Location
    Bangalore,India
    Posts
    51

    Re: Null ???

    IS NULL,NVL OR DECODE could be of rescue..

  4. #4
    Join Date
    Sep 2003
    Location
    Milan, Italy
    Posts
    130
    I haven't really understood what you want ... but, do you know that

    where a or b and c

    gets evaluated as

    where a or (b and c)

    ?

    HTH
    al

  5. #5
    Join Date
    Nov 2003
    Posts
    87
    hi all,

    leave it.
    it got worked for me.
    here is the query i used.

    thanx 2 all.

    SELECT COUNT(*)
    FROM tbsd_prud_params
    WHERE UPPER (NVL (prud_value_type, '-NULL')) LIKE 'C%'
    AND UPPER (NVL (prud_param_name , '-NULL')) LIKE '%'
    AND UPPER (NVL (prud_param_id, '-NULL')) LIKE '%'


Posting Permissions

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