CODEWITHSUNDEEP
sqloracle-databasealias
lee1129
lee1129

Reputation: 1

SQL command not properly ended, concerning with table aliases

I understand that oracle does not allow AS commands for table aliases, so I've been suggested a solution like below.

SELECT Temp.avgsale
FROM (SELECT o.received, AVG(p.price*d.qty+d.sfee) avgsale
     FROM orders o, parts p, odetails d
     GROUP BY o.received) Temp
WHERE Temp.avgsale=(SELECT MIN(Temp.avgsale) FROM Temp);

However, I am getting the following error when I run it: ERROR at line 4: ORA-00942: table or view does not exist. I also get similar errors when I try to set the alias at the beginning of the SELECT query: Temp (SELECT ...). Any help would be appreciated, as I'm having trouble as to what is wrong with the query.

Upvotes: 0

Views: 870

Answers (2)

jpw
jpw

Reputation: 44881

The issue is that when the subquery in the where clause is parsed the table alias temp might not have been defined yet. You could use a common table expression instead:

WITH Temp AS (
    SELECT o.received, AVG(p.price*d.qty+d.sfee) avgsale
    FROM orders o, parts p, odetails d
    GROUP BY o.received
    )

SELECT Temp.avgsale
FROM Temp
WHERE Temp.avgsale = (SELECT MIN(Temp.avgsale) FROM Temp);

It looks like the query has another issue though; it's missing joins between the orders, parts and odetails tables which are needed. You probably want something like this:

WITH Temp AS (
    SELECT o.received, AVG(p.price*d.qty+d.sfee) avgsale
    FROM orders o
    JOIN odetails od ON o.orderid = od.orderid
    JOIN parts p ON p.partid = od.partid 
    GROUP BY o.received
    )

SELECT Temp.avgsale
FROM Temp
WHERE Temp.avgsale = (SELECT MIN(Temp.avgsale) FROM Temp);

Note that I'm just guessing what your column names are as you failed to include any information about it in the question.

Also, maybe you wanted the orders matching the min(temp.avgsale) and not just the min()? if so change the last part to:

SELECT Temp.received, Temp.avgsale
FROM Temp
WHERE Temp.avgsale = (SELECT MIN(Temp.avgsale) FROM Temp);

This query would make more sense to me.

Upvotes: 2

David Faber
David Faber

Reputation: 12485

It looks as if you want to get data where the average sale of orders on a given date (received? is that a date?) is the minimum average sale? You could use window (analytic) functions to do this. Please note - I am guessing at your join conditions below, since your original query is using cartesian joins.

SELECT received, avgsale FROM (
    SELECT received, avgsale, RANK() OVER ( ORDER BY avgsale ) AS rn
      FROM (
        SELECT o.received, AVG(p.price * d.qty + d.sfee) AS avgsale
          FROM orders o INNER JOIN odetails d
            ON o.order_id = d.order_id
         INNER JOIN parts p
            ON d.part_id = p.part_id
         GROUP BY o.received
    )
) WHERE rn = 1;

Note that this will retrieve all the values of received, as well as the corresponding average sale, where that average sale is equal to the minimum. If you want only one row, use ROW_NUMBER() in place of RANK().

If you prefer to write this with a CTE then you might do so as follows:

WITH temp AS (
    SELECT o.received, AVG(p.price * d.qty + d.sfee) AS avgsale
      FROM orders o INNER JOIN odetails d
        ON o.order_id = d.order_id
     INNER JOIN parts p
        ON d.part_id = p.part_id
     GROUP BY o.received
)
SELECT received, avgsale FROM (
    SELECT received, avgsale, RANK() OVER ( ORDER BY avgsale ) AS rn
      FROM temp
) WHERE rn = 1;

Upvotes: 0

Related Questions