| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Help - Complex SQL SELECT statement!!!!
I'm sorry this message is so long, but this is fairly complicated and I want to try to explain it as best as I can. I will really appreciate it if someone can take the time to read this, and help me out. I would appreciate a post to this newsgroup, and email, if possible to my work eddress (lljo_at_chevron.com).
I need to do a SQL select statement, which is joining many tables, and has to retrieve data for the specified year, plus the previous year, and the year before that too. I will include the original statment at the end of this message (maybe someone can help clean it up) but with some luck, the smaller example below will give you an idea of what I need to do.
SELECT pre.oil_vol,
pre.gas_vol,
post.oil_vol,
post.gas_vol
FROM pre,
post
WHERE ( pre.well_number = post.well_number )
AND ( pre.sidetrack = post.sidetrack )
AND ( post.year = &year );
This part is fairly simple, but now I also need to return the same oil and gas volumes from the PRE and POST tables, for the previous 1 and 2 years. So I have tried something like this.
SELECT pre.oil_vol,
pre.gas_vol,
post.oil_vol,
post.gas_vol,
pre1.oil_vol pre_oil_vol1,
pre1.gas_vol pre_oil_vol1,
post1.oil_vol post_oil_vol1,
post1.gas_vol post_oil_vol1,
pre2.oil_vol pre_oil_vol2,
pre2.gas_vol pre_oil_vol2,
post2.oil_vol post_oil_vol2,
post2.gas_vol post_oil_vol2
FROM pre,
post,
pre pre1,
pre pre2,
post post1,
post post2
WHERE ( pre2.well_number = post.well_number )
AND ( pre2.sidetrack = post.sidetrack )
AND ( pre2.year = &year - 2 )
AND ( pre1.well_number = post.well_number )
AND ( pre1.sidetrack = post.sidetrack )
AND ( pre1.year = &year - 1 )
AND ( post2.well_number = post.well_number )
AND ( post2.sidetrack = post.sidetrack )
AND ( post2.year = &year - 2 )
AND ( post1.well_number = post.well_number )
AND ( post1.sidetrack = post.sidetrack )
AND ( post1.year = &year - 1 )
AND ( pre.well_number = post.well_number )
AND ( pre.sidetrack = post.sidetrack )
AND ( post.year = &year );
Now this, ALMOST, works. It does return what I need, except for those wells that may not have data for 1 or 2 years ago. I need to return all the wells, even if there are not any records for previous year(s). I have tried doing an outer join on those years, but the following syntax does not work:
WHERE ( pre2.well_number = post.well_number ) AND ( pre2.sidetrack = post.sidetrack ) AND ( pre2.year = &year - 2 (+) ) AND ( pre1.well_number = post.well_number ) AND ( pre1.sidetrack = post.sidetrack ) AND ( pre1.year = &year - 1 (+) ) AND ( post2.well_number = post.well_number ) AND ( post2.sidetrack = post.sidetrack ) AND ( post2.year = &year - 2 (+) ) AND ( post1.well_number = post.well_number ) AND ( post1.sidetrack = post.sidetrack ) AND ( post1.year = &year - 1 (+) ) AND ( pre.well_number = post.well_number ) AND ( pre.sidetrack = post.sidetrack ) AND ( post.year = &year );
This causes an error, something about missing right parenthisis!
I have also tried using parenthesis, like the following, with no luck (same error):
AND (pre2.year = (&year - 2) (+) )
Does anyone have any suggestions as to how I can do an outer join on this calculated field? Or maybe a better way to do this completly?
As I said earlier, below is the real SQL statement. It's a mess, but it's close to what I need.
Thank you again, for taking the time to read this.
SELECT w_well_wits.profit_center_code,
w_ucal5.ucal5nm,
w_ucal6.ucal6nm,
w_well_wits.well_number,
w_catalog.chevno,
w_catalog.wellbore_number,
w_catalog.well_completion_number,
w_wellwork_common.wellwork_afe,
w_wellwork_common.company_interest,
RTRIM(w_pre_general.expenditure_type || ' - ' ||
w_post_general.expenditure_type) "exptype",
w_post_general.completion_date,
nvl(w_pre_cost.wellwork_net_total,0) +
nvl(w_pre_cost.facility_net_total,0) "pre_m$",
nvl(w_post_cost.wellwork_uca_net_total,0) +
nvl(w_post_cost.facility_uca_net_total,0) "post_m$",
nvl(w_post_cost.wellwork_uca_actual_cost,0) +
nvl(w_post_cost.facility_uca_actual_cost,0) "m$",
nvl(w_pre_depletion2.oil_depletion_net,0) "pre2_bbls",
nvl(w_pre_depletion2.gas_depletion_net,0) "pre2_mmcf",
nvl(w_pre_depletion1.oil_depletion_net,0) "pre1_bbls",
nvl(w_pre_depletion1.gas_depletion_net,0) "pre1_mmcf",
nvl(w_pre_depletion.oil_depletion_net,0) "pre_bbls",
nvl(w_pre_depletion.gas_depletion_net,0) "pre_mmcf",
nvl(w_post_depletion2.oil_depletion_net,0) "post2_bbls",
nvl(w_post_depletion2.gas_depletion_net,0) "post2_mmcf",
nvl(w_post_depletion1.oil_depletion_net,0) "post1_bbls",
nvl(w_post_depletion1.gas_depletion_net,0) "post1_mmcf",
nvl(w_post_depletion.oil_actual_net,0) "post_bbls",
nvl(w_post_depletion.gas_actual_net,0) "post_mmcf",
nvl(margin,0) "margin"
FROM w_ucal5,
w_ucal6,
w_margins,
w_team_code,
w_pre_general,
w_post_general,
w_pre_depletion w_pre_depletion2,
w_pre_depletion w_pre_depletion1,
w_pre_depletion,
w_post_depletion w_post_depletion2,
w_post_depletion w_post_depletion1,
w_post_depletion,
w_pre_cost,
w_post_cost,
w_catalog,
w_well_wits,
w_selected_wellwork,
w_wellwork_common
AND ( w_well_wits.field_code = w_ucal5.ucal5cd (+))
AND ( w_well_wits.profit_center_code = w_ucal5.ucal3cd (+))
AND ( w_well_wits.lease_block_code = w_ucal6.ucal6cd (+) )
AND ( w_well_wits.field_code = w_ucal6.ucal5cd (+) )
AND ( w_well_wits.profit_center_code = w_ucal6.ucal3cd (+) )
AND ( w_well_wits.business_unit_code = w_margins.business_unit_code
(+) )
AND ( w_well_wits.profit_center_code = w_margins.profit_center_code
(+) )
AND ( w_well_wits.field_code = w_margins.field_code (+) )
AND ( w_pre_depletion2.number_year = (w_pre_depletion.number_year -
2) )
AND ( w_pre_depletion1.number_year = (w_pre_depletion.number_year -
1) )
AND ( w_pre_depletion.number_year = w_post_depletion.number_year )
AND ( w_post_depletion2.number_year = (w_post_depletion.number_year
- 2) )
AND ( w_post_depletion1.number_year = (w_post_depletion.number_year
- 1) )
AND ( w_post_depletion.number_year = &YEAR)
AND ( w_wellwork_common.wellwork_number =
w_pre_general.wellwork_number )
AND ( w_wellwork_common.project_id = w_pre_general.project_id )
AND ( w_wellwork_common.unique_id = w_pre_general.unique_id )
AND ( w_wellwork_common.set_id = w_pre_general.set_id )
AND ( w_wellwork_common.wellwork_number =
w_post_general.wellwork_number )
AND ( w_wellwork_common.project_id = w_post_general.project_id )
AND ( w_wellwork_common.unique_id = w_post_general.unique_id )
AND ( w_wellwork_common.set_id = w_post_general.set_id )
AND ( w_wellwork_common.wellwork_number =
w_pre_depletion2.wellwork_number )
AND ( w_wellwork_common.project_id = w_pre_depletion2.project_id )
AND ( w_wellwork_common.unique_id = w_pre_depletion2.unique_id )
AND ( w_wellwork_common.set_id = w_pre_depletion2.set_id )
AND ( w_wellwork_common.wellwork_number =
w_pre_depletion1.wellwork_number )
AND ( w_wellwork_common.project_id = w_pre_depletion1.project_id )
AND ( w_wellwork_common.unique_id = w_pre_depletion1.unique_id )
AND ( w_wellwork_common.set_id = w_pre_depletion1.set_id )
AND ( w_wellwork_common.wellwork_number =
w_pre_depletion.wellwork_number )
AND ( w_wellwork_common.project_id = w_pre_depletion.project_id )
AND ( w_wellwork_common.unique_id = w_pre_depletion.unique_id )
AND ( w_wellwork_common.set_id = w_pre_depletion.set_id )
AND ( w_wellwork_common.wellwork_number =
w_post_depletion2.wellwork_number )
AND ( w_wellwork_common.project_id = w_post_depletion2.project_id )
AND ( w_wellwork_common.unique_id = w_post_depletion2.unique_id )
AND ( w_wellwork_common.set_id = w_post_depletion2.set_id )
AND ( w_wellwork_common.wellwork_number =
w_post_depletion1.wellwork_number )
AND ( w_wellwork_common.project_id = w_post_depletion1.project_id )
AND ( w_wellwork_common.unique_id = w_post_depletion1.unique_id )
AND ( w_wellwork_common.set_id = w_post_depletion1.set_id )
AND ( w_wellwork_common.wellwork_number =
w_post_depletion.wellwork_number )
AND ( w_wellwork_common.project_id = w_post_depletion.project_id )
AND ( w_wellwork_common.unique_id = w_post_depletion.unique_id )
AND ( w_wellwork_common.set_id = w_post_depletion.set_id )
AND ( w_wellwork_common.wellwork_number =
w_post_cost.wellwork_number )
AND ( w_wellwork_common.project_id = w_pre_cost.project_id )
AND ( w_wellwork_common.unique_id = w_pre_cost.unique_id )
AND ( w_wellwork_common.set_id = w_pre_cost.set_id )
AND ( w_wellwork_common.project_id = w_post_cost.project_id )
AND ( w_wellwork_common.unique_id = w_post_cost.unique_id )
AND ( w_wellwork_common.set_id = w_post_cost.set_id )
AND ( w_wellwork_common.unique_id = w_catalog.unique_id )
AND ( w_wellwork_common.set_id = w_catalog.set_id )
AND ( w_wellwork_common.project_id = w_well_wits.project_id )
AND ( w_wellwork_common.unique_id = w_well_wits.unique_id )
AND ( w_wellwork_common.set_id = w_well_wits.set_id )
AND ( w_selected_wellwork.wellwork_number =
w_wellwork_common.wellwork_number )
AND ( w_selected_wellwork.unique_id = w_wellwork_common.unique_id )
AND ( ( w_selected_wellwork.userid = '&USER' ) )
AND ( w_margins.year = &YEAR );
-- Larry Jones lljones_at_flash.net lljo_at_chevron.comReceived on Tue May 06 1997 - 00:00:00 CDT
 |
 |