Home » SQL & PL/SQL » SQL & PL/SQL » full outer join + darabase link
full outer join + darabase link [message #243996] Mon, 11 June 2007 04:19 Go to next message
boutte
Messages: 2
Registered: June 2007
Junior Member
hell,

My DB is under 9.2.0.6, I have 2 users : user1 owner of the tables and user2 who is connecting to the user1 tables by a Database link : DBLNK_CAL, user1 has 3 tables.

This request under user1 is OK :
select count(*) from
(select id_poste
from POSTE
full outer join PAPE
on POSTE.pape_id = PAPE.id_pape
full outer join CENTRE
on PAPE.centre_id = CENTRE.id_centre)

But this one under user2 is KO :
select count(*) from
(select id_poste
from POSTE@DBLNK_CAL POS
full outer join PAPE@DBLNK_CAL PAP
on POS.pape_id = PAP.id_pape
full outer join CENTRE@DBLNK_CAL CEN
on PAP.centre_id = CEN.id_centre)
avec comme msg d'erreur :
ORA-00904: "A2"."QCSJ_C004001" : identificateur non valide
ORA-02063: précédant line de DBLNK_CAL


Under user2 these 2 request are OK :

select id_poste
from POSTE@DBLNK_CAL POS
full outer join PAPE@DBLNK_CAL PAP
on POS.pape_id = PAP.id_pape
full outer join CENTRE@DBLNK_CAL CEN
on PAP.centre_id = CEN.id_centre

and

select count(*) from
(select id_poste
from POSTE@DBLNK_CAL POS
full outer join PAPE@DBLNK_CAL PAP
on POS.pape_id = PAP.id_pape)
-- full outer join CENTRE@DBLNK_CAL CEN
-- on PAP.centre_id = CEN.id_centre)


WHY ?



Merci.
Re: full outer join + darabase link [message #244003 is a reply to message #243996] Mon, 11 June 2007 04:36 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Welcome to the forum.

Please read and apply How to format your posts.

Please repost formatted.
Also post your execution plans.
Are they final objects names or synonyms?
Post all relevant informations for us to understand the queries (for instance, db link definition queried from dictionary not words).

Regards
Michel

Re: full outer join + darabase link [message #244006 is a reply to message #244003] Mon, 11 June 2007 04:46 Go to previous messageGo to next message
boutte
Messages: 2
Registered: June 2007
Junior Member
hello,

My DB is under 9.2.0.6, I have 2 users : user1 owner of the tables and user2 who is connecting to the user1 tables by a Database link : DBLNK_CAL, user1 has 3 tables.

This request under user1 is OK :
select count(*) from
(select id_poste
from POSTE
full outer join PAPE
on POSTE.pape_id = PAPE.id_pape 
full outer join CENTRE
on PAPE.centre_id = CENTRE.id_centre)


But this one under user2 is KO :
select count(*) from
(select id_poste
from POSTE@DBLNK_CAL POS
full outer join PAPE@DBLNK_CAL PAP
on POS.pape_id = PAP.id_pape 
full outer join CENTRE@DBLNK_CAL CEN
on PAP.centre_id = CEN.id_centre)
avec comme msg d'erreur :
ORA-00904: "A2"."QCSJ_C004001" : identificateur non valide
ORA-02063: précédant line de DBLNK_CAL


Under user2 these 2 request are OK :

select id_poste
from POSTE@DBLNK_CAL POS
full outer join PAPE@DBLNK_CAL PAP
on POS.pape_id = PAP.id_pape 
full outer join CENTRE@DBLNK_CAL CEN
on PAP.centre_id = CEN.id_centre


and

select count(*) from
(select id_poste
from POSTE@DBLNK_CAL POS
full outer join PAPE@DBLNK_CAL PAP
on POS.pape_id = PAP.id_pape) 
-- full outer join CENTRE@DBLNK_CAL CEN
-- on PAP.centre_id = CEN.id_centre)


WHY ?


When I use 2 "FULL OUTER JOIN" at least whith my tables requested by the database link, it's KO. It's running well with only a "FULL OUTER JOIN".
When I use a "SELECT FROM SELECT" it's KO, when I use only the "SELECT", its' runnning with 1 oor more "FULL OUTER JOIN" !!!

I do not understand the Oracle errors :
ORA-00904: "A2"."QCSJ_C004001" : identificateur non valide
ORA-02063 : précédant line de DBLNK_CAL
Re: full outer join + darabase link [message #244009 is a reply to message #244006] Mon, 11 June 2007 05:03 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If we assume that you tell us ALL THE RELEVANT INFORMATION, it is not possible to get your message as you have neither A2 nor QCSJ_C004001 in your query.

If you don't answer our questions and post what is necessary to understand, You are On Your Own (YOYO).

Regards
Michel
Re: full outer join + darabase link [message #244194 is a reply to message #244009] Mon, 11 June 2007 22:40 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Is the remote database also 9.2?
What happens if you connect directly to that database and run the query without the @DBLNK_CAL qualifiers.

Ross Leishman
Previous Topic: Cursors vs new feautures in oracle
Next Topic: Regarding bulk update
Goto Forum:
  


Current Time: Sat Dec 10 01:00:44 CST 2016

Total time taken to generate the page: 0.20286 seconds