Results 1 to 7 of 7
  1. #1
    Join Date
    Jun 2006
    Posts
    22

    Question Unanswered: Display Rows Based on Criteria

    I'm querying a view that only lists a queue if it has something in it. I want to display a count of the items in queues 1- 4, even if there is nothing in the queue.

    For instance:
    Que1 0
    Que2 10
    Que3 0
    Que4 569

    The query below displays the queue if there is something in it, how do I force it to display all four queues regardless of whether or not the queue has items?

    SELECT queues, Count(items)
    FROM queueview
    GROUP BY queues
    HAVING (queues In ('que1','que2','que3','que4')
    ORDER BY queues

  2. #2
    Join Date
    Jun 2007
    Location
    germany
    Posts
    155
    I would think that you need to see the SQL involved with VIEW queueview.

    I am not an 'expert', but nothing in the SQL that you provided precludes selection of queues with zero items.

  3. #3
    Join Date
    Jun 2006
    Posts
    22
    Nothing precludes it but if the queue is empty it doesn't show in the view. I guess if one of the queues isn't showing in the view, I would like to list that queue with a count of 0. Is this possible?

  4. #4
    Join Date
    Jun 2007
    Location
    germany
    Posts
    155
    Nothing precludes it but if the queue is empty it doesn't show in the view.
    which means the SQL invoked to select the VIEW (the VIEW definition) has something along the lines of COUNT(ITEMS) > 0.

    I guess if one of the queues isn't showing in the view, I would like to list that queue with a count of 0. Is this possible?
    not by SELECTing FROM the VIEW.

    you would need to SELECT FROM basetable.

  5. #5
    Join Date
    Jun 2007
    Location
    germany
    Posts
    155
    Nebraska God,

    you should take the time to look at the DDL that created the VIEW.

  6. #6
    Join Date
    Jun 2007
    Location
    germany
    Posts
    155
    or you could try something like this if you can't access basetable:
    Code:
    SELECT QUEUES
         , SUM(ITEMS)
    FROM (
      SELECT queues, Items
      FROM queueview
      WHERE QUEUES IN('que1','que2','que3','que4')
    UNION ALL
       SELECT char('que1')
            , INT(0)
       FROM SYSIBM.SYSDUMMY1
    UNION ALL
       SELECT char('que2')
            , INT(0)
       FROM SYSIBM.SYSDUMMY1
    UNION ALL
       SELECT char('que3')
            , INT(0)
       FROM SYSIBM.SYSDUMMY1
    UNION ALL
       SELECT char('que4')
            , INT(0)
       FROM SYSIBM.SYSDUMMY1) as v(QUEUES,ITEMS)
    GROUP BY QUEUES
    ORDER BY QUEUES
    Last edited by dbzTHEdinosaur; 01-21-10 at 07:12. Reason: correct sql

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    The query below displays the queue if there is something in it, how do I force it to display all four queues regardless of whether or not the queue has items?

    SELECT queues, Count(items)
    FROM queueview
    GROUP BY queues
    HAVING (queues In ('que1','que2','que3','que4')
    ORDER BY queues
    Try this(Not tested):
    Code:
    SELECT p.queues, Count(q.items)
      FROM (VALUES 'Que1' , 'Que2' , 'Que3' , 'Que4'
           ) p(queues)
      LEFT OUTER JOIN
           queueview q
       ON  q.queues = p.queues
     GROUP BY
           p.queues
     ORDER BY
           p.queues

    If "VALUES 'Que1' , 'Que2' , 'Que3' , 'Que4'" was not supported on your DB2,
    you can use the following instead of it.
    SELECT 'Que1' FROM sysibm.sysdummy1 UNION ALL
    SELECT 'Que2' FROM sysibm.sysdummy1 UNION ALL
    SELECT 'Que3' FROM sysibm.sysdummy1 UNION ALL
    SELECT 'Que4' FROM sysibm.sysdummy1

Posting Permissions

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