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:
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:
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:
But it doesnt work. How can I do this? Any ideas, please help.
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.