CODEWITHSUNDEEP
sqldb2
Lawrence Ferguson
Lawrence Ferguson

Reputation: 321

JOIN using IN with a sub query

I'm trying to do a join using a sub query with IN but I get an error "Comparison Operator IN not valid"

Is there a way to overcome this?

Here is my sql which doesn't work:

    SELECT
    SIZE,
    COUNT(SIZE)

FROM
  (SELECT ITEM,
        MIN(SZCUBC) AS CUBE
        FROM XXX.ITEM 
        JOIN XXX.ITEMA ON IAITEM = ITEM 
        LEFT JOIN DB.SIZECD ON ITDHGT*ITDWTH*ITDDTH*QTY <= SZCUBC AND SIZE IN (SELECT SIZEB FROM DB.LOC WHERE BQTY>0 GROUP BY SIZEB) 
        WHERE IASOHQ >0 
        GROUP BY ITEM) t

LEFT JOIN DB.SIZECD T1 ON SZCUBC = CUBE 

GROUP BY
    SIZE

I was using the below which works, but isn't dynamic...

    SELECT
    SIZE,
    COUNT(SIZE)

FROM
  (SELECT ITEM,
        MIN(SZCUBC) AS CUBE
        FROM XXX.ITEM 
        JOIN XXX.ITEMA ON IAITEM = ITEM 
        LEFT JOIN DB.SIZECD ON ITDHGT*ITDWTH*ITDDTH*QTY <= SZCUBC AND SIZE IN ('TA','TB','TC','TD','TE','TF') 
        WHERE IASOHQ >0 
        GROUP BY ITEM) t

LEFT JOIN DB.SIZECD T1 ON SZCUBC = CUBE 

GROUP BY
    SIZE

Upvotes: 0

Views: 64

Answers (3)

Mark Barinstein
Mark Barinstein

Reputation: 12454

Assuming that SIZE belongs to DB.SIZECD.

LEFT JOIN 
(
DB.SIZECD C  
JOIN (SELECT SIZEB FROM DB.LOC WHERE BQTY>0 GROUP BY SIZEB) G ON G.SIZEB = C.SIZE
) ON ITDHGT*ITDWTH*ITDDTH*QTY <= SZCUBC

Instead of:

LEFT JOIN DB.SIZECD ON ITDHGT*ITDWTH*ITDDTH*QTY <= SZCUBC AND SIZE IN 
(SELECT SIZEB FROM DB.LOC WHERE BQTY>0 GROUP BY SIZEB)

We can't move the subselect with DB.LOC outside the LEFT JOIN to preserve the logic.

Upvotes: 1

Parfait
Parfait

Reputation: 107767

Per DB2 docs on possibly a related error:

The ON clause cannot contain a subquery.

Therefore consider a WHERE replacement:

...
  (SELECT ITEM,
        MIN(SZCUBC) AS CUBE
   FROM XXX.ITEM 
   JOIN XXX.ITEMA ON IAITEM = ITEM 
   LEFT JOIN DB.SIZECD ON ITDHGT*ITDWTH*ITDDTH*QTY <= SZCUBC
   WHERE IASOHQ > 0 
     AND SIZE IN (SELECT SIZEB 
                  FROM DB.LOC 
                  WHERE BQTY > 0) 
   GROUP BY ITEM) t

Upvotes: 1

ScaisEdge
ScaisEdge

Reputation: 133400

You could try replacing the IN clause with a JOIN clause

(and i removed the group by without aggreagtion function in you subquery with a distinct clause )

    SELECT
    SIZE,
    COUNT(SIZE)
FROM
  ( SELECT ITEM,
        MIN(SZCUBC) AS CUBE
        FROM XXX.ITEM 
        JOIN XXX.ITEMA ON IAITEM = ITEM 
        JOIN  (
          SELECT DISTINCT SIZEB 
          FROM DB.LOC 
          WHERE BQTY>0
        ) t2 ON t2.SIZEB = DB.SIZECD.SIZE
        LEFT JOIN DB.SIZECD ON ITDHGT*ITDWTH*ITDDTH*QTY <= SZCUBC 
        WHERE IASOHQ >0 
        GROUP BY ITEM
        ) t
LEFT JOIN DB.SIZECD T1 ON SZCUBC = CUBE 

GROUP BY  SIZE

Upvotes: 1

Related Questions