Results 1 to 3 of 3
  1. #1
    Join Date
    May 2009
    Posts
    12

    Unanswered: How can i use "With" in a function

    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
    Last edited by avishalpatil; 05-15-09 at 13:00.

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Try this:


    Code:
    CREATE FUNCTION PRODUCTION.UDF_My_Function()
    RETURNS integer 
    LANGUAGE SQL NOT DETERMINISTIC NO EXTERNAL ACTION READS SQL DATA CALLED ON NULL INPUT
    RETURN 
    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%'
    Andy

  3. #3
    Join Date
    May 2009
    Posts
    12
    Hey Andy,
    Thanks a lot.Its working.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •