Display Rows Based on Criteria

Nebraska_God · #1 (**permalink**) 01-21-10, 03:38

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

dbzTHEdinosaur · #2 (**permalink**) 01-21-10, 03:46

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.

Nebraska_God · #3 (**permalink**) 01-21-10, 03:48

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?

dbzTHEdinosaur · #4 (**permalink**) 01-21-10, 03:54

Quote:

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.

Quote:

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.

dbzTHEdinosaur · #5 (**permalink**) 01-21-10, 03:58

Nebraska God,

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

dbzTHEdinosaur · #6 (**permalink**) 01-21-10, 06:07

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

tonkuma · #7 (**permalink**) 01-21-10, 09:52

Quote:

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

Internet Services

Web Development

Digital Marketing

Consumer Tech