Re: Database link and decode in 9i -- The Plot Thickens

From: <Cherie_Machler_at_gelco.com>
Date: Mon, 21 Jan 2002 11:50:41 -0800
Message-ID: <F001.003F6389.20020121112526@fatcity.com>

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:     ORACLE-L_at_fatcity.com                  
                           
                    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_at_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_at_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_at_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

Received on Mon Jan 21 2002 - 13:50:41 CST