Home » SQL & PL/SQL » SQL & PL/SQL » Need to put results of SQL into a table (Oracle SQL )
| Need to put results of SQL into a table [message #608595] |
Fri, 21 February 2014 04:41  |
 |
skbytes
Messages: 21
Registered: February 2014
Location: Chester
|
Junior Member |
|
|
Hi Guys,
I've just got a piece of code from an SQL server guy but it runs against Oracle SQL because i've not really used the WITH statement in SQL before and I need to catch the results into a table which I will create.
Where would I create the insert into table statement in the below query. I'm think near the bottom.
-- summary report
with run_date as
( select MAX(wcal_primary_date) AS rd_date
from work_calendars
where wcal_primary_date < ( SELECT soc_wcal_primary_date FROM societies )
and wcal_hol_type IS NULL
)
,pe_data as
(
select acc_account_no,
acc_subacc_no,
acc_balance_os,
pacx_product_code,
rd_date
from eod_accounts
inner join account_types
on act_code = acc_act_code
inner join product_act_code_xref
on pacx_act_code = acc_act_code
cross join run_date
where acc_process_status = 2
and acc_gact_type in ('C','U','T','S')
and decode(nvl(act_overdraft_ind,'N'),
'N',0,
decode(sign(nvl(acc_balance_os,0)),
-1,1,
0)
) = 0
),
acc_custs_balance as
(select acc_account_no acb_account_no,
acc_subacc_no acb_subacc_no,
pacx_product_code AS acb_product_code,
rd_date,
nvl((select count(distinct case when scvmcr_master_cust_seqno is null then accl_cust_seqno
else scvmcr_master_cust_seqno
end)
from account_links
inner join run_date
on trunc(accl_create_date) <= rd_date
left outer join scv_master_cust_relationships
on (scvmcr_master_cust_seqno = accl_cust_seqno
or
scvmcr_sub_cust_seqno = accl_cust_seqno)
where accl_account_no = acc_account_no
and accl_subacc_no = acc_subacc_no
and nvl(accl_end_date,rd_date) >= rd_date
and accl_cust_usage not in ('AB',
'TR',
'FB',
'SI',
'BA',
'PA',
'CO',
'BE',
'VB',
'VL',
'PR',
'OW',
'NM',
'RV',
'EX',
'AT',
'RB')
),1) acb_customers,
nvl(acc_balance_os,0) acb_balance_os,
case when acc_balance_os >= 0 then acc_balance_os else 0 end AS acb_balance_os_posonly
from pe_data
),
acc_customers AS
(select acc_account_no AS ac_account_no,
acc_subacc_no AS ac_subacc_no,
pacx_product_code AS ac_product_code,
nvl(accl_cust_seqno,acc_account_no) ac_cust_seqno
from pe_data
left outer join (select accl_account_no,
accl_subacc_no,
case when scvmcr_master_cust_seqno is null then accl_cust_seqno
else scvmcr_master_cust_seqno
end accl_cust_seqno
from account_links
inner join run_date
on trunc(accl_create_date) <= rd_date
left outer join scv_master_cust_relationships
on (scvmcr_master_cust_seqno = accl_cust_seqno
or
scvmcr_sub_cust_seqno = accl_cust_seqno)
where nvl(accl_end_date,rd_date) >= rd_date
and accl_cust_usage not in ('AB',
'TR',
'FB',
'SI',
'BA',
'PA',
'CO',
'BE',
'VB',
'VL',
'PR',
'OW',
'NM',
'RV',
'EX',
'AT',
'RB')
)
on accl_account_no = acc_account_no
and accl_subacc_no = acc_subacc_no
group by acc_account_no,
acc_subacc_no,
pacx_product_code,
nvl(accl_cust_seqno,acc_account_no)
),
customer_balances as
(select ac_cust_seqno AS cb_cust_seqno,
min(rd_date) AS rd_date,
sum(acb_balance_os/case when acb_customers = 0 then 1
else acb_customers
end) cb_cust_balance,
sum(acb_balance_os_posonly / case when acb_customers = 0 then 1
else acb_customers
end) cb_cust_balance_posonly
from acc_custs_balance
inner join acc_customers
on ac_account_no = acb_account_no
and ac_subacc_no = acb_subacc_no
group by ac_cust_seqno
),
customer_balances_by_product as
(select ac_cust_seqno AS cbp_cust_seqno,
acb_product_code AS cbp_product_code,
sum(acb_balance_os/case when acb_customers = 0 then 1
else acb_customers
end) cbp_cust_balance
from acc_custs_balance
inner join acc_customers
on ac_account_no = acb_account_no
and ac_subacc_no = acb_subacc_no
group by ac_cust_seqno, acb_product_code
)
,cte_prod_hier
AS
( SELECT hddhd_name, hddhd_long_name, hddhd_parent
FROM reporting_hierarchy
where hddhd_hier_seqno = 67
)
,cte_prod_hier2
AS
( SELECT hddhd_name, hddhd_long_name, hddhd_parent, level AS prod_level, CONNECT_BY_ROOT hddhd_name AS root
FROM cte_prod_hier
WHERE LEVEL <= 3
CONNECT BY HDDHD_NAME = PRIOR HDDHD_PARENT
)
,cte_prod_hierarchy
AS
( SELECT l1.ROOT AS product_code
,'[' || l1.hddhd_name || + '] - ' || l1.hddhd_long_name AS product
,'[' || l2.hddhd_name || + '] - ' || l2.hddhd_long_name AS product_class
,'[' || l3.hddhd_name || + '] - ' || l3.hddhd_long_name AS Product_Group
FROM cte_prod_hier2 l1
LEFT JOIN cte_prod_hier2 l2
ON l2.root = l1.root
AND l2.prod_level = 2
LEFT JOIN cte_prod_hier2 l3
ON l3.root = l1.root
AND l3.prod_level = 3
WHERE l1.prod_level = 1
)
,cte_bands
AS
( SELECT rd_date
,cbp_product_code
,h.product
,h.product_class
,h.product_group
,cb_cust_seqno
,cb_cust_balance
,cbp_cust_balance
/* Proportion of a customer counted in each product. Only positive customer product balances are counted, otherwise
get some anomalous results e.g. cust 11917398 had 2 accts, 1 at -49p and another at 50p, total bal = 1p
Issue 12: Also set cust count = 0 if overall cust balance <= 0 */
,CASE WHEN cb_cust_balance > 0 AND cbp_cust_balance > 0 THEN cbp_cust_balance / cb_cust_balance_posonly
ELSE 0
END AS cust_prod_count
/* Balance band, if customer is over (even by a fraction) over a band limit put them in the next band up */
,CASE WHEN cb_cust_balance >= 0.01 THEN LEAST(FLOOR(CEIL((cb_cust_balance - 0.01) * 100)/(1000*100)),250)
WHEN cb_cust_balance > 0 THEN 0
ELSE -1
END AS bal_band_no
,CASE WHEN cb_cust_balance > 85000
THEN ROUND(cbp_cust_balance * 85000 / cb_cust_balance,2)
ELSE cbp_cust_balance
END AS Prod_In_Scheme
,CASE WHEN cb_cust_balance > 85000
THEN ROUND(cbp_cust_balance * ( cb_cust_balance - 85000 ) / cb_cust_balance,2)
ELSE 0
END AS Prod_Out_of_Scheme
FROM customer_balances_by_product
JOIN customer_balances
ON cb_cust_seqno = cbp_cust_seqno
JOIN cte_prod_hierarchy h
ON h.product_code = cbp_product_code
)
SELECT MIN(rd_date) AS period_date
,cbp_product_code AS product_code
,product
,product_class
,product_group
,bal_band_no
,CAST(SUM(cbp_cust_balance) AS NUMBER(18,4)) AS total_balance
,CAST(SUM(Prod_In_Scheme) AS NUMBER(18,4)) AS Prod_In_Scheme
,CAST(SUM(Prod_Out_of_Scheme) AS NUMBER(18,4)) AS Prod_Out_of_Scheme
,CAST(SUM(cust_prod_count) AS NUMBER(15,6)) AS Cust_Count
,CAST(SUM(CASE WHEN cbp_cust_balance > 0
THEN cust_prod_count * (Prod_In_Scheme / cbp_cust_balance)
ELSE 0
END) AS NUMBER(15,6)) AS cust_prod_count_in_scheme
,CAST(SUM(CASE WHEN cbp_cust_balance > 0
THEN cust_prod_count * (Prod_Out_Of_Scheme / cbp_cust_balance)
ELSE 0
END) AS NUMBER(15,6)) AS cust_prod_count_out_of_scheme
,COUNT(*) AS count_custs
FROM cte_bands
GROUP BY cbp_product_code
,product
,product_class
,product_group
,bal_band_no
ORDER BY product_group, product_class, product, bal_band_no
|
|
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Tue Apr 23 06:11:12 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
