0

I'm still new to SQL, so I apologize that this is probably a dumb question, This basic bit of code is causing an invalid identifier error

SELECT price * qty_on_hand AS dollar_value
FROM products
WHERE dollar_value >= 20000
ORDER BY dollar_value DESC;

I am trying to select the multiplication of two columns, where there value of greater than or equal to 20,000 and then sort them in descending order. the error message is

Error at Command Line : 3 Column : 7
Error report -
SQL Error: ORA-00904: "DOLLAR_VALUE": invalid identifier
00904. 00000 -  "%s: invalid identifier"
*Cause:    
*Action:

What is wrong with my SQL? How do i fix this?

Justin
  • 37
  • 1
  • 1
  • 7
  • 4
    The structure of a SQL `select` statement is misleading. The `where` clause is processed **before** the `select` clause. Since the identifier (name) `dollar_value` is defined in the `select` clause, it is not visible to the `where` clause. To fix this, either define `dollar_value` in a subquery, then apply the `where` clause in an outer query, or rewrite the `where` clause to read `where price * qty_on_hand >= 20000`. Don't worry about computing the same expression twice; the optimizer will recognize the same thing you do, and it will only compute it once and use the result in both places. –  Apr 02 '22 at 20:22
  • 1
    Please pay attention to the tags under your post. The website adds the tags for you, but it is pretty clueless. Either when you post, or after the fact (through Edit tags), you can control what tags appear under your post. I removed the `mysql` tag since it clearly doesn't apply to your question. –  Apr 02 '22 at 20:24

0 Answers0