Thanks for the question, MA.
Asked: December 02, 2016 - 6:50 am UTC
Last updated: December 02, 2016 - 2:35 pm UTC
Version: 11gR2
Viewed 1000+ times
You Asked
hi tom i am facing this query in dynamic sql using dblinks plz simplifie this query and explain me
sqlquery := 'select sv.display_value status_id,s.parent_account_no,s.emf_config_id,cm.mkt_code'
|| ' from service@'
|| TRIM (v_ds_database)
|| ' '
|| 's, service_status@'
|| TRIM (v_ds_database)
|| ' '
|| 'ss, status_values@'
|| TRIM (v_ds_database)
|| ' '
|| 'sv,'
|| 'cmf@'
|| TRIM (v_ds_database)
|| ' '
|| 'cm'
|| ' where s.subscr_no=:b_subscr_no'
|| ' and s.subscr_no_resets= :b_subscr_no_resets'
|| ' and s.subscr_no= ss.subscr_no'
|| ' and s.subscr_no_resets = ss.subscr_no_resets'
|| ' and ss.status_id=sv.status_id'
|| ' and s.parent_account_no = cm.account_no and ss.inactive_dt is null';
and Chris said...
It's taking a variable to dynamically provide the database link name. So the query becomes a join of the tables over the db link:
select sv.display_value status_id,s.parent_account_no,s.emf_config_id,
cm.mkt_code
from service@dblink s,
service_status@dblink ss,
status_values@dblink sv,
cmf@dblink cm
where s.subscr_no =:b_subscr_no
and s.subscr_no_resets = :b_subscr_no_resets
and s.subscr_no = ss.subscr_no
and s.subscr_no_resets = ss.subscr_no_resets
and ss.status_id =sv.status_id
and s.parent_account_no = cm.account_no
and ss.inactive_dt is null ;
What it means beyond that, you'll need to speak with someone who knows and understands your database...
Is this answer out of date? If it is, please let us know via a Comment