Hello Set, > 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
Wouldn't this be the value of Field2 then? > FROM MySmallLookupTable > WHERE Field2 = :FromMainTable > UNION > SELECT CAST(:FromMainTable AS VarChar(5)), Field1 Same here? > 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? With regards, Martijn Tonies Upscene Productions http://www.upscene.com Download Database Workbench for Oracle, MS SQL Server, Sybase SQL Anywhere, MySQL, InterBase, NexusDB and Firebird!
