|Subject||RE: [firebird-support] CTE with unknown input parameter|
|Author||Svein Erling Tysvær|
>Hello Set,Hello Martijn!
>> WITH MyCTE ASSure, but that's not the problem (just a desparate workaround I attempted).
>> (SELECT CAST(:FromMainTable AS VarChar(5)) FromMainTable, Field1
>Wouldn't this be the value of Field2 then?
>> UNIONNo, Field2 might e.g. be '1000' whereas :FromMainTable would be '10000'.
>> SELECT CAST(:FromMainTable AS VarChar(5)), Field1
>> FROM MySmallLookupTable
>> WHERE Field2 = substring(:FromMainTable FROM 1 FOR 4)) ...
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):
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 = ???)
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).