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
(select m.invoice_nbr, sum(m.extension) Surcharge from AXS.PUB.ivc_mchgs m
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,
from AXS.PUB.return_hdr ReturnHeader
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
That's a computed column alias and thus the error. You should consider using the entire expression rather like
on concat(substring(OuterInvoiceHeader.sold_to_cust_nbr, 1, 6) + '-', OuterInvoiceHeader.sold_to_cust_seq) = Returns.ReturnMemberID
on OuterInvoiceHeader.MemberID = Returns.ReturnMemberID. As well, change any other place where you are using the same alias. You can and should use that alias only in a outer query and not in the same query.