Results 1 to 8 of 8
  1. #1
    Join Date
    Jul 2003
    Posts
    2,296

    Unanswered: Cannot get '0' when analytics are NULL??

    Why can't I get a zero here?

    PHP Code:
    platform@kod1SELECT NVL(x0FROM (
    SELECT COUNT(*) over (PARTITION BY cust_id
    FROM customer WHERE org_id 
    'x');

    no rows selected 
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  2. #2
    Join Date
    Apr 2004
    Location
    USA
    Posts
    33
    Try this:

    select nvl((SELECT 1 FROM (
    SELECT COUNT(*) over (PARTITION BY cust_id) x
    FROM customer WHERE org_id = 'x')), 0)
    from dual;

  3. #3
    Join Date
    Jul 2003
    Posts
    2,296
    this works, thanks.

    I am wondering why it has to be encapsulated into a FROM DUAL clause.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  4. #4
    Join Date
    May 2004
    Location
    Redwood Shores, CA
    Posts
    68
    Quote Originally Posted by The_Duck
    this works, thanks.

    I am wondering why it has to be encapsulated into a FROM DUAL clause.
    because it does not resolve to the alias on this level
    you have to use subselect (inline view) to pick up your alias x
    My way or the highway. Yeah

  5. #5
    Join Date
    Apr 2004
    Location
    USA
    Posts
    33
    Quote Originally Posted by The_Duck
    this works, thanks.

    I am wondering why it has to be encapsulated into a FROM DUAL clause.
    Do you have any records satisfying the condition < org_id = 'x' >. If not, the select fails and nvl does not work. That's why I put it over DUAL since dual always has one record.

    Nvl works only with null values and not with no rows.

  6. #6
    Join Date
    Jul 2003
    Posts
    2,296
    Quote Originally Posted by arvindram
    Do you have any records satisfying the condition < org_id = 'x' >. If not, the select fails and nvl does not work. That's why I put it over DUAL since dual always has one record.

    Nvl works only with null values and not with no rows.
    Mutha Trucka!

    I wish NVL would satisfy the NO ROWS condition as well.
    WHY NOT right?

    OR why not implement some function based on NO ROWS returned like:

    PHP Code:
    select norow(x0from (
    SELECT COUNT(*) over (PARTITION BY cust_id
    FROM customer WHERE org_id 
    'x'); 
    stupid oracle ...
    (or stupid coder which would mean me)
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  7. #7
    Join Date
    May 2004
    Location
    Redwood Shores, CA
    Posts
    68
    You got me fulled. Too many x's.
    My way or the highway. Yeah

  8. #8
    Join Date
    Oct 2003
    Posts
    706
    I sense a more fundamental conceptual-design issue lurking behind the various implementation-specific ways (e.g. "DUAL") that you're devising to "fix" it.

    "No rows" is not the same as "zero" and it never will be. Likewise, "NULL" is not "zero" and it never will be.

    When you issue a query, non-matching rows will never be returned; NULL field values will never be averaged; and so on. Sometimes you can get what you want by LEFT JOINing such a result against a table or query which provides zeroes for all rows. But more often, the best approach to a problem might be to devise a series of queries which, upon their completion, give you a (temporary) table with the final results you want to see.

    The world is full of "jerry-mandred queries" which "happen to work now" but that become maintenance nightmares when the application requirements change.
    ChimneySweep(R): fast, automatic
    table repair at a click of the
    mouse! http://www.sundialservices.com

Posting Permissions

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