Re: Help - Complex SQL SELECT statement!!!!

hello,

   You seem to have a really interesting problem.   

  Even if you get this beast sorted out, long term maintenance is going to be less than fun.

 What are the circumstances that require you to have one query to give you the answer you want?

Would it be possible to use a temporary table and and build the result set into that as either:

1. one row with updates or
2. multiple rows with separate processing when you use it to produce the final result.

  I promise that it will a lot more maintainable and flexible. It will probably run slower (this is not necessarily true).

I am not criticising your approach, i have no idea of the circumstances that you have to produce an answer for.

graham (aka grumpy).

Larry Jones <lljones_at_flash.net> wrote...

| 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
| WHERE ( w_well_wits.field_management_team_code =
| w_team_code.team_code (+))
| 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.com

Graham Miller ...
Opinions expressed are mine, they are free, and worth exactly what they cost. Received on Sat May 10 1997 - 00:00:00 CDT