Progress SQL Column cannot be found or is not specified for query

I'm writing SQL against a Progress 10.2B07 database and am getting the following error "Column 'OUTERINVOICEHEADER.MEMBERID' cannot be found or is not specified for query (13865).

Here is the query:

select concat(substring(OuterInvoiceHeader.sold_to_cust_nbr, 1, 6) + '-', OuterInvoiceHeader.sold_to_cust_seq) as MemberID,
sum(OuterInvoiceHeader.net_weight) as TotalInvoicePounds,
sum(OuterInvoiceHeader.net_weight / 2000) as TotalTons,
sum(OuterInvoiceHeader.invoice_amt) as InvoiceAmount,
sum(InvoiceSurcharges.Surcharge) as Surcharges,
sum(OuterInvoiceHeader.invoice_amt - InvoiceSurcharges.Surcharge) as Total,
sum(Returns.qty_received) as PoundsReturned
from AXS.PUB.ivc_header OuterInvoiceHeader

inner join
(select m.invoice_nbr, sum(m.extension) Surcharge from AXS.PUB.ivc_mchgs m
inner join
AXS.PUB.ivc_header h
on h.invoice_nbr = m.invoice_nbr
group by m.invoice_nbr) InvoiceSurcharges
on OuterInvoiceHeader.invoice_nbr = InvoiceSurcharges.invoice_nbr

left outer join
(select concat(substring(ReturnHeader.ship_to_nbr, 1, 6)+'-',InnerInvoiceHeader.sold_to_cust_seq) as ReturnMemberID,
ReturnHeader.invoice_nbr as ReturnInvoiceNum,
qty_received
from AXS.PUB.return_hdr ReturnHeader
inner join
AXS.PUB.ivc_header InnerInvoiceHeader
on ReturnHeader.invoice_nbr = InnerInvoiceHeader.invoice_nbr
inner join AXS.PUB.return_line ReturnLine
on ReturnHeader.claim_nbr = ReturnLine.claim_nbr
where ReturnInvoiceNum = '0001010914'
group by ReturnMemberID, ReturnInvoiceNum, qty_received) Returns
on OuterInvoiceHeader.MemberID = Returns.ReturnMemberID
--on OuterInvoiceHeader.invoice_nbr = Returns.ReturnInvoiceNum

where OuterInvoiceHeader.sold_to_cust_nbr = '000837' and OuterInvoiceHeader.invoice_date between '06/01/2016' and '06/30/2016' and OuterInvoiceHeader.invoice_status = '5804' and OuterInvoiceHeader.invoice_type='5601'
group by MemberID


The problem is in the left join; the commented out on clause "on OuterInvoiceHeader.invoice_nbr = Returns.ReturnInvoiceNum" will work if uncommented. The "on OuterInvoiceHeader.MemberID = Returns.ReturnMemberID" clause gives me the error.

What I don't understand is that both of these reference a column in the top SELECT statement, the only difference is that one is a concatenation and the other is not.

I hope that I just can't see the forest for the trees here and the answer is simple, so if anyone has any suggestions or questions I'm all ears.

Continue reading...