Forum Post: RE: joining 2 virtual tables runs forever or at least a very long time

[scottemick]

Ok I figured it out...I had to head on over to SQL Server Management Studio and try it against linked tables where I noticed my syntax was wrong. Now I have fixed it. Interesting how Progress just runs forever without giving an error. Below is the corrected query: SELECT * FROM (SELECT R.RegionID , R.Name Region , E.EntityID , E.Name Location , Netsales , Year(dt_StartBusDate) Period , DayOfWeek(dt_StartBusDate) DOW FROM PUB.Entity E INNER JOIN PUB.eDB_MajorGroupTotals T ON E.EntityID = T.EntityID INNER JOIN PUB.Region R ON E.RegionID = R.RegionID WHERE dt_StartBusDate BETWEEN '07-20-14' AND '07-26-14' ) T1 INNER JOIN (SELECT R.RegionID , R.Name Region , E.EntityID , E.Name Location , Netsales , Year(dt_StartBusDate) Period , DayOfWeek(dt_StartBusDate) DOW FROM PUB.Entity E INNER JOIN PUB.eDB_MajorGroupTotals T ON E.EntityID = T.EntityID INNER JOIN PUB.Region R ON E.RegionID = R.RegionID WHERE dt_StartBusDate BETWEEN '07-19-13' AND '07-25-13' ) T2 ON T1.RegionID = T2.RegionId AND T1.EntityId = T2.EntityId AND T1.DOW=T2.DOW

Continue reading...