Susbelect across servers

[pms18066]

I have a query that has a subselect from another server in the join logic. See below. This query wont run due to an error saying that table ef is not a table in the query..????

Does anyone know of a problem with this type of logic? It runs find when all of the tables are on the same server..

Thanks

SELECT
ss.SchoolYear
,ss.DistrictCode
,ss.StatusIndicator
,ss.SchoolCode
,ss.StudentLink
,EnterDate
,ExitDate
,EnterCode
,ExitCode
,PermanentID
,StudentSSN
,AdvisorLink
,StateID
,ss.QuarterlyUpdateID
,ESLCode
,EFAPrimaryCode
,ss.DistrictCode + ss.SchoolCode AS DistSchool
,CASE
WHEN StudentGenderCode in ('F', 'M') THEN StudentGenderCode
ELSE
'MISSING'
END AS SEX
,CASE
WHEN GradeCode = '00' THEN 'KG'
WHEN GradeCode = 'PK' THEN GradeCode
WHEN GradeCode BETWEEN '01' AND '12' THEN GradeCode
ELSE
'MISSING'
END AS GRADELVMEM
,CASE
WHEN StudentEthnicCode in ('A', 'AP', 'P') THEN 'AS'
WHEN StudentEthnicCode in ('AI', 'I') THEN 'AM'
WHEN StudentEthnicCode in ('B', 'BI') THEN 'BL'
WHEN StudentEthnicCode = 'H' THEN 'HI'
WHEN StudentEthnicCode in ('W', 'WA', 'WB', 'WI') THEN 'WH'
ELSE
'MISSING'
END AS RACEETHNIC
,CASE
WHEN LunchStatusCode = 'F' THEN 'FL'
WHEN LunchStatusCode = 'R' THEN 'RPL'
WHEN LunchStatusCode = 'N' THEN ' '
ELSE
'MISSING'
END AS LUNCHPROG
INTO
#tmppotter
FROM
badb.SC_Eden_ods.dbo.EDENStudent ss
LEFT OUTER JOIN
SDEDBDW.Data_Repository.dbo.SASIEFAHistory ef
ON ss.SchoolYear = ef.SchoolYear
AND ss.QuarterlyUpdateID = ef.QuarterlyUpdateID
AND ss.DistrictCode = ef.DistrictCode
AND ss.SchoolCode = ef.SchoolCode
AND ss.StudentLink = ef.StudentLink
AND ef.EFAEnterDate = (SELECT MAX(ef2.EFAEnterDate)
FROM SDEDBDW.Data_Repository.dbo.SASIEFAHistory ef2
WHERE
ef.SchoolYear = ef2.SchoolYear
AND ef.DistrictCode = ef2.DistrictCode
AND ef.SchoolCode = ef2.SchoolCode
AND ef.QuarterlyUpdateID = ef2.QuarterlyUpdateID
AND ef.StudentLink = ef2.StudentLink)
WHERE
ss.StatusIndicator = ' '

 

[gmmastros]

My best suggestion would be to remove the sub-query. It's not clear from your query which columns are coming from the SASI database, and which are coming from the EDEN database. By carefully creating a derived table, you should be able to return all the data from the SASI database where the EnterDate is the Max(EnterDate). Let me know what columns are coming from SASI and I'll try to work up something for you.

Out of curiosity, what are you using for transportation software?



-George

"the screen with the little boxes in the window." - Moron

 

[gmmastros]

Try changing your From clause to this...

FROM
 badb.SC_Eden_ods.dbo.EDENStudent ss
LEFT OUTER JOIN
 SDEDBDW.Data_Repository.dbo.SASIEFAHistory ef
 ON ss.SchoolYear   = ef.SchoolYear
 AND ss.QuarterlyUpdateID = ef.QuarterlyUpdateID
 AND ss.DistrictCode  = ef.DistrictCode
 AND ss.SchoolCode  = ef.SchoolCode
 AND ss.StudentLink  = ef.StudentLink
Left Join (
    Select Max(EFAEnterDate) As EFAEnterDate,
           SchoolYear,
           DistrictCode,
           SchoolCode,
           QuarterlyUpdateId,
           StudentLink
    From   SDEDBDW.Data_Repository.dbo.SASIEFAHistory
    Group By SchoolYear, DistrictCode, SchoolCode, QuarterlyUpdateId, StudentLink
    ) As A
       On  ef.SchoolYear = A.SchoolYear
       And ef.DistrictCode = A.DistrictCode 
       And ef.SchoolCode = A.SchoolCode
       And ef.QuarterlyUpdateId = A.QuarterlyUpdateId
       And ef.StudentLink = A.StudentLink
       And ef.EFAEnterDate = A.EFAEnterDate

-George

"the screen with the little boxes in the window." - Moron

 

[pms18066]

I was just getting the efa code from SASI in this case...
Everything else is already in theEDEN dbase..

I used a temp table to pull out the subselect and that worked pretty good. It still takes a couple of miutes to run but thats more than sufficient.

Thanks

Tony