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!