sqlibm-midrangedb2-400

DB2/400 SQL subqueries

I want to make sure that warehouses C and D are both in the same City.

Is there simpler way to do this?

Table:

City Warehouse
1      A        
1      B
2      C
2      D 
3      E
3      F

SQL:

WITH subquery AS(
  SELECT DISTINCT WHM.City FROM WHM WHERE  
  WHM.Warehouse = 'C' or WHM.Warehouse ='D'
  GROUP BY WHM.City 
  ORDER BY WHM.City ) 
SELECT count(*) FROM subquery

If response = 1, then True

Solution

  • This is a minor improvement on what you already have, but you could select the distinct count and avoid the unnecessary subquery:

    SELECT COUNT(DISTINCT City)     -- returns 1 for a single shared city
FROM WHM
WHERE Warehouse IN ('C', 'D');

    Note that GROUP BY is not needed as we want the distinct count across the entire table.