March 25, 2015 at 3:42 pm
I can create a temp table like this, and immediately read the data:
declare @NAICSGroups table (NAICS2DigitCode int, NAICSShortTitle varchar(35));
insert into @NAICSGroups (NAICS2DigitCode, NAICSShortTitle)
values
(11,'Agriculture'),
(21,'Mining'),
(22,'Utilities'),
(23,'Construction'),
(31,'Manufacturing'),
(32,'Manufacturing'),
(33,'Manufacturing'),
(42,'Wholesale Trade'),
(44,'Retail Trade'),
(45,'Retail Trade'),
(48,'Transportation/Warehousing'),
(49,'Transportation/Warehousing'),
(51,'Information'),
(52,'Finance/Insurance'),
(53,'Real Estate'),
(54,'Pro, Sci, Tech Svcs'),
(55,'Mgt of Companies'),
(56,'Admin, Support, Waste, Remediation'),
(61,'Educational Svcs'),
(62,'Health and Social'),
(71,'Arts, Entertainment, Recreation'),
(72,'Accomodation/Food'),
(81,'Other'),
(92,'Public Administration')
select * from @NAICSGroups --this works
But if I try to use the temp table in my real query, I get the abovementioned error:
declare @NAICSGroups table (NAICS2DigitCode int, NAICSShortTitle varchar(35));
insert into @NAICSGroups (NAICS2DigitCode, NAICSShortTitle)
values
(11,'Agriculture'),
(21,'Mining'),
(22,'Utilities'),
(23,'Construction'),
(31,'Manufacturing'),
(32,'Manufacturing'),
(33,'Manufacturing'),
(42,'Wholesale Trade'),
(44,'Retail Trade'),
(45,'Retail Trade'),
(48,'Transportation/Warehousing'),
(49,'Transportation/Warehousing'),
(51,'Information'),
(52,'Finance/Insurance'),
(53,'Real Estate'),
(54,'Pro, Sci, Tech Svcs'),
(55,'Mgt of Companies'),
(56,'Admin, Support, Waste, Remediation'),
(61,'Educational Svcs'),
(62,'Health and Social'),
(71,'Arts, Entertainment, Recreation'),
(72,'Accomodation/Food'),
(81,'Other'),
(92,'Public Administration')
select WageQuarter.City, WageQuarter.CY, WageQuarter.CYQ, WageQuarter.NAICS2DigitCode, WageQuarter.NAICSShortTitle, COUNT(WageQuarter.SSN)
from
(select distinct dbo.DWEmployerView.City, dbo.DWWageDetailView.WageReportYear as CY, dbo.DWWageDetailView.WageReportQuarter as CYQ,
NAICS2DigitCode, NAICSShortTitle, dbo.DWEmployerView.EmployerID, SSN
from @NAICSGroups
inner join NGTSViewDB.dbo.DWEmployerView on @NAICSGroups.NAICS2DigitCode=SUBSTRING(NGTSViewDB.dbo.DWEmloyerView.NAICSCode,1,2)
inner join NGTSViewDB.dbo.DWWageDetailView on NGTSViewDB.dbo.DWWageDetailView.EmployerID=NGTSViewDB.dbo.DWEmployerView.EmployerID
where dbo.DWWageDetailView.WageReportYear>=2013 and dbo.DWWageDetailView.WageReportYear<2015 and dbo.DWWageDetailView.Wages>0
and dbo.DWWageDetailView.WageTypeCode in (20156,20161,20165)
and dbo.DWWageDetailView.WageReportDetailStatusREFID=17676
) as WageQuarter
left merge join
(select distinct dbo.DWEmployerView.City, dbo.DWWageDetailView.WageReportYear as CY, dbo.DWWageDetailView.WageReportQuarter as CYQ,
NAICS2DigitCode, NAICSShortTitle, dbo.DWEmployerView.EmployerID, SSN
from @NAICSGroups
inner join NGTSViewDB.dbo.DWEmployerView on @NAICSGroups.NAICS2DigitCode=SUBSTRING(NGTSViewDB.dbo.DWEmloyerView.NAICSCode,1,2)
inner join NGTSViewDB.dbo.DWWageDetailView on NGTSViewDB.dbo.DWWageDetailView.EmployerID=NGTSViewDB.dbo.DWEmployerView.EmployerID
where dbo.DWWageDetailView.WageReportYear>=2013 and dbo.DWWageDetailView.WageReportYear<2015 and dbo.DWWageDetailView.Wages>0
and dbo.DWWageDetailView.WageTypeCode in (20156,20161,20165)
and dbo.DWWageDetailView.WageReportDetailStatusREFID=17676
) as PrevQuarter
on WageQuarter.CY =
case PrevQuarter.CYQ
when 1 then PrevQuarter.CY - 1
else PrevQuarter.CY
end
and WageQuarter.CYQ =
case PrevQuarter.CYQ
when 1 then 4
else PrevQuarter.CYQ - 1
end
and WageQuarter.EmployerID = PrevQuarter.EmployerID
and WageQuarter.SSN = PrevQuarter.SSN
where PrevQuarter.SSN is null
group by WageQuarter.City, WageQuarter.CY, WageQuarter.CYQ, WageQuarter.NAICS2DigitCode, WageQuarter.NAICSShortTitle
order by WageQuarter.City, WageQuarter.CY, WageQuarter.CYQ, WageQuarter.NAICS2DigitCode, WageQuarter.NAICSShortTitle;
So, can t-sql not find the temp table because it's in the FROM clause inside a subquery?
March 25, 2015 at 4:08 pm
No, but you must alias it so SQL can recognize the difference between a scalar variable one a table. I think it's a parsing issue if you don't use an alias:
select WageQuarter.City, WageQuarter.CY, WageQuarter.CYQ, WageQuarter.NAICS2DigitCode, WageQuarter.NAICSShortTitle, COUNT(WageQuarter.SSN)
from
(select distinct dbo.DWEmployerView.City, dbo.DWWageDetailView.WageReportYear as CY, dbo.DWWageDetailView.WageReportQuarter as CYQ,
NAICS2DigitCode, NAICSShortTitle, dbo.DWEmployerView.EmployerID, SSN
from @NAICSGroups ng
inner join NGTSViewDB.dbo.DWEmployerView on ng.NAICS2DigitCode=SUBSTRING(NGTSViewDB.dbo.DWEmloyerView.NAICSCode,1,2)
inner join NGTSViewDB.dbo.DWWageDetailView on NGTSViewDB.dbo.DWWageDetailView.EmployerID=NGTSViewDB.dbo.DWEmployerView.EmployerID
where dbo.DWWageDetailView.WageReportYear>=2013 and dbo.DWWageDetailView.WageReportYear<2015 and dbo.DWWageDetailView.Wages>0
and dbo.DWWageDetailView.WageTypeCode in (20156,20161,20165)
and dbo.DWWageDetailView.WageReportDetailStatusREFID=17676
) as WageQuarter
left merge join
(select distinct dbo.DWEmployerView.City, dbo.DWWageDetailView.WageReportYear as CY, dbo.DWWageDetailView.WageReportQuarter as CYQ,
NAICS2DigitCode, NAICSShortTitle, dbo.DWEmployerView.EmployerID, SSN
from @NAICSGroups ng
inner join NGTSViewDB.dbo.DWEmployerView on ng.NAICS2DigitCode=SUBSTRING(NGTSViewDB.dbo.DWEmloyerView.NAICSCode,1,2)
inner join NGTSViewDB.dbo.DWWageDetailView on NGTSViewDB.dbo.DWWageDetailView.EmployerID=NGTSViewDB.dbo.DWEmployerView.EmployerID
where dbo.DWWageDetailView.WageReportYear>=2013 and dbo.DWWageDetailView.WageReportYear<2015 and dbo.DWWageDetailView.Wages>0
and dbo.DWWageDetailView.WageTypeCode in (20156,20161,20165)
and dbo.DWWageDetailView.WageReportDetailStatusREFID=17676
) as PrevQuarter
on WageQuarter.CY =
case PrevQuarter.CYQ
when 1 then PrevQuarter.CY - 1
else PrevQuarter.CY
end
and WageQuarter.CYQ =
case PrevQuarter.CYQ
when 1 then 4
else PrevQuarter.CYQ - 1
end
and WageQuarter.EmployerID = PrevQuarter.EmployerID
and WageQuarter.SSN = PrevQuarter.SSN
where PrevQuarter.SSN is null
group by WageQuarter.City, WageQuarter.CY, WageQuarter.CYQ, WageQuarter.NAICS2DigitCode, WageQuarter.NAICSShortTitle
order by WageQuarter.City, WageQuarter.CY, WageQuarter.CYQ, WageQuarter.NAICS2DigitCode, WageQuarter.NAICSShortTitle;
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
March 25, 2015 at 4:15 pm
Thank you Scott! It's running!
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply