Hi, I hope someone here can help

[ensorg]

Hi,

I hope someone here can help me out.I'm trying to learn SQL and am having a few problems with the Group By statement. I can get it to work with only one table and no joins but when I try and bring other tables into the query with where statements I hit problems. I have included my code below.

Thanks All!

SELECT PO_HEADER.ORDERNO, PO_HEADER.SUPPREF, PO_DETAIL.NON_STK_RF, PO_DETAIL.PART, Sum(PO_DETAIL.QTY_ORD), Sum(PO_DETAIL.QTY_REC), QTY_ORD-QTY_REC, PO_HEADER.SUPPLIER, SUPPLIER.SUP_NAME, PART.DESCR, PO_DETAIL.PUR_PRICE, PO_DETAIL.DUE
FROM PUBLIC.PART PART, PUBLIC.PO_DETAIL PO_DETAIL, PUBLIC.PO_HEADER PO_HEADER, PUBLIC.SUPPLIER SUPPLIER
WHERE PO_HEADER.ORDERNO = PO_DETAIL.ORDERNO AND PO_HEADER.SUPPLIER = SUPPLIER.SUP_NO AND PO_DETAIL.PART = PART.PART_NO AND ((PO_HEADER.SUPPREF Not Like 'E%') AND (PO_DETAIL.NON_STK_RF Is Null) AND (QTY_ORD-QTY_REC>0))
GROUP BY PO_HEADER.ORDERNO, PO_HEADER.SUPPREF, PO_DETAIL.NON_STK_RF, PO_DETAIL.PART, PO_HEADER.SUPPLIER, SUPPLIER.SUP_NAME, PART.DESCR, PO_DETAIL.PUR_PRICE, PO_DETAIL.DUE

 

[MadhanaGopal]

Add QTY_ORD-QTY_REC field in Group By Clause.
Hope now it will run.Try the following query.

SELECT PO_HEADER.ORDERNO, PO_HEADER.SUPPREF, PO_DETAIL.NON_STK_RF, PO_DETAIL.PART, Sum(PO_DETAIL.QTY_ORD), Sum(PO_DETAIL.QTY_REC), QTY_ORD-QTY_REC, PO_HEADER.SUPPLIER, SUPPLIER.SUP_NAME, PART.DESCR, PO_DETAIL.PUR_PRICE, PO_DETAIL.DUE
FROM PUBLIC.PART PART, PUBLIC.PO_DETAIL PO_DETAIL, PUBLIC.PO_HEADER PO_HEADER, PUBLIC.SUPPLIER SUPPLIER
WHERE PO_HEADER.ORDERNO = PO_DETAIL.ORDERNO AND PO_HEADER.SUPPLIER = SUPPLIER.SUP_NO AND PO_DETAIL.PART = PART.PART_NO AND ((PO_HEADER.SUPPREF Not Like 'E%') AND (PO_DETAIL.NON_STK_RF Is Null) AND (QTY_ORD-QTY_REC>0))
GROUP BY PO_HEADER.ORDERNO, PO_HEADER.SUPPREF, PO_DETAIL.NON_STK_RF, PO_DETAIL.PART,QTY_ORD-QTY_REC, PO_HEADER.SUPPLIER, SUPPLIER.SUP_NAME, PART.DESCR, PO_DETAIL.PUR_PRICE, PO_DETAIL.DUE P.Madhana Gopal,
Principal Software Engineer,
LCube Innovative Solns. Pvt. Ltd.,
India.