Can you reference a derived query field from another query field

quote:

---

Originally posted by: advancemal
yes i know its hard to explain what i mean but here goes.
if i select a query derived field say "select cost+2 as newfield", it seems that i cnt reference this new field again withoiut typing in the formula again. eg i cant say select cost+2 as newfield, newfield+22 as secondnewfield.

---

Depends on the db. Apparently yours does not have that capability.

Awesome, glad I could help.

yes great thanks, the nested select should work

I'm not entirely sure I follow you, but here goes.

You can't reference a derived field in a select statement in the same select statement like so:

SELECT field1 + 2 AS field2, field2 - field3 AS field4 ...

That won't work. The only place you can actually use those field2 and field4 aliases is in the ORDER BY clause.

However, depending on what you need to do, you - like you said - can either replicate the formula in the other calculation, or use nested SELECT statements. For instance you could do this:

SELECT (f.field2 - f.field3) AS field4
FROM (
SELECT field1 + 2 AS field2, field 3 FROM myTable) f

It really depends on what you're trying to accomplish though. Hope that helps.