How can i write "With" in a function.Here is the code
I have to write this block in a function.It should return sum(cost).

WITH n(id, name, part_id,cost) as

(

select distinct certi.id, certi.name, ep.PART_ID,COALESCE(pllist.amount,pl.amount) cost

from

tpt_ext_ce_certification certi

inner join tpt_ext_ce_track tr1 on tr1.certification_id = certi.id

inner join tpt_ext_ce_group gr ON gr.track_id = tr1.id

inner join tpt_ce_education_plan ep ON ep.owner_id = gr.id

left outer join scv_offering_action offact on offact.offering_temp_id=ep.part_id and offact.party_id='emplo000000000001000'

left outer join scv_registration tr on offact.id=tr.offering_action_id and tr.student_id='emplo000000000001000' and tr.status<600

left outer join SCV_PL_LIST_ENTRY pllist on tr.class_id=pllist.part_id and pllist.currency_id='crncy000000000000001'

left outer join TPV_PUB_PL_LIST p on p.id=pllist.price_list_id and p.name='Master Price List'

left outer join SCV_PL_LIST_ENTRY pl on ep.part_id=pl.part_id and pl.currency_id='crncy000000000000001'

left outer join TPV_PUB_PL_LIST p1 on p1.id=pl.price_list_id and p1.name='Master Price List'

WHERE

tr1.locale_id = gr.locale_id

AND gr.locale_id = certi.locale_id

AND certi.locale_id = 'local000000000000001'

and substr(tr1.flags,2,1) = '1'

and certi.id ='crtfy000000000200045' --@certname

union all

SELECT nplus1.id, nplus1.name,nplus1.part_id,nplus1.cost

from

( select distinct certi.id, certi.name, ep.PART_ID,COALESCE(pllist.amount,pl.amount) cost

from

tpt_ext_ce_certification certi

inner join tpt_ext_ce_track tr1 on tr1.certification_id = certi.id

inner join tpt_ext_ce_group gr ON gr.track_id = tr1.id

inner join tpt_ce_education_plan ep ON ep.owner_id = gr.id

left outer join scv_offering_action offact on offact.offering_temp_id=ep.part_id and offact.party_id='emplo000000000001000'

left outer join scv_registration tr on offact.id=tr.offering_action_id and tr.student_id='emplo000000000001000' and tr.status<600

left outer join SCV_PL_LIST_ENTRY pllist on tr.class_id=pllist.part_id and pllist.currency_id='crncy000000000000001'

left outer join TPV_PUB_PL_LIST p on p.id=pllist.price_list_id and p.name='Master Price List'

left outer join SCV_PL_LIST_ENTRY pl on ep.part_id=pl.part_id and pl.currency_id='crncy000000000000001'

left outer join TPV_PUB_PL_LIST p1 on p1.id=pl.price_list_id and p1.name='Master Price List'

WHERE

tr1.locale_id = gr.locale_id

AND gr.locale_id = certi.locale_id

AND certi.locale_id = 'local000000000000001'

and substr(tr1.flags,2,1) = '1' ) as nplus1, n

WHERE n.part_id = nplus1.id and nplus1.id like 'crtfy%'

)

SELECT sum(cost) FROM n where part_id like 'cours%'


Regards
Vishal

Try this:


Andy

Hey Andy,
Thanks a lot.Its working.