April 27, 2011 at 5:25 am
Hi,
I have to insert records from my source sql to tmp table.
below is my source sql
with
actuals as (
select fcm_credit_org_key as credit_org_key,
FLOOR(fcm_month_dt_key/100) as fsc_year_nbr,
SUM(fcm_actual_perm_fund_amt) as fca_actual_perm_fund_amt,
SUM(fcm_actual_annual_fund_amt) as fca_actual_annual_fund_amt,
SUM(fcm_actual_polio_plus_amt) as fca_actual_polio_plus_amt,
SUM(fcm_actual_other_funds_amt) as fca_actual_other_funds_amt,
SUM(fcm_actual_misc_amt) as fca_actual_misc_amt,
0 as fca_rec_ins_aud_key
from f_contrib_month_agg
group by fcm_credit_org_key,
FLOOR(fcm_month_dt_key/100)
),
goals as (
select
isnull(org_key,-1) as credit_org_key,
cgl_fsc_year_nbr as fsc_year_nbr,
cgl_goal_apf_share_amt as fca_goal_annual_fund_amt,
cgl_goal_apf_polio_amt as fca_goal_polio_plus_amt,
cgl_goal_avg_per_mbr_amt as fca_goal_avg_per_mbr_amt,
cgl_goal_new_benefactors_cnt as fca_goal_new_benefactors_cnt,
cgl_goal_new_gifts_cnt as fca_goal_new_gifts_cnt,
cgl_goal_mbr_cnt as fca_goal_mbr_cnt,
cgl_goal_bequest_mbr_cnt as fca_goal_bequest_mbr_cnt
from t_club_goal t
left join m_dt d
on cgl_fsc_year_nbr = dt_fsc_year_nbr
and dt_day_of_month_nbr = 1
and dt_month_short_name = 'JUL'
left join m_org o
on cgl_credit_org_id = org_id
and org_level_name = CASE cgl_credit_org_type_cd WHEN 3 THEN 'Club' WHEN 7 THEN 'District' WHEN 8 THEN 'Zone' WHEN 9 THEN 'Rotary Office' ELSE 'N/A' END
and dt_cal_dt between org_rec_eff_dt and org_rec_end_dt
)
select
isnull(g.credit_org_key,a.credit_org_key) as fca_credit_org_key,
isnull(g.fsc_year_nbr,a.fsc_year_nbr) as fca_year_dt_key,
isnull(fca_goal_annual_fund_amt,0) as fca_goal_annual_fund_amt,
isnull(fca_goal_polio_plus_amt,0) as fca_goal_polio_plus_amt,
isnull(fca_goal_avg_per_mbr_amt,0) as fca_goal_avg_per_mbr_amt,
isnull(fca_goal_new_benefactors_cnt,0) as fca_goal_new_benefactors_cnt,
isnull(fca_goal_new_gifts_cnt,0) as fca_goal_new_gifts_cnt,
isnull(fca_goal_mbr_cnt,0) as fca_goal_mbr_cnt,
isnull(fca_goal_bequest_mbr_cnt,0) as fca_goal_bequest_mbr_cnt,
isnull(fca_actual_perm_fund_amt,0) as fca_actual_perm_fund_amt,
isnull(fca_actual_annual_fund_amt,0) as fca_actual_annual_fund_amt,
isnull(fca_actual_polio_plus_amt,0) as fca_actual_polio_plus_amt,
isnull(fca_actual_other_funds_amt,0) as fca_actual_other_funds_amt,
isnull(fca_actual_misc_amt,0) as fca_actual_misc_amt,
a.*
from actuals a
full outer join goals g
on g.credit_org_key = a.credit_org_key
and g.fsc_year_nbr = a.fsc_year_nbr
and this is my temp table name
tmp_f_contrib_annual_agg
so request you to please suggest only how to write the code to insert records in a tmp table.
Regards,
Kiran
April 27, 2011 at 5:47 am
with actuals as (...),
goals as (...)
INSERT INTO tmp_f_contrib_annual_agg (
--put column list in here
)
select
...
from actuals a
full outer join goals g
on g.credit_org_key = a.credit_org_key
and g.fsc_year_nbr = a.fsc_year_nbr
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
April 27, 2011 at 6:50 am
Thanks it work
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply