0

I am having an issue saying that "total amount" is an invalid identifier on my where clause. I want to use the "total amount" and compare it to another number that is determine in the insert code here section but cannot figure out how to properly set up the first part of the where.

When naming a column like I did do you not use the name when wanting to use that column in a where?

 SELECT desc, TO_CHAR(SUM(price * quantity), '99999.00') AS "total amount"
 FROM items i
 JOIN line_items li
   ON i.item_id = li.item_id
 WHERE "total amount" > 
   (*insert code here*);
Azmir
  • 9
  • 2

2 Answers2

1

First, you want a having clause, not a where cause.

Second, your query is missing a group by.

Third, you cannot use a column alias in where or having in Oracle. Instead, you need to repeat the expression, use a subquery, or use a CTE.

For this example, I would recommend:

SELECT i.desc,
       TO_CHAR(SUM(i.price * li.quantity), '99999.00') AS "total amount"
FROM items i JOIN
     line_items li
     ON i.item_id = li.item_id
GROUP BY i.desc
HAVING SUM(i.price * li.quantity) > ?;

Notes:

  • The ? is for whatever the comparison value or expression is.
  • Your query is missing a GROUP BY.
  • desc is a really bad name for a column, because it is a SQL keyword (think ORDER BY).
  • Get in the good habit of qualifying all column references in your query to identify the table they come from. I have made educated guesses for this query.
  • You wouldn't want to do a comparison on "total amount" anyway, because it is a string and you (no doubt) want a comparison of numbers (so 10 > 9 but '10' < '9'.
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
0

Derived value should always come in brackets.

SELECT * FROM 
(
    SELECT desc, TO_CHAR(SUM(price * quantity), '99999.00') AS "total amount"
    FROM items i
    JOIN line_items li ON i.item_id = li.item_id
) AMT
WHERE "total amount" > '' /* YOUR VALUE HERE */
Arulkumar
  • 12,966
  • 14
  • 47
  • 68
saravanatn
  • 630
  • 5
  • 9