Home » SQL & PL/SQL » SQL & PL/SQL » Using WITH Clause (Oracle Database 10g)
Using WITH Clause [message #434673] Thu, 10 December 2009 03:56 Go to next message
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 #434674 is a reply to message #434673] Thu, 10 December 2009 04:04 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
As it is clear as mud, post the code you use including DDL statements for the tables you use.

Before please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code and use code tags.
Use the "Preview Message" button to verify.
Also always post your Oracle version with 4 decimals.

Regards
Michel
Re: Using WITH Clause [message #434698 is a reply to message #434673] Thu, 10 December 2009 05:00 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #434712 is a reply to message #434709] Thu, 10 December 2009 05:29 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Thu, 10 December 2009 11:04
As it is clear as mud, post the code you use including DDL statements for the tables you use.

Before please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code and use code tags.
Use the "Preview Message" button to verify.
Also always post your Oracle version with 4 decimals.

Regards
Michel

Re: Using WITH Clause [message #434715 is a reply to message #434709] Thu, 10 December 2009 05:45 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I can see no way of using the WITH clause to improve the performance of this code.

Re: Using WITH Clause [message #434716 is a reply to message #434709] Thu, 10 December 2009 05:46 Go to previous message
pablolee
Messages: 2834
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.
Previous Topic: SELECT INTO error (merged 3)
Next Topic: ORA-14551 and INSERT INTO
Goto Forum:
  


Current Time: Sat Dec 03 12:29:29 CST 2016

Total time taken to generate the page: 0.09484 seconds