By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
428,742 Members | 1,570 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 428,742 IT Pros & Developers. It's quick & easy.

update with left join,from,on

P: 1
Hi all

pls tell me that the following statment is valid in oracle 9i or 10g..

update ws
set received_by_facility = coalesce(rbf_ouk.organizational_unit_id, 0),
assigned_to_facility = coalesce(atf_ouk.organizational_unit_id, 0),
received_by_team = coalesce(rbt_ouk.organizational_unit_id, 0),
assigned_to_team = coalesce(att_ouk.organizational_unit_id, 0)

from arrow.tsop_worksheet ws
/* received by individual */
left join temployee_keys rb_ek on ws.received_by = rb_ek.employee_id left join f0150 rb_ma on rb_ab.maan8 = rb_ek.oneworld_id and rb_ma.maostp = ' '
/* received by individual's team */
left join torganizational_unit_keys rbt_ouk on rbt_ouk.oneworld_id = rb_ma.mapa8 left join f0150 rbt_ma on rbt_ma.maan8 = rbt_ouk.oneworld_id and rbt_ma.maostp = ' '
/* received by individual's team's facility */
left join torganizational_unit_keys rbf_ouk on rbf_ouk.oneworld_id = rbt_ma.mapa8
/* assigned to individual */
left join temployee_keys at_ek on ws.assigned_to = at_ek.employee_id left join f0150 at_ma on at_ab.maan8 = at_ek.oneworld_id and at_ma.maostp = ' '
/* assigned to individual's team */
left join torganizational_unit_keys att_ouk on att_ouk.oneworld_id = at_ma.mapa8 left join f0150 att_ma on att_ma.maan8 = att_ouk.oneworld_id and att_ma.maostp = ' '
/* assigned to individual's team's facility */
left join torganizational_unit_keys atf_ouk on atf_ouk.oneworld_id = att_ma.mapa8

where worksheet_id is between start_ws_id and end_ws;


if yes then pls tell me the order of evalution..

and if not then what is the alternative in oracle of this statment

Regards

Naveen
Jul 18 '07 #1
Share this Question
Share on Google+
1 Reply


P: 5
Hi all

pls tell me that the following statment is valid in oracle 9i or 10g..

update ws
set received_by_facility = coalesce(rbf_ouk.organizational_unit_id, 0),
assigned_to_facility = coalesce(atf_ouk.organizational_unit_id, 0),
received_by_team = coalesce(rbt_ouk.organizational_unit_id, 0),
assigned_to_team = coalesce(att_ouk.organizational_unit_id, 0)

from arrow.tsop_worksheet ws
/* received by individual */
left join temployee_keys rb_ek on ws.received_by = rb_ek.employee_id left join f0150 rb_ma on rb_ab.maan8 = rb_ek.oneworld_id and rb_ma.maostp = ' '
/* received by individual's team */
left join torganizational_unit_keys rbt_ouk on rbt_ouk.oneworld_id = rb_ma.mapa8 left join f0150 rbt_ma on rbt_ma.maan8 = rbt_ouk.oneworld_id and rbt_ma.maostp = ' '
/* received by individual's team's facility */
left join torganizational_unit_keys rbf_ouk on rbf_ouk.oneworld_id = rbt_ma.mapa8
/* assigned to individual */
left join temployee_keys at_ek on ws.assigned_to = at_ek.employee_id left join f0150 at_ma on at_ab.maan8 = at_ek.oneworld_id and at_ma.maostp = ' '
/* assigned to individual's team */
left join torganizational_unit_keys att_ouk on att_ouk.oneworld_id = at_ma.mapa8 left join f0150 att_ma on att_ma.maan8 = att_ouk.oneworld_id and att_ma.maostp = ' '
/* assigned to individual's team's facility */
left join torganizational_unit_keys atf_ouk on atf_ouk.oneworld_id = att_ma.mapa8

where worksheet_id is between start_ws_id and end_ws;


if yes then pls tell me the order of evalution..

and if not then what is the alternative in oracle of this statment

Regards

Naveen


Hey,Wat you are trying to do exactly
coalesce function returns first not null value, so in u r case, it will abviously returns first value,bcoz second value u put zero.
Jul 19 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.