SELECT 
    P.PRODNR, P.PRODNAME,
    (SELECT SUM(QUANTITY) 
     FROM PO_LINE POL
     WHERE P.PRODNR = POL.PRODNR) AS TOTALORDERED
FROM 
    PRODUCT P
WHERE 
    P.PRODNR IN (SELECT P.PRODNR
                 FROM PO_LINE
                 WHERE QUANTITY >= ALL (SELECT QUANTITY
                                        FROM PO_LINE))

I need to get the table where TOTALORDERED column should not give me any NULL values. I tried IS NOT NULL in all both subquery and main query but did not work.

First attempt:

SELECT
   P.PRODNR, P.PRODNAME,
   (SELECT SUM(QUANTITY) 
    FROM PO_LINE POL
    WHERE P.PRODNR = POL.PRODNR) AS TOTALORDERED
FROM
    PRODUCT P
WHERE 
    TOTALORDERED IS NOT NULL
    AND P.PRODNR IN (SELECT P.PRODNR
                     FROM PO_LINE
                     WHERE QUANTITY >= ALL (SELECT QUANTITY
                                            FROM PO_LINE))

Error:

Unknown column 'TOTALORDERED' in 'where clause'

CodePudding user response：

I think your error was caused by using TOTALORDERED alias in the where section and it's not yet available in there.

Select * from (
    SELECT P.PRODNR, P.PRODNAME,
    (SELECT SUM(QUANTITY) FROM PO_LINE POL
    WHERE P.PRODNR = POL.PRODNR) AS TOTALORDERED
    FROM PRODUCT P
    join (SELECT PRODNR
                    FROM PO_LINE
                    WHERE QUANTITY >= ALL
                                    (SELECT QUANTITY
                                    FROM PO_LINE
                                    )
          )pl on pl.PRODNR = P.PRODNR
)t 
WHERE TOTALORDERED is not NULL

Update: new answer based on query in question

select * from (
SELECT 
    P.PRODNR, P.PRODNAME,
    (SELECT SUM(QUANTITY) 
     FROM PO_LINE POL
     WHERE P.PRODNR = POL.PRODNR) AS TOTALORDERED
FROM 
    PRODUCT P
WHERE 
    P.PRODNR IN (SELECT P.PRODNR
                 FROM PO_LINE
                 WHERE QUANTITY >= ALL (SELECT QUANTITY
                                        FROM PO_LINE))
)s
WHERE TOTALORDERED is not NULL