May 3, 2012 at 2:30 am
Hi guys,
ok I have this query:
Declare @Measure nvarchar (50)
Set @Measure = (Select MeasureCode from v_meascode where (( Measure_Description = N'Bags' ) ))
If @Measure = 'TONS'
Begin
Select l.Source_Description, a.ShKey, Sum(m.MeasureValue) as MeasureValue, s.Period_day as DateCode
into #TempProduction
from ALLOCTNTIMESTAMP a Join
MEASURETIMESTAMP m ON m.TSKey = a.TSKey join
V_Locations l ON l.Source_Code = a.Location join
#TempShift s ON s.ShiftCode = a.ShKey Join
V_MeasCode ON V_MeasCode.Measure_Description = m.MeasCode join
V_Equipment e ON e.EquipmentCode = a.Equipment Join
V_Equipment_Model em ON em.Equipment_Model_Code = e.Equipment_Model_Code join
V_Equipment_Function ef ON ef.Equipment_Function_Code = em.Equipment_Model_Function_Code
Where (( Measure_Description = N'Tons' ) ) and ef.Equipment_Function_Description = 'Loading'
Group by l.Source_Description, a.ShKey, m.MeasureValue, s.Period_day
Order by l.Source_Description
End
If @Measure <> 'TONS'
Begin
Select l.Source_Description, a.ShKey, Sum(m.MeasureValue) as MeasureValue, s.Period_day as DateCode
into #TempProduction
from ALLOCTNTIMESTAMP a Join
MEASURETIMESTAMP m ON m.TSKey = a.TSKey join
V_Locations l ON l.Source_Code = a.Location join
#TempShift s ON s.ShiftCode = a.ShKey Join
V_MeasCode ON V_MeasCode.Measure_Description = m.MeasCode join
V_Equipment e ON e.EquipmentCode = a.Equipment Join
V_Equipment_Model em ON em.Equipment_Model_Code = e.Equipment_Model_Code join
V_Equipment_Function ef ON ef.Equipment_Function_Code = em.Equipment_Model_Function_Code
Where (( Measure_Description = N'Bags' ) )
Group by l.Source_Description, a.ShKey, m.MeasureValue, s.Period_day
Order by l.Source_Description
End
I'm getting an error stating that the table #TempProduction already exists. So even if it's not running the top query it does see the temp table name. How can I bypass this? I need to have that name because I'm using it further down the full query.
May 3, 2012 at 2:33 am
Use a CREATE TABLE statement to create the temp table before the IF and then use INSERT INTO.
Declare @Measure nvarchar (50)
create table #TempProduction
(
Source_Description <<datatype>>,
ShKey <<datatype>>,
MeasureValue <<datatype>>,
DateCode <<datatype>>
)
Set @Measure = (Select MeasureCode from v_meascode where (( Measure_Description = N'Bags' ) ))
If @Measure = 'TONS'
Begin
INSERT INTO #TempProduction
Select l.Source_Description, a.ShKey, Sum(m.MeasureValue) as MeasureValue, s.Period_day as DateCode
from ALLOCTNTIMESTAMP a Join
MEASURETIMESTAMP m ON m.TSKey = a.TSKey join
V_Locations l ON l.Source_Code = a.Location join
#TempShift s ON s.ShiftCode = a.ShKey Join
V_MeasCode ON V_MeasCode.Measure_Description = m.MeasCode join
V_Equipment e ON e.EquipmentCode = a.Equipment Join
V_Equipment_Model em ON em.Equipment_Model_Code = e.Equipment_Model_Code join
V_Equipment_Function ef ON ef.Equipment_Function_Code = em.Equipment_Model_Function_Code
Where (( Measure_Description = N'Tons' ) ) and ef.Equipment_Function_Description = 'Loading'
Group by l.Source_Description, a.ShKey, m.MeasureValue, s.Period_day
Order by l.Source_Description
End
If @Measure <> 'TONS'
Begin
INSERT INTO #TempProduction
Select l.Source_Description, a.ShKey, Sum(m.MeasureValue) as MeasureValue, s.Period_day as DateCode
from ALLOCTNTIMESTAMP a Join
MEASURETIMESTAMP m ON m.TSKey = a.TSKey join
V_Locations l ON l.Source_Code = a.Location join
#TempShift s ON s.ShiftCode = a.ShKey Join
V_MeasCode ON V_MeasCode.Measure_Description = m.MeasCode join
V_Equipment e ON e.EquipmentCode = a.Equipment Join
V_Equipment_Model em ON em.Equipment_Model_Code = e.Equipment_Model_Code join
V_Equipment_Function ef ON ef.Equipment_Function_Code = em.Equipment_Model_Function_Code
Where (( Measure_Description = N'Bags' ) )
Group by l.Source_Description, a.ShKey, m.MeasureValue, s.Period_day
Order by l.Source_Description
End
Fitz
May 3, 2012 at 2:35 am
Two options spring to mind
As your using an IF you obviously only want one query to be executed
DROP Table #TempProduction before the next IF is evaluated then create it in the next query
Group by l.Source_Description, a.ShKey, m.MeasureValue, s.Period_day
Order by l.Source_Description
DROP TABLE #TempProduction
End
If @Measure <> 'TONS'
Begin
Or in your second query rename the #TempProduction to something else
Andy
==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe
May 3, 2012 at 2:56 am
Mark Fitzgerald-331224 (5/3/2012)
Use a CREATE TABLE statement to create the temp table before the IF and then use INSERT INTO.
Declare @Measure nvarchar (50)
create table #TempProduction
(
Source_Description <<datatype>>,
ShKey <<datatype>>,
MeasureValue <<datatype>>,
DateCode <<datatype>>
)
Set @Measure = (Select MeasureCode from v_meascode where (( Measure_Description = N'Bags' ) ))
If @Measure = 'TONS'
Begin
INSERT INTO #TempProduction
Select l.Source_Description, a.ShKey, Sum(m.MeasureValue) as MeasureValue, s.Period_day as DateCode
from ALLOCTNTIMESTAMP a Join
MEASURETIMESTAMP m ON m.TSKey = a.TSKey join
V_Locations l ON l.Source_Code = a.Location join
#TempShift s ON s.ShiftCode = a.ShKey Join
V_MeasCode ON V_MeasCode.Measure_Description = m.MeasCode join
V_Equipment e ON e.EquipmentCode = a.Equipment Join
V_Equipment_Model em ON em.Equipment_Model_Code = e.Equipment_Model_Code join
V_Equipment_Function ef ON ef.Equipment_Function_Code = em.Equipment_Model_Function_Code
Where (( Measure_Description = N'Tons' ) ) and ef.Equipment_Function_Description = 'Loading'
Group by l.Source_Description, a.ShKey, m.MeasureValue, s.Period_day
Order by l.Source_Description
End
If @Measure <> 'TONS'
Begin
INSERT INTO #TempProduction
Select l.Source_Description, a.ShKey, Sum(m.MeasureValue) as MeasureValue, s.Period_day as DateCode
from ALLOCTNTIMESTAMP a Join
MEASURETIMESTAMP m ON m.TSKey = a.TSKey join
V_Locations l ON l.Source_Code = a.Location join
#TempShift s ON s.ShiftCode = a.ShKey Join
V_MeasCode ON V_MeasCode.Measure_Description = m.MeasCode join
V_Equipment e ON e.EquipmentCode = a.Equipment Join
V_Equipment_Model em ON em.Equipment_Model_Code = e.Equipment_Model_Code join
V_Equipment_Function ef ON ef.Equipment_Function_Code = em.Equipment_Model_Function_Code
Where (( Measure_Description = N'Bags' ) )
Group by l.Source_Description, a.ShKey, m.MeasureValue, s.Period_day
Order by l.Source_Description
End
Fitz
Cool seems to work but I do get:
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation
Lower down in my query. Believe this is because of the data type I use when I create the table. Everything is like it should be except the ShKey. I believe it has to be
TokenCode:nvarchar(20)
But doesn't accept it when I create the table.
May 3, 2012 at 2:58 am
Andy Hyslop (5/3/2012)
Two options spring to mindAs your using an IF you obviously only want one query to be executed
DROP Table #TempProduction before the next IF is evaluated then create it in the next query
Group by l.Source_Description, a.ShKey, m.MeasureValue, s.Period_day
Order by l.Source_Description
DROP TABLE #TempProduction
End
If @Measure <> 'TONS'
Begin
Changing the name is not an option.
I did try to rename it to Production1 and production 2 and then just Union them. Didn't want to work (Don't think there's a way to change a temp table name.)
Or in your second query rename the #TempProduction to something else
Andy
May 3, 2012 at 3:02 am
Andy Hyslop (5/3/2012)
Two options spring to mindAs your using an IF you obviously only want one query to be executed
DROP Table #TempProduction before the next IF is evaluated then create it in the next query
It's not an execution time error, it's a parse-time error. The parser doesn't execute the IF statements, it doesn't evaluate (or care) which branch the query will run, it just parses the entire query. It sees two statements that will create the same table, and that is an error.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 3, 2012 at 3:02 am
Ok so lower down I have this:
Select Budget_Group,BudgetItem_Name,BudgetedValue,MeasureValue, RIGHT(shift,1)+DATENAME(DW,DateCode) as ShiftValue , b.Location
Into #TempFinal
From #TempProduction p join
#TempBudget b on b.Shift = p.ShKey and b.location = p.Source_description
Group By Budget_Group,BudgetItem_Name,Shift,BudgetedValue,MeasureValue, DateCode, b.Location
Order by Budget_Group, BudgetItem_Name
I changed it to:
Select Budget_Group,BudgetItem_Name,BudgetedValue,MeasureValue, RIGHT(shift,1)+DATENAME(DW,DateCode) as ShiftValue , b.Location
Into #TempFinal
From #TempProduction p join
#TempBudget b on b.Shift COLLATE DATABASE_DEFAULT = p.ShKey COLLATE DATABASE_DEFAULT and b.location COLLATE DATABASE_DEFAULT = p.Source_description COLLATE DATABASE_DEFAULT
Group By Budget_Group,BudgetItem_Name,Shift,BudgetedValue,MeasureValue, DateCode, b.Location
Order by Budget_Group, BudgetItem_Name
And it is working. Is there any disadvantage from this?
May 3, 2012 at 3:03 am
renvilo (5/3/2012)
Mark Fitzgerald-331224 (5/3/2012)
Use a CREATE TABLE statement to create the temp table before the IF and then use INSERT INTO.
Declare @Measure nvarchar (50)
create table #TempProduction
(
Source_Description <<datatype>>,
ShKey <<datatype>>,
MeasureValue <<datatype>>,
DateCode <<datatype>>
)
Set @Measure = (Select MeasureCode from v_meascode where (( Measure_Description = N'Bags' ) ))
If @Measure = 'TONS'
Begin
INSERT INTO #TempProduction
Select l.Source_Description, a.ShKey, Sum(m.MeasureValue) as MeasureValue, s.Period_day as DateCode
from ALLOCTNTIMESTAMP a Join
MEASURETIMESTAMP m ON m.TSKey = a.TSKey join
V_Locations l ON l.Source_Code = a.Location join
#TempShift s ON s.ShiftCode = a.ShKey Join
V_MeasCode ON V_MeasCode.Measure_Description = m.MeasCode join
V_Equipment e ON e.EquipmentCode = a.Equipment Join
V_Equipment_Model em ON em.Equipment_Model_Code = e.Equipment_Model_Code join
V_Equipment_Function ef ON ef.Equipment_Function_Code = em.Equipment_Model_Function_Code
Where (( Measure_Description = N'Tons' ) ) and ef.Equipment_Function_Description = 'Loading'
Group by l.Source_Description, a.ShKey, m.MeasureValue, s.Period_day
Order by l.Source_Description
End
If @Measure <> 'TONS'
Begin
INSERT INTO #TempProduction
Select l.Source_Description, a.ShKey, Sum(m.MeasureValue) as MeasureValue, s.Period_day as DateCode
from ALLOCTNTIMESTAMP a Join
MEASURETIMESTAMP m ON m.TSKey = a.TSKey join
V_Locations l ON l.Source_Code = a.Location join
#TempShift s ON s.ShiftCode = a.ShKey Join
V_MeasCode ON V_MeasCode.Measure_Description = m.MeasCode join
V_Equipment e ON e.EquipmentCode = a.Equipment Join
V_Equipment_Model em ON em.Equipment_Model_Code = e.Equipment_Model_Code join
V_Equipment_Function ef ON ef.Equipment_Function_Code = em.Equipment_Model_Function_Code
Where (( Measure_Description = N'Bags' ) )
Group by l.Source_Description, a.ShKey, m.MeasureValue, s.Period_day
Order by l.Source_Description
End
Fitz
Cool seems to work but I do get:
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation
Lower down in my query. Believe this is because of the data type I use when I create the table. Everything is like it should be except the ShKey. I believe it has to be
TokenCode:nvarchar(20)
But doesn't accept it when I create the table.
Check the collation on the source database/column. The conflict is that the TEMPDB will use the servers collation (i.e. in the CREATE TABLE nvarchar()) and the source table has a different collation for that column. Use a COLLATE item at the end of the nvarchar() to whichever puts it right.
Fitz
May 3, 2012 at 3:03 am
renvilo (5/3/2012)
Cool seems to work but I do get:Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation
Lower down in my query. Believe this is because of the data type I use when I create the table. Everything is like it should be except the ShKey. I believe it has to be
TokenCode:nvarchar(20)
But doesn't accept it when I create the table.
You can explicitly set the collation when you create the temp table. Post the create table that you have, and we'll point out what needs changing.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 3, 2012 at 3:05 am
GilaMonster (5/3/2012)
renvilo (5/3/2012)
Cool seems to work but I do get:Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation
Lower down in my query. Believe this is because of the data type I use when I create the table. Everything is like it should be except the ShKey. I believe it has to be
TokenCode:nvarchar(20)
But doesn't accept it when I create the table.
You can explicitly set the collation when you create the temp table. Post the create table that you have, and we'll point out what needs changing.
This is my temp table:
create table #TempProduction
(
Source_Description VarChar(100),
ShKey nvarchar(20),
MeasureValue float,
DateCode nVarChar(50)
)
May 3, 2012 at 3:05 am
GilaMonster (5/3/2012)
Andy Hyslop (5/3/2012)
Two options spring to mindAs your using an IF you obviously only want one query to be executed
DROP Table #TempProduction before the next IF is evaluated then create it in the next query
It's not an execution time error, it's a parse-time error. The parser doesn't execute the IF statements, it doesn't evaluate (or care) which branch the query will run, it just parses the entire query. It sees two statements that will create the same table, and that is an error.
Yea also thought it was n parse error but is there a way to execute without parse?
May 3, 2012 at 3:06 am
It's not an execution time error, it's a parse-time error. The parser doesn't execute the IF statements, it doesn't evaluate (or care) which branch the query will run, it just parses the entire query. It sees two statements that will create the same table, and that is an error.
Ahh I missed that - thanks Gail
Andy
==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe
May 3, 2012 at 3:06 am
What's the default collation of your database?
What's the default collation of TempDB?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 3, 2012 at 3:08 am
GilaMonster (5/3/2012)
What's the default collation of your database?What's the default collation of TempDB?
Database Default - Latin1_General_CI_AS
Didn't set any for the TempDB
May 3, 2012 at 3:12 am
renvilo (5/3/2012)
GilaMonster (5/3/2012)
Andy Hyslop (5/3/2012)
Two options spring to mindAs your using an IF you obviously only want one query to be executed
DROP Table #TempProduction before the next IF is evaluated then create it in the next query
It's not an execution time error, it's a parse-time error. The parser doesn't execute the IF statements, it doesn't evaluate (or care) which branch the query will run, it just parses the entire query. It sees two statements that will create the same table, and that is an error.
Yea also thought it was n parse error but is there a way to execute without parse?
No.
Query execution phases are parse, bind, optimise, execute.
There are ways around the error, the best is to create the temp table upfront then populate it in the if statement.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply