MySQL: Using alias as a column

Question

I have this query:

select qa_returns_items.item_code, 
               (CASE status_code
                 WHEN 11 THEN (qa_returns_items.item_quantity - qa_returns_residues.item_quantity) 
                 WHEN 12 THEN (qa_returns_items.item_quantity + qa_returns_residues.item_quantity)   END) as total_ecuation , 
                qa_returns_residues.item_unitprice,
               ( qa_returns_residues.item_unitprice * total_ecuation) as item_subtotal,
               (qa_returns_residues.item_discount * item_quantity) as item_discount,
               ( ( qa_returns_residues.item_unitprice * total_ecuation) -   
                 (qa_returns_residues.item_discount * item_quantity) ) as item_total
from qa_returns_residues, qa_returns_items
where
    total_ecuation > 0 
AND qa_returns_items.item_code = qa_returns_residues.item_code;

It shows me the error: Unknown column 'total_ecuation' in 'field list'

how I can use the alias as a column?

Andomar · Accepted Answer · 2012-06-24 18:07:34Z

3

Consider using a subquery. I've added table aliases to make the query more readable:

select  *
,       item_unitprice * total_ecuation as item_subtotal
,       (item_unitprice * total_ecuation) - item_discount as item_total
from    (
        select  ri.item_code
        ,       case status_code
                when 11 then ri.item_quantity - rr.item_quantity 
                when 12 then ri.item_quantity + rr.item_quantity
                end as total_ecuation
        ,       rr.item_unitprice
        ,       rr.item_quantity
        ,       rr.item_discount * rr.item_quantity as item_discount
        from    qa_returns_residues rr
        join    qa_returns_items ri
        on      ri.item_code = rr.item_code
        ) as SubQueryAlias
where   total_ecuation > 0

edited Jun 24, 2012 at 18:07

answered Jun 24, 2012 at 17:48

Andomar

239k55 gold badges387 silver badges412 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Andriy M Over a year ago

You forgot to add that you also replaced the comma join with an explicit join (to make the query more readable, of course). :)

John Nuñez Over a year ago

Corrected syntax errors but I always get the same error. Unknown column 'total_ecuation' in 'field list'

Andomar Over a year ago

Aha, total_equation was used in other columns as well. I edited the answer to move those calculations to the outer query, does that help?

John Nuñez Over a year ago

The syntax is right now, but i get another error: Unknown column 'rr.item_unitprice' in 'field list'

Andomar Over a year ago

In the outer query it should be either SubQueryAlias.item_unitprice or item_unitprice, edited in answer.

|

Sebas · Accepted Answer · 2012-06-24 17:21:00Z

2

I'm afraid you have to rewrite your query to use a named subquery, or repeat the whole case statement in the where clause...

answered Jun 24, 2012 at 17:21

Sebas

21.6k9 gold badges60 silver badges109 bronze badges

Collectives™ on Stack Overflow

MySQL: Using alias as a column

2 Answers 2

6 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related