>My real problem/question is that I don't know the value of :FromMainTable
>until the main select attempts:
>
>LEFT JOIN MyCTE CTE ON MT.Afield = CTE.FromMainTable
>
>The simplest way to put my question would then be (though oversimplifying,
>probably leading to answers that doesn't work in the real case):
>
>Can
>
>SELECT ...
>FROM A
>LEFT JOIN B B1 ON A.FIELD1 = B1.FIELD1
>LEFT JOIN B B2 ON A.FIELD2 = B2.FIELD1
>
>in any way be replaced by a construct similar to
>
>WITH TMP_B AS
>(SELECT ... FROM B WHERE FIELD1 = ???)
>SELECT ...
>FROM A
>LEFT JOIN TMP_B B1 ON A.FIELD1 = B1.FIELD1 LEFT JOIN TMP_B B2 ON A.FIELD2 = B2.FIELD1
>
>The problem is of course that the value for ??? will come from the LEFT JOIN of the
>outer select (for one alias it should match A.FIELD1, for another A.FIELD2). I suspect
>I have to put that in the LEFT JOIN itself rather than use the CTE, but that would make
>the real query (with 10 LEFT JOINs, each containing about 10 ORs and one NOT EXISTS and
>the error discovered could require an additional nine NOT EXISTS with up to 45 ORs for
>each LEFT JOIN) grow considerably from its current 166 lines (possibly making the query
>700 lines - I will probably consider modifying it to EXECUTE BLOCK, I dislike writing
>unnecessarily verbose SQL or code).

Fixed my problem very differently, rather than having 10 LEFT JOINs with many ORs and NOT EXISTS, I ended up with 100 LEFT JOINs and 10 occurrences of COALESCE with 11 possible values each. The query seems to perform OK and is easily readable with only 122 lines (including the 100 LEFT JOINs).

Set