Link to home
Start Free TrialLog in
Avatar of kdeutsch
kdeutschFlag for United States of America

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')

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of BrandonGalderisi
BrandonGalderisi
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of kdeutsch

ASKER

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
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
Thanks a ton