it's a bad new that i have no way :(

when i try the CTE, the prepare seam to grow ("seam" i just do now some tests). also CTE are a little hard to use in my situation ...

> Having said that, a year or so ago, Ann Harrison wrote that repeating a subquery doesn't add much to the execution speed, so:
>
> SELECT T1.PK, (SELECT T2.F1 FROM T2 WHERE T1.PK = T2.PK) AS A1,
> CASE WHEN (SELECT T2.F1 FROM T2 WHERE T1.PK = T2.PK) = T1.PK THEN 1 else 0 end
> FROM T1
>
> might not take much more time than when only using the subquery once (though test it, your question is probably different to the one she responded to).

i have just done some test now :

select
(select xxx from yyy where yyy.ID=MyTable.ID) as field1
from Mytable

average time taken by select : 10ms

select
(select xxx from yyy where yyy.ID=MyTable.ID) as field1,
(select xxx from yyy where yyy.ID=MyTable.ID) as field2,
(select xxx from yyy where yyy.ID=MyTable.ID) as field3,
(select xxx from yyy where yyy.ID=MyTable.ID) as field4,
(select xxx from yyy where yyy.ID=MyTable.ID) as field5,
(select xxx from yyy where yyy.ID=MyTable.ID) as field6
from Mytable

average time taken by select : 30ms (3x more slower)

select
(select xxx from yyy where yyy.ID=MyTable.ID) as field1,
'aaa' as field2,
'bbb' as field3,
'ccc' as field4,
'ddd' as field5,
'eee' as field6
from Mytable

average time taken by select : 12ms

so it's mean than repeating the same subquery add a little to the execution ...