Recordsets/.NET

dsk96m

Well-known member
Joined
Jan 11, 2013
Messages
173
Programming Experience
1-3
Here is the situation. I have to get data out of oracle from two tables. The problem is the tables are in different oracle instances. I need to use the results from one select in the where close of the second. I am not sure how to do this. Prior, i just hard coded the where, but things change so much, i decided to do it a different way.

Previous way:
VB.NET:
ORAP_Rst = New ADODB.Recordset
ORAD_Rst = New ADODB.Recordset

strSql = "SELECT CDAS.VDWHEMP3.EMPL_NO, CDAS.VDWHEMP3.EMPL_CCTR_ID, CDAS.VDWHEMP3.EMPL_ID, CDAS.VDWHEMP3.EMPL_last_nm, " & _
      "CDAS.VDWHEMP3.EMPL_first_nm, CDAS.VDWHEMP3.EMPL_middle_init, CDAS.VDWHEMP3.EMPL_Stat, " & _
      "CDAS.VDWHEMP3.EMPL_Last_work_dt, CDAS.VDWHEMP3.EMPL_pasa_cd, CDAS.VDWHEMP3.EMPL_stat, " & _
      "CDAS.VDWHEMP3.EMPL_seniority_dt, CDAS.VDWHEMP3.EMPL_pacc_id, CDAS.VDWHEMP3.EMPL_Empl_id From CDAS.VDWHEMP3 " & _
      "WHERE (((CDAS.VDWHEMP3.EMPL_CCTR_ID) LIKE 'PA%' Or (CDAS.VDWHEMP3.EMPL_CCTR_ID) LIKE 'OA%' Or (CDAS.VDWHEMP3.EMPL_CCTR_ID) LIKE 'SA%' Or " & _
      "(CDAS.VDWHEMP3.EMPL_CCTR_ID) LIKE 'BG%' Or (CDAS.VDWHEMP3.EMPL_CCTR_ID) LIKE 'BR%' Or (CDAS.VDWHEMP3.EMPL_CCTR_ID) LIKE '2C%' Or                     (CDAS.VDWHEMP3.EMPL_CCTR_ID) LIKE 'EK2%' Or " & _
      "(CDAS.VDWHEMP3.EMPL_CCTR_ID) LIKE '5K2%' Or (CDAS.VDWHEMP3.EMPL_CCTR_ID) LIKE 'JZ%' Or (CDAS.VDWHEMP3.EMPL_CCTR_ID) LIKE 'R5205%' Or (CDAS.VDWHEMP3.EMPL_CCTR_ID) LIKE 'R5206%' Or " & _
       "(CDAS.VDWHEMP3.EMPL_CCTR_ID) LIKE 'A3570%' Or (CDAS.VDWHEMP3.EMPL_CCTR_ID) LIKE '8Z%' Or " & _
       "(CDAS.VDWHEMP3.EMPL_CCTR_ID) LIKE 'KE477%' or (CDAS.VDWHEMP3.EMPL_CCTR_ID) LIKE 'E1019%' Or (CDAS.VDWHEMP3.EMPL_CCTR_ID) LIKE '2D%') " & _
       "AND ((CDAS.VDWHEMP3.EMPL_Stat)<>'TERMINATED' AND (CDAS.VDWHEMP3.EMPL_Stat)<>'RETIRED') AND ((CDAS.VDWHEMP3.EMPL_CCTR_ID)<>'8ZA')) ORDER BY CDAS.VDWHEMP3.EMPL_last_nm"

        ORAD_Rst.Open(strSql, oCNN2)
        ORAD_Rst.MoveFirst()

This would get me what I need before. You can see the hardcoded where closer for empl_cctr_id. CDAS table is in ORAD (1 oracle instance). So now i want to pull the values of the where clause from this:

VB.NET:
ORAP_Rst.Open("SELECT FSET.TFSETBCTR.BCTR_CD FROM FSET.TFSETBCTR WHERE FSET.TFSETBCTR.BCTR_INCL='T'", oCNN)

This is from the FSET Tables in ORAP (2nd oracle instance).

I was going to try and do an exists in, but they are two different oracle instances so I couldnt do that.

I tried to do this:

VB.NET:
strSql = "SELECT CDAS.VDWHEMP3.EMPL_NO, CDAS.VDWHEMP3.EMPL_CCTR_ID, CDAS.VDWHEMP3.EMPL_ID, CDAS.VDWHEMP3.EMPL_last_nm, " & _
                "CDAS.VDWHEMP3.EMPL_first_nm, CDAS.VDWHEMP3.EMPL_middle_init, CDAS.VDWHEMP3.EMPL_Stat, " & _
                "CDAS.VDWHEMP3.EMPL_Last_work_dt, CDAS.VDWHEMP3.EMPL_pasa_cd, CDAS.VDWHEMP3.EMPL_stat, " & _
                "CDAS.VDWHEMP3.EMPL_seniority_dt, CDAS.VDWHEMP3.EMPL_pacc_id, CDAS.VDWHEMP3.EMPL_Empl_id From CDAS.VDWHEMP3 " & _
                "Where ((CDAS.VDWHEMP3.EMPL_Stat)<>'TERMINATED' AND (CDAS.VDWHEMP3.EMPL_Stat)<>'RETIRED') AND ((CDAS.VDWHEMP3.EMPL_CCTR_ID)<>'8ZA')) ORDER BY CDAS.VDWHEMP3.EMPL_last_nm " & _
                "AND CDAS.VDWHEMP3.EMPL_CCTR_ID EXISTS " & ORAP_Rst.GetRows

But it doesnt work. How can I do this? Any ideas, please help.
 
Typically you would do this server-side. You can create a database link between two Oracle databases on two different servers, and then use the fully qualified name to access both in the same query.

If you are going to write text queries and not use proper command objects, I would suggest you at least use XML literals to enclose them instead of concatenating strings with & _. See the link below for more information. However I would suggest you use actual parametrized command objects instead.

CREATE DATABASE LINK

Multiline strings in VB.NET - Stack Overflow
 
Last edited:
Would it be like
Dim sqlstr= <sql>
SELECT CDAS.VDWHEMP3.EMPL_NO, CDAS.VDWHEMP3.EMPL_CCTR_ID, CDAS.VDWHEMP3.EMPL_ID, CDAS.VDWHEMP3.EMPL_last_nm,
CDAS.VDWHEMP3.EMPL_first_nm, CDAS.VDWHEMP3.EMPL_middle_init, CDAS.VDWHEMP3.EMPL_Stat, ....
</sql>.Value
 
What is i get the one result into an array:
Dim ccVar As Object = ORAP_Rst.GetRows(ORAP_Rst.RecordCount)

How can I use that in a where clause of an sql statement
 
Back
Top