0

I have a SQL like this below

SELECT A.UNIT, A.PO_NO, A.LINE, A.QUNATITY, 
       SUM(B.RECEIVED_QUNATITY) AS "RECEIVED QUANTITY" 
FROM PO_TBL A, RECEIVER_TBL B 
WHERE A.UNIT = B.UNIT AND A.PO_NO = B.PO_NO AND A.LINE = B.LINE 
GROUP BY A.UNIT, A.PO_NO, A.LINE, A.QUNATITY 
HAVING ((A.QUNATITY - SUM(B.RECEIVED_QUNATITY)) > 0);

The above SQL return more rows if the Having function is not used and returns null rows, when we using Having function. Even though, Quantity has value as "10" and RECEIVED_QUNATITY has value as "0", that rows are not shown in the output. Kindly help me on this scenario......

Masoud
  • 8,020
  • 12
  • 62
  • 123
Spine
  • 11
  • 1
    Are you sure that the HAVING-condition has a match? Without knowing the data one can not decide your question. – jogo Nov 21 '15 at 10:24
  • You need to give the table structure with some sample data. Best would be to use [sqlfiddle](http://sqlfiddle.com) to recreate the source table so we know what you are saying. – Utsav Nov 21 '15 at 10:25

1 Answers1

0

Try this one. I just place isnull function to check null values.

SELECT A.UNIT, A.PO_NO, A.LINE, A.QUNATITY, 
       SUM(B.RECEIVED_QUNATITY) AS "RECEIVED QUANTITY" 
FROM PO_TBL A, RECEIVER_TBL B 
WHERE A.UNIT = B.UNIT AND A.PO_NO = B.PO_NO AND A.LINE = B.LINE 
GROUP BY A.UNIT, A.PO_NO, A.LINE, A.QUNATITY 
HAVING ((A.QUNATITY - isnull(SUM(B.RECEIVED_QUNATITY),0)) > 0);

Assuming A.Quantity doesn't have null values or else implement isnull over it also.

Bhanu Chandra
  • 408
  • 8
  • 26
  • Appreciate you thinking on a solution and giving the answer, but it is moot to answer a question if OP doesn't give complete information. For example `isnull` is not ANSI standard. It doesn't work in Oracle for sure. You can use `coalesce` or some function like that but we will never know until OP gives more information. – Utsav Nov 21 '15 at 12:47
  • Hi Bhanu.... Thanks for help.... I used the below code to achieve what i needed. HAVING ((A.QUANTITY - (SUM(B.RECEIVED_QUANTITY)) IS NULL) OR (A.QUANTITY - (SUM(B.RECEIVED_QUANTITY)) <> 0)) – Spine Nov 21 '15 at 17:11