Error: invalid reference to FROM-clause entry for table-CodePudding

saleco_dw=> SELECT s.cus_code AS Customer_Code, r.reg_name AS Region_Name, (s.sale_price*s.sale_units) AS Total_Sales FROM dwdaysalesfact s, dwregion r LEFT JOIN dwcustomer c ON c.cus_code = s.cus_code ORDER BY r.reg_name, c.cus_code;

ERROR: invalid reference to FROM-clause entry for table "s"

LINE 4: ON c.cus_code = s.cus_code ^

HINT: There is an entry for table "s", but it cannot be referenced from this part of the query.

CodePudding user response：

The ON is just telling how to join r to c, so references to s there are not allowed. I can't tell you how to fix it, as I don't know what you actually want to happen.

But combining comma joins and explicit joins in the same FROM is confusing.

CodePudding user response：

It looks like you may be missing a join or where clause to include the dwregion table you are aliasing in the FROM statement.

SELECT 
    s.cus_code AS Customer_Code, 
    r.reg_name AS Region_Name, 
    (s.sale_price*s.sale_units) AS Total_Sales 
FROM dwdaysalesfact s, dwregion r  -- how are you intending to use r?
LEFT JOIN dwcustomer c ON c.cus_code = s.cus_code 
ORDER BY r.reg_name, c.cus_code;