>This seems to do the SQL job:
>
>With Recursive
>RecurseCGS as
>(
> Select ID from CGS_Structure
> where ID = :ID_RootCGS
> Union all
> Select Child.ID from CGS_Structure Child, RecurseCGS Parent
> where Child.Parent_ID = Parent.id
>)
>Select * from
>(
> select p.ID, p.BIZTYPE_DESCRIPTION, p.CostingType_Description, p.Description,
> p.CGS_Description, p.Date_Closing, >p.TOTAL_REVENUE, p.EXCHANGE_RATE,
> p.CGSSHARE_PCT, p.CGS_REVENUE from V_BD_Biz_Prim p, RecurseCGS
> where ((p.BIZTYPE_ID = :BizType_ID and p.CGS_ID = RecurseCGS.ID and
> p.Date_Closing between :Date_Begin and :Date_End)
> and
> (
> p.TOTAL_REVENUE is null or p.TOTAL_REVENUE = 0
> or p.EXCHANGE_RATE is null or p.EXCHANGE_RATE = 0
> or p.CGSSHARE_PCT is null or p.CGSSHARE_PCT = 0
> ))
> or
> (p.BIZTYPE_ID = :BizType_ID and p.CGS_ID = RecurseCGS.ID and p.Date_Closing
> is null)
>)
>order by CGS_Description, Date_Closing desc, ID
Ouch, is this a one-time job or is it part of a program that can be rewritten
later? My flaky memory cannot remember to ever have seen a mixture of using a
recursive CTE, SQL-89 and SELECT FROM SELECT.
I think the below query should get the same result as your query and that it is
more readable (though, having written the query, I'm very subjective)
with recursive RecurseCGS as
(select ID from CGS_Structure
where ID = :ID_RootCGS
union all
select Child.ID from CGS_Structure Child
join RecurseCGS Parent
on Child.Parent_ID = Parent.id)
select p.ID, p.BIZTYPE_DESCRIPTION, p.CostingType_Description, p.Description,
p.CGS_Description,
p.Date_Closing, p.TOTAL_REVENUE, p.EXCHANGE_RATE, p.CGSSHARE_PCT,
p.CGS_REVENUE
from V_BD_Biz_Prim p
join RecurseCGS r on p.CGS_ID = r.ID
where p.BIZTYPE_ID = :BizType_ID
and (p.Date_Closing is null
or (p.Date_Closing between :Date_Begin and :Date_End
and (coalesce(p.TOTAL_REVENUE, 0) = 0
or coalesce(p.EXCHANGE_RATE, 0) = 0
or coalesce(p.CGSSHARE_PCT, 0) = 0)))
order by p.CGS_Description, p.Date_Closing desc, p.ID
HTH,
Set
