需要有关查询或替代解决方案的帮助 [英] Need help coming up with a query or an alternate solution

问题描述

我在db1上有一个table1.我在db2 . The table2`上有另一个表table2，正在通过dblink在plsql过程中使用.

I have a table1 on db1. And I have another table table2 on 'db2. Thetable2` is being used in the plsql procedure via dblink.

我已经使用光标从table1获取所有主键，这样我的变量ar_col是table1的PK的列表.

I have used cursor to fetch all the primary keys from table1 such that my variable ar_col is list of PK's of table1.

现在，我想从table2获取这些值的所有值.万一我在table2中没有值，我的结果中至少应该有来自table1的pk.

Now I want to fetch all the values for those values from table2. In case, I don't have a value in table2, I should at least have pk from table1 in my result.

我使用以下查询，但返回空白

I used following query but it is returning blank

SELECT t1.col1, t2.col1, t2.col2, t2.col3, 
  FROM table1 t1
  LEFT OUTER JOIN table2@db_link t2
  ON t2.col1 = t1.col1
  WHERE t2.col1 IN (ar_col);

我想在for循环中使用结果，因此我将在此查询上使用成批收集. ar_col是包含table1的所有pk的数组

I want to use the result in a for loop, so i would be using bulk collect into on this query. the ar_col is array which holds all the pks of table1

P.S: ar_column的定义是

TYPE t_column IS TABLE OF TABLE_1.COLUMN_1%TYPE INDEX BY PLS_INTEGER;
ar_column t_column;

P.S2:ar_column并不具有所有主键，只能根据某些条件进行选择.

P.S2: ar_column doesn't have all the primary keys, only selected based on some criteria.

推荐答案

类似的东西?

select * 
  from (
         SELECT t1.col1, 
                t2.col1 t2col1, 
                t2.col2, 
                t2.col3, 
           FROM table1 t1, table2@db_link t2
          where t1.col1 = t2.col1 (+)
       )
 WHERE t2col1 in ( 
                   select thePrimaryKeyCol (what is the col name here)
                     from table1 
                 )

这篇关于需要有关查询或替代解决方案的帮助的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！