Why is SQL not letting me access information in inner queries?-CodePudding

I'm writing a query to find solve the following question:

"For those customer – product combinations where the product belongs to one of the product lines that have ‘Ethernet’ in their name, list the name of the customer, name of the product, the sales last year and total sales year to date."

Now, I have four tables that I need to use to solve this: xproduct, xprodline, and xsales, and xcustomer. These are related in the following ways:

And they have the following columns:

Since xcustomer and xproduct are not directly related, I'm using xsales to join them, but I'm having issues accessing the information I get from inner queries. This is the code I have so far, but it throws "ORA-00904: "S"."SALES_YEAR_TO_DATE": invalid identifier":

SELECT xcustomer.cust_name, PL.prod_name, S.sales_last_year, S.sales_year_to_date FROM xcustomer
JOIN(
    SELECT xsales.sales_cust_nbr FROM xsales 
    JOIN (
        SELECT xproduct.prod_name, xprodline.prodline_pyear_sales, xprodline.prodline_ytd_sales, xproduct.prod_nbr FROM xproduct 
            INNER JOIN xprodline 
            ON xproduct.prod_prodline = xprodline.prodline_nbr
            WHERE prod_prodline= 1
        ) PL
    ON xsales.sales_prod_nbr = PL.prod_nbr
    )S
ON S.sales_cust_nbr = xcustomer.cust_nbr;

CodePudding user response：

Try this:

SELECT xcustomer.cust_name, PL.prod_name, S.sales_last_year, S.sales_year_to_date FROM xcustomer
JOIN(
    SELECT xsales.sales_cust_nbr, xsales.sales_last_year, xsales.sales_year_to_date FROM xsales 
    JOIN (
        SELECT xproduct.prod_name, xprodline.prodline_pyear_sales, xprodline.prodline_ytd_sales, xproduct.prod_nbr FROM xproduct 
            INNER JOIN xprodline 
            ON xproduct.prod_prodline = xprodline.prodline_nbr
            WHERE prod_prodline= 1
        ) PL
    ON xsales.sales_prod_nbr = PL.prod_nbr
    )S
ON S.sales_cust_nbr = xcustomer.cust_nbr;

You were missing selecting the columns you needed.

CodePudding user response：

You are missing one more column (PROD_NAME) in your subquery. Here is the update query:

SELECT xcustomer.cust_name, 
       S.prod_name, 
       S.sales_last_year, 
       S.sales_year_to_date 
FROM xcustomer
JOIN(
    SELECT xsales.sales_cust_nbr,  xsales.sales_last_year, xsales.sales_year_to_date, pl.prod_name
    FROM xsales 
    JOIN (
        SELECT xproduct.prod_name, xprodline.prodline_pyear_sales, xprodline.prodline_ytd_sales, xproduct.prod_nbr 
        FROM xproduct 
        INNER JOIN xprodline 
          ON xproduct.prod_prodline = xprodline.prodline_nbr
        WHERE prod_prodline= 1
        ) PL
    ON xsales.sales_prod_nbr = PL.prod_nbr
    )S
ON S.sales_cust_nbr = xcustomer.cust_nbr;

Also, I think you can directly join the tables and simplify your query as follows:

SELECT C.cust_name, 
       PL.prod_name, 
       S.sales_last_year, 
       S.sales_year_to_date 
FROM xcustomer C
JOIN xsales S 
  ON C.cust_nbr = S.sales_cust_nbr
JOIN xprodline PL 
  ON S.sales_prod_nbr = PL.prod_nbr
JOIN xproduct P
  ON PL.prodline_nbr = P.prod_prodline
WHERE P.prod_prodline= 1