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]