>Hello Set, Hello Martijn!
>> WITH MyCTE AS >> (SELECT CAST(:FromMainTable AS VarChar(5)) FromMainTable, Field1 > >Wouldn't this be the value of Field2 then? Sure, but that's not the problem (just a desparate workaround I attempted). >> UNION >> SELECT CAST(:FromMainTable AS VarChar(5)), Field1 >> FROM MySmallLookupTable >> WHERE Field2 = substring(:FromMainTable FROM 1 FOR 4)) ... > >Same here? No, Field2 might e.g. be '1000' whereas :FromMainTable would be '10000'. 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). Set
