0

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.

Improve this question
3
  • you might want to check AXS.PUB.ivc_header to see if you actually have the column MemberID. Commented Aug 24, 2016 at 19:32
  • I do have the memberid but that is not the complete id for the company, I have to concatenate the sold_to_cust_seq; each sequence identifies the subsidiary. Commented Aug 24, 2016 at 19:38
  • have a look at my answer that should explain the reason. Commented Aug 24, 2016 at 19:40

2 Answers 2

Reset to default
1

try this:

I replaced the references to the alias MemberID to be the actual concatinated columns CONCAT(SUBSTRING(OuterInvoiceHeader.sold_to_cust_nbr, 1, 6)+'-', OuterInvoiceHeader.sold_to_cust_seq)

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 CONCAT(SUBSTRING(OuterInvoiceHeader.sold_to_cust_nbr, 1, 6)+'-', OuterInvoiceHeader.sold_to_cust_seq) = 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 CONCAT(SUBSTRING(OuterInvoiceHeader.sold_to_cust_nbr, 1, 6)+'-', OuterInvoiceHeader.sold_to_cust_seq);

Basically you need to keep in mind the order which SQL statements are executed:

  1. FROM clause
  2. WHERE clause
  3. GROUP BY clause
  4. HAVING clause
  5. SELECT clause
  6. ORDER BY clause
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

1

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

Instead of 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.

Improve this answer

3 Comments

Both answers are correct, this one came first, that's why I'm marking it as the correct one. Thanks Rahul and Kamran for the speedy responses, they really helped.
Hi Colin actually I answered first but that ok :P
Sorry Kamran, I'm relatively new to SO and still figuring out how it all works. Now that I look back your answer was posted first, I don't know how I missed it.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.