kdeutsch
asked on
Combine local sql query with openquery
I have the following 2 sql statments that I need to combine the first is on a local database and the second goes over a linked server to grab info from oracle, my dilema is that they want the 1st queries name to be part of the second query to load in the same datagrid as one. How can i combine these 2 queries to show information. The 2 tables have a common id field of intRecruiterRecordID
Select r.intRecruiterId, w.strFullName
from tblrecruiter as r INNER JOIN
Worsdotnet.dbo.tblUser as w on w.intID = r.intRecruiterId
and w.bitFullTime = 1 and r.bitActive = 1
Select * from OPENQUERY(SIDPERS, 'Select ACN,
APP_NAME,
UIC,
PARA,
LINE,
MOS,
to_char(DT_ENLIST, ''YYYY/MM/DD'') DT_ENLIST,
ACN_STATUS,
to_char(DT_EXPIRE, ''YYYY/MM/DD'') DT_EXPIRE,
INTDRIVING_DISTANCE
From ACN.ACN_CNTL_NBR_TBL where DT_EXPIRE >= SYSDATE AND ACN_STATUS > 0 AND
ACN_STATUS < 3
Order by ACN_STATUS, UIC, DT_EXPIRE')
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
ok,
got it, I just pulled the a values as well and it seems to work.
Select w.strFullName, a.ACN, a.APP_NAME, a.UIC, a.PARA, a.LINE, a.MOS, a.DT_ENLIST, a.ACN_STATUS, a.DT_EXPIRE,
a.INTDRIVING_DISTANCE
from tblrecruiter as r INNER JOIN
Worsdotnet.dbo.tblUser as w on w.intID = r.intRecruiterId
and w.bitFullTime = 1 and r.bitActive = 1
inner join
OPENQUERY(SIDPERS, 'Select ACN, APP_NAME, UIC, PARA,
LINE, MOS, to_char(DT_ENLIST, ''YYYY/MM/DD'') DT_ENLIST,
ACN_STATUS, to_char(DT_EXPIRE, ''YYYY/MM/DD'') DT_EXPIRE,
INTDRIVING_DISTANCE,NCOID
From ACN.ACN_CNTL_NBR_TBL where DT_EXPIRE >= SYSDATE AND ACN_STATUS > 0 AND
ACN_STATUS < 3
') a
on r.intRecruiterRecordID = a.NCOID
got it, I just pulled the a values as well and it seems to work.
Select w.strFullName, a.ACN, a.APP_NAME, a.UIC, a.PARA, a.LINE, a.MOS, a.DT_ENLIST, a.ACN_STATUS, a.DT_EXPIRE,
a.INTDRIVING_DISTANCE
from tblrecruiter as r INNER JOIN
Worsdotnet.dbo.tblUser as w on w.intID = r.intRecruiterId
and w.bitFullTime = 1 and r.bitActive = 1
inner join
OPENQUERY(SIDPERS, 'Select ACN, APP_NAME, UIC, PARA,
LINE, MOS, to_char(DT_ENLIST, ''YYYY/MM/DD'') DT_ENLIST,
ACN_STATUS, to_char(DT_EXPIRE, ''YYYY/MM/DD'') DT_EXPIRE,
INTDRIVING_DISTANCE,NCOID
From ACN.ACN_CNTL_NBR_TBL where DT_EXPIRE >= SYSDATE AND ACN_STATUS > 0 AND
ACN_STATUS < 3
') a
on r.intRecruiterRecordID = a.NCOID
ASKER
Thanks a ton
ASKER
I checked the oracle db and in ther the IntRecruiterRecordId is actually NCOID, I changed to the following query but it only gets the information from the main part of the query and does not give me anything from the subquery, but runs fine.
Select r.intRecruiterId, w.strFullName
from tblrecruiter as r INNER JOIN
Worsdotnet.dbo.tblUser as w on w.intID = r.intRecruiterId
and w.bitFullTime = 1 and r.bitActive = 1
inner join
OPENQUERY(SIDPERS, 'Select ACN, APP_NAME, UIC, PARA,
LINE, MOS, to_char(DT_ENLIST, ''YYYY/MM/DD'') DT_ENLIST,
ACN_STATUS, to_char(DT_EXPIRE, ''YYYY/MM/DD'') DT_EXPIRE,
INTDRIVING_DISTANCE,NCOID
From ACN.ACN_CNTL_NBR_TBL where DT_EXPIRE >= SYSDATE AND ACN_STATUS > 0 AND
ACN_STATUS < 3
') a
on r.intRecruiterRecordID = a.NCOID