Combine local sql query with openquery

kdeutsch
kdeutsch used Ask the Experts™
on
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')

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
You can either pull the columns back into a local temp table from the openquery, then join.  Or, do the join as part of the query.
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 
							From	ACN.ACN_CNTL_NBR_TBL where DT_EXPIRE >= SYSDATE AND ACN_STATUS > 0 AND 
									ACN_STATUS < 3 
') a
on r.intRecruiterRecordID = a.intRecruiterRecordID

Open in new window

Author

Commented:
BrandonGalderisi,
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

Author

Commented:
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

Author

Commented:
Thanks a ton

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial