CODEWITHSUNDEEP
sqldb2-400
Pinchas K
Pinchas K

Reputation: 1571

Not getting any data is the last select ok?

This is a db2 query but like SQL Server. I am expecting data here that will have all rows where there is no freight credit row (FRTCRDM)

This is orders data. All orders sb multi rows based on each item sold gets a row:

Order#    Item        qty    Price etc
123        Widget1     1      10.00
123        Widget2     2      20.00
123        FRTWDG      1      5.00
123        FRTCRDM     1      2.00

So here, we have a shipping discount of 2.00. What I want is, all records where there was no FRTCRDM, no shipping discount. So we would not want the above example. We would want it if the last row was missing. 

CREATE VIEW ralib.shpfrtn AS
    SELECT T01.IDORD#, T01.IDDOCD, T01.IDPRT#, 
           T01.IDSFX#, T01.IDSHP#, T01.IDNTU$, T01.IDENT# ,
           (T01.IDNTU$ * T01.IDSHP# ) AS LINTOT, 
           T02.IARCC3, T02.IAPRLC , T01.IDORDT, T01.IDHCD3 
    FROM ASTDTA.OEINDLID T01
      INNER JOIN ASTDTA.ICPRTMIA T02 ON T01.IDPRT# = T02.IAPRT# 
    WHERE t01.iddocd > 20131231
      AND t01.IDHCD3 = 'MDL'    
      AND t01.idord# NOT IN (SELECT t01.idord# FROM astdta.oeindlid b
                             WHERE idprt# = 'FRTCRDM')

Upvotes: 0

Views: 34

Answers (2)

dnoeth
dnoeth

Reputation: 60472

It's probably the SELECT t01.idord#, it results in a correlated subquery (in fact it's idord# not in (idord#)which is always wrong), change it to SELECT idord#:

SELECT T01.IDORD#, T01.IDDOCD, T01.IDPRT#, 
       T01.IDSFX#, T01.IDSHP#, T01.IDNTU$, T01.IDENT# ,
       (T01.IDNTU$ * T01.IDSHP# ) AS LINTOT, 
       T02.IARCC3, T02.IAPRLC , T01.IDORDT, T01.IDHCD3 
FROM ASTDTA.OEINDLID T01
  INNER JOIN ASTDTA.ICPRTMIA T02 ON T01.IDPRT# = T02.IAPRT# 
WHERE t01.iddocd > 20131231
  AND t01.IDHCD3 = 'MDL'    
  AND t01.idord# NOT IN (SELECT idord# FROM astdta.oeindlid b
                         WHERE idprt# = 'FRTCRDM')

Or change it to a correct correlated subquery using NOT EXISTS:

SELECT T01.IDORD#, T01.IDDOCD, T01.IDPRT#, 
       T01.IDSFX#, T01.IDSHP#, T01.IDNTU$, T01.IDENT# ,
       (T01.IDNTU$ * T01.IDSHP# ) AS LINTOT, 
       T02.IARCC3, T02.IAPRLC , T01.IDORDT, T01.IDHCD3 
FROM ASTDTA.OEINDLID T01
  INNER JOIN ASTDTA.ICPRTMIA T02 ON T01.IDPRT# = T02.IAPRT# 
WHERE t01.iddocd > 20131231
  AND t01.IDHCD3 = 'MDL'    
  AND NOT EXISTS
    (SELECT * FROM astdta.oeindlid b
     WHERE idprt# = 'FRTCRDM'
       AND t01.idord# = b.idord#)

Upvotes: 0

jpw
jpw

Reputation: 44891

In the last clause in the where you need to remove the table prefix from the subquery:

NOT IN (SELECT idord# FROM astdta.oeindlid WHERE idprt# = 'FRTCRDM')

Right now you are referring to the id of the outer table t01 and the not in will not work as intended.

Upvotes: 2

Related Questions