>>If I select from COMBINED, the sql runs but as below I get an error. This is pretty annoying when constructing a large SQL with many >>CTEs and you want to test each one and combinations of them. I can get why the error is there, but any other suggestion? >> >>with >>CTE1 as (select 1 as ID from rdb$database), >>CTE2 as (select 2 as ID from rdb$database), >>COMBINED as >>(select * from CTE1 >> union >> select * from CTE2), >>select * from CTE1
>This is even more annoying when the "large SQL with many CTEs" is generated from a large complex piece of code, >and you've got to make sure that the large complex piece of code doesn't generate a CTE that doesn't happen to be used this time. Unlike Louis, I don't understand why this results in an error, I'd say Firebird is too picky in this case. You can circumvent such errors by simply adding CTEDummy as below, if you include all CTEs in CTEDummy, you should not get the annoying error. However, I don't know how this would affect performance - it worked perfectly against rdb$database in the below example ;o). with CTE1 as (select 1 as ID from rdb$database), CTE2 as (select 2 as ID from rdb$database), COMBINED as (select * from CTE1 union select * from CTE2), CTEDummy(Dummy) as (select null from CTE1 join CTE2 on 1=2 join COMBINED on 1=2) select CTE1.* from CTE1 left join CTEDummy on 1=2 HTH, Set
