The developer updated me with the following information regarding this problem:
'If I edit the inline view and removed the decode from the outer query, the
query works'
This update leads me to believe that this might be some kind of bug or
altered feature in 9.0.1 instead of some stupid permissions problem on my
part. Anybody seen any changes to decodes or decodes in views in 9i?
I'm sifting through the stuff on Metalink but it's slow going.
Thanks for any feedback you might have on this issue. I am also opening a
TAR with Oracle on this problem.
Cherie Machler
Oracle DBA
Gelco Information Network.
Cherie Machler
To: [EMAIL PROTECTED]
01/21/02 11:05 cc:
AM Subject: Database link and inline view
(permissions problem?)
(Document link: Cherie Machler)
I am terrible with database links. We are having a problem with a link
from a 9.0.1 database to an 8.0.4 database.
Following is the problem:
(Embedded image moved to file: pic09961.pcx) Full Description: I am
trying to run a SQL query with an inline view (see below) in
EDWDEV01 using a link to DWDEV01 to access the ACCT_PAY_TYPE
table. If I run the query in EDWDEV01 I get "no rows
selected". If I move the query to DWDEV01 and remove the
conneciton descriptions, I get 10 rows selected. Is this
permissions, database versions, or what? Any suggestions,
ideas?
SELECT
apt.acct_no,
apt.pay_seq_no,
apt.src_chng_batch_win_dt_key wh_date_key,
0 wh_time_key,
apt.data_whse_mod_dt_tm wh_mod_dt_tm,
DECODE(apt.src_chng_batch_win_dt_key, subq.maxkey,'C','H') wh_row_sts,
apt.data_src_mod_dt_tm wh_row_eff_dt_tm,
apt.data_src_mod_dt_tm upd_dt_tm,
apt.acct_pay_desc,
apt.status
FROM acct_pay_type@whse_dbo_dwdev01 apt,
(SELECT
acct_no,
data_sts,
pay_seq_no,
eff_dt_x,
MAX(src_chng_batch_win_dt_key) maxkey
FROM acct_pay_type@whse_dbo_dwdev01
GROUP BY acct_no, data_sts, pay_seq_no, eff_dt_x) subq
WHERE apt.acct_no = subq.acct_no
AND apt.data_sts = subq.data_sts
AND apt.pay_seq_no = subq.pay_seq_no
AND apt.eff_dt_x = subq.eff_dt_x
ORDER BY 1, 2, 3 DESC;
Following is the link information:
SQL> select * from dba_db_links;
OWNER DB_LINK USERNAME HOST CREATED
---------- --------------- ---------- ---------- ----------
PUBLIC WHSE_DBO_DWDEV0 WHSE_DBO DWDEV01 21-JAN-02
1.GELCO.COM
If I do a select * from acct_pay_type@whse_dbo_dwdev01 from within the
remote database (not DWDEV01), I get ten rows returned. If I run this sql
from the remote database, (not DWDEV01), I get no rows returned. If I run
the SQL from DWDEV01 with the @whse_dbo_dwdev01 removed, I get 10 rows
returned.
What is my problem? Seems like I'm missing permissions somewhere or
something like that. Any help is appreciated.
Cherie Machler
Oracle DBA
Gelco Information Network
pic09961.pcx
Description: Binary data
