Home » SQL & PL/SQL » SQL & PL/SQL » Using WITH Clause (Oracle Database 10g)
| Using WITH Clause [message #434673] |
Thu, 10 December 2009 03:56  |
marlon_loyolite
Messages: 66
Registered: July 2006
|
Member |
|
|
Hi All,
Please advice me whether the below steps can be implemented using Oracle WITH clause.
Steps
1. Have a MAIN table which has to be refreshed with CORRECT data for each month.
2. Am creating a GTT table TEMP for data migration in MAIN table.
3. Then I insert some aggregated data(CORRECT data) into that TEMP table.
INSERT INTO TEMP (SELECT 1 AGGREGATED DATA FROM DUAL(more than 1 million records).
4. DELETE a months data from it but before that we INSERT those records into
some LOG table for backup.
5. Finally we insert CORRECT data from the TEMP table for that month into MAIN table.
In this am using GTT, since am unaware of Oracle WITH clause, can somebody guide me
to implement the same using WITH rather than using GTT.
Regards,
Marlon.
|
|
|
|
|
|
| Re: Using WITH Clause [message #434698 is a reply to message #434673] |
Thu, 10 December 2009 05:00  |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
By the sounds of it, the WITH (or subquery factoring clause) isn't what you need.
It's only really useful in a single query that references the same subquery multiple times, or has subqueries in it that can be arranged into a hierarchy.
It can make other queries easier to read as well.
It doesn't sound like it's what you're looking for, but if you post some more details about what you're doing, we'll be able to give better advice.
|
|
|
|
| Re: Using WITH Clause [message #434709 is a reply to message #434674] |
Thu, 10 December 2009 05:26 |
marlon_loyolite
Messages: 66
Registered: July 2006
|
Member |
|
|
Please find the below steps followed.
INSERT INTO TEMPORARY_TABLE
(SELECT snap_shot_date, source_system,
pillar3_exposure_class_code, exposure_type,
bcar_exposure_class_code, line_of_business,
ccis_product, pd_value, lgd_rate,
'No Specific Maturity' residual_maturity_term,
SUM (ead_pre_sec_pre_crm) AS ead_pre_sec_pre_crm,
SUM (ead_post_sec_pre_crm) AS ead_post_sec_pre_crm,
SUM (ead_post_sec_post_crm)
AS ead_post_sec_post_crm,
insured_ind, securitized_ind,
SUM (notional_principle) AS notional_principle,
SUM (rwa) AS rwa, SUM (el) AS el,
SUM (capital) AS capital, geo_section,
SUM (count_of_accounts) count_of_accounts,
pd_assessment_rating_grade, SYSDATE load_date,
SYSDATE update_date, p_submitted_by update_userid
FROM MAIN_TABLE
WHERE snap_shot_date = to_date('12/31/2009','MM/DD/YYYY')
AND bcar_exposure_class_code = 'REVOLV'
GROUP BY snap_shot_date,source_system,pillar3_exposure_class_code,exposure_type,
bcar_exposure_class_code,line_of_business,ccis_product,pd_value,lgd_rate,
residual_maturity_term,insured_ind,securitized_ind,geo_section,
pd_assessment_rating_grade);
INSERT INTO LOG_TABLE
(SELECT snap_shot_date, source_system, pillar3_exposure_class_code,
exposure_type, bcar_exposure_class_code, line_of_business,
ccis_product, pd_value, lgd_rate, pillar3_pd_band,
pd_band_description, residual_maturity_term, ead_pre_sec_pre_crm,
ead_post_sec_pre_crm, ead_post_sec_post_crm, insured_ind,
securitized_ind, notional_principle, rwa, el, capital, geo_section,
count_of_accounts, pd_assessment_rating_grade, SYSDATE load_date,
SYSDATE update_date, p_submitted_by update_userid
FROM MAIN_TABLE
WHERE snap_shot_date = to_date('12/31/2009','MM/DD/YYYY')
AND bcar_exposure_class_code = 'REVOLV');
DELETE FROM summary.pillar3
WHERE snap_shot_date = to_date('12/31/2009','MM/DD/YYYY')
AND bcar_exposure_class_code = 'REVOLV';
INSERT INTO MAIN_TABLE
(SELECT snap_shot_date, source_system, pillar3_exposure_class_code,
exposure_type, bcar_exposure_class_code, line_of_business,
ccis_product, pd_value, lgd_rate, pillar3_pd_band,
pd_band_description, residual_maturity_term,
ead_pre_sec_pre_crm, ead_post_sec_pre_crm,
ead_post_sec_post_crm, insured_ind, securitized_ind,
notional_principle, rwa, el, capital, geo_section,
count_of_accounts, pd_assessment_rating_grade,
SYSDATE load_date, SYSDATE update_date,
p_submitted_by update_userid
FROM TEMPORARY_TABLE
WHERE snap_shot_date = to_date('12/31/2009','MM/DD/YYYY'));
Regards,
Marlon
|
|
|
|
|
|
|
|
| Re: Using WITH Clause [message #434716 is a reply to message #434709] |
Thu, 10 December 2009 05:46 |
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
|
Senior Member |
|
|
|
If you are asking whether you can use the with clause as part of the syntax to insert a set of values into a table via a select statement, then the answer is no.
|
|
|
|
Goto Forum:
Current Time: Sat Feb 08 07:57:12 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
