Simplifying SELECTed sub-queries

[gin]

Observe the query below, not inserted as code so you can see the colours. The two lines in red are identical.

=======================

SELECT

IF (

qtype<>2, package, package*(

SELECT usd_conv FROM quotes_default def WHERE def.date <= DATE(modified) ORDER BY def.date DESC LIMIT 1

)

) as ptotal,

IF (

qtype<>2, gpackage, gpackage*(

SELECT usd_conv FROM quotes_default def WHERE def.date <= DATE(modified) ORDER BY def.date DESC LIMIT 1

)

) as gptotal

FROM jobs j

=======================

 

Basically, I'm wondering if there's a way simplify this. Something like below, which I know doesn't work, but you get the idea? To put the results of the red sub-query into a variable, then use the variable elsewhere.

=======================

SELECT

(SELECT usd_conv FROM quotes_default def WHERE def.date <= DATE(modified) ORDER BY def.date DESC LIMIT 1) AS rate,

IF (

qtype<>2, package, package*rate

) as ptotal,

IF (

qtype<>2, gpackage, gpackage*rate

) as gptotal

FROM jobs j

=======================

[fenway]

Technically, there's a way, but it's buggy, so don't.

 

If you don't want it repeat, do the math afterwards, not in the SQL statement.

[gin]

Thank you, good to know!

 

One more question. Which would be less strain on the server: my first query above, or doing the calculations after?

[fenway]

That depends on the optimizer for your version of mysql.