>select a.repcode, a.purord, count(a.sernum) as qty , b.stuff into #select1 > from scraps a LEFT OUTER JOIN redcode b ON a.scrapcode = b.number where >a.purord in (select distinct purord from income where custnum = 'AF' OR >custnum = 'AF3') and a.actiondate >= '01/01/05' and a.actiondate <= '09/07/05' >and b.stuff not like 'INPUT ERROR%' group by a.repcode, a.purord, >a.scrapcode, b.stuff order by a.repcode, a.purord, a.scrapcode, b.stuff ; > >select distinct purord, recdate into #select2 from income; > >select a.*, b.recdate from #select1 a left outer join #select2 b >on a.purord = b.purord order by a.purord ; > >drop table #select1; > >drop table #select2 >Sergey is right, VFP7 the sql odbc parser don't support a complex script.
SELECT a.*, b.recdate FROM (select a.repcode, a.purord, count(a.sernum) as qty , b.stuff from scraps a LEFT OUTER JOIN redcode b ON a.scrapcode = b.number where a.purord in (select distinct purord from income where custnum = 'AF' OR custnum = 'AF3') and a.actiondate >= '01/01/05' and a.actiondate <= '09/07/05' and b.stuff not like 'INPUT ERROR%' group by a.repcode, a.purord, a.scrapcode, b.stuff order by a.repcode, a.purord, a.scrapcode, b.stuff ) a left outer (select distinct purord, recdate from income) b on a.purord = b.purord order by a.purord