>> Select
>> Field1,
>> (select ...) AS MyFIELD2
>> IIF(MyField is null, select ...., Null) As MYField3 From
>> ....
>>
>> it's not work, it's say unknow "MyField"
>> but if i replace MyFIELD2 by Field1 it's work ...
>>
>> is their any workaround to make this SQL work ?

>I don't see a MyField in that query. Also: you can't reference fields by an alias that is defined
>in the same (sub)query. If you want to do that you will need to wrap the first query in another query.
>In that query you will be able the reference the field by alias.

To elaborate on Marks reply:

This is not possible since A1 is only yet available when CASE is performed:

SELECT T1.PK, (SELECT T2.F1 FROM T2 WHERE T1.PK = T2.PK) AS A1,
CASE WHEN A1 = T1.PK THEN 1 else 0 end
FROM T1

This is possible (well, in recent versions of Firebird, not in Firebird 1.5) and powerful:

WITH MyCTE(PK, A1) AS
(SELECT PK, F1 FROM T2)

SELECT T1.PK, MyCTE.A1,
CASE WHEN MyCTE.A1 = T1.PK THEN 1 else 0 end
FROM T1
LEFT JOIN MyCTE ON T1.PK = MyCTE.PK

Simple scenarios could simply be solved with only a LEFT JOIN, I added the CTE since it is likely there's more to it than:

SELECT T1.PK, T2.F1 AS A1,
CASE WHEN T2.F1 = T1.PK THEN 1 else 0 end
FROM T1
LEFT JOIN T2 ON T1.PK = T2.PK

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).

HTH,
Set