Join Alias and Where in SQL

Question

I am trying to run a query where i can use an alias in where clause

For example:

Select V.Fees - Coalesce(V.Payment,0) - Coalesce (V.Adjustment,0) AS Balance
[
the inner join query for the tables
]
Where A.Order not Null and Balance = 0
Order by Name

but as usual it is not able to recognize Balance under where?

How can we fix this?

Use a subquery or repeat the expression or in MySQL use a having clause. — Gordon Linoff, Sep 26 '14 at 15:15
Or just repeat the operation in the where : `Where A.Order not Null and V.Fees - Coalesce(V.Payment,0) - Coalesce (V.Adjustment,0) = 0` — xlecoustillier, Sep 26 '14 at 15:17
having is probably the best bet : http://stackoverflow.com/questions/2905292/where-vs-having — DarkMukke, Sep 26 '14 at 15:17

score 0 · Answer 1 · answered Sep 26 '14 at 15:17

0

The only way to do what you're asking is to put that field in a CTE and reference it through a join of the CTE.

answered Sep 26 '14 at 15:17

Mr. Mascaro

2,693
1
11
18

score 0 · Answer 2 · answered Sep 26 '14 at 15:19

0

you can repeat the expression in the where clause

Select V.Fees - Coalesce(V.Payment,0) - Coalesce (V.Adjustment,0) AS Balance
[
the inner join query for the tables
]
Where A.Order not Null and (V.Fees - Coalesce(V.Payment,0) - Coalesce (V.Adjustment,0)) = 0
Order by Name

answered Sep 26 '14 at 15:19

radar

13,270
2
25
33

@soumyasubhraKonar, you can accept the answer if it works for you – radar Sep 26 '14 at 16:11

Join Alias and Where in SQL

2 Answers2