RE: [firebird-support] Re: Merge results of recursive CTE with results of non-recursive CTE

[Nols Smit]

Hi,
> Not sure i understand you correctly, but... did you tried to create query 
> with more that one CTE ? :)

My Recursive CTE:
=================

With Recursive lc as
(
Select ID from BD_Locality where ID = :ID_Root
Union all
Select ID from BD_Locality, lc where Parent_ID = lc.id
)
select BizType_Description, Loc_Description from v_bd_biz_prim where BizType_ID 
= :BizType_ID
and (Loc_ID = :ID_root or Parent_Loc_ID = :ID_root) and Loc_ID in
(Select ID from lc)

And my non-recursive CTE:
===========================
with RPL (BizType_Description, Loc_Description) as
(
Select BizType_Description, Loc_Description from V_BD_Biz_Prim
where ID > 0
 and BizType_ID = :BizType_ID

Union all

Select BizType_Description, Loc_Description from V_BD_Biz_Sec_OtherUnits
where ID > 0
 and BizType_ID = :BizType_ID
)
Select BizType_Description, Loc_Description, Count(*) as Nr_Records from RPL
Group by BizType_Description, Loc_Description


Now I wondered if it's possible to have one block of executable SQL?


Regards,

Nols Smit


[Non-text portions of this message have been removed]