Hi, I'm trying to use a CTE a bit differently from what I usually do, but am failing.
WITH MyCTE AS (SELECT CAST(:FromMainTable AS VarChar(5)) FromMainTable, Field1 FROM MySmallLookupTable WHERE Field2 = :FromMainTable UNION SELECT CAST(:FromMainTable AS VarChar(5)), Field1 FROM MySmallLookupTable WHERE Field2 = substring(:FromMainTable FROM 1 FOR 4)) ... SELECT ... FROM MyMainAndLargerTable MT LEFT JOIN MyCTE CTE ON MT.Afield = CTE.FromMainTable LEFT JOIN MyCTE CTE2 ON MT.Anotherfield = CTE2.FromMainTable ... The problem with this is that upon prepare, DB Workbench (naturally?) wants me to supply a value for the parameter :FromMainTable, a value that will vary from row to row. Of course, I can easily get a single value using SELECT ... FROM MyCTE CTE WHERE Field2 = :FromMainTable but I'd prefer the entire result set to be returned within one query. (The actual query was 166 lines in Fb 1.5 and when I noticed I had to fix it (which would make it grow), I thought that I could take advantage of CTEs in Fb 2.5 to prevent it from increasing to 500 lines). Should it at all be possible to use CTEs in a way similar to this or would I have to either let my original query grow or use EXECUTE BLOCK? Set [Non-text portions of this message have been removed]
