sql - 从字符串转换为 uniqueidentifier 时转换失败

Question

当我运行我的最终查询时，我收到了上述错误。我已经正确设置了我的表格格式，如果我运行我的 @ProdTotal单独查询我得到正确的数据，如果我运行最终 @Sales_Prod查询不带 pt.Production_Volume .

该错误特别指向 Line 99这是哪里INSERT INTO @Production开始。
如果我的 pt.Production_Volume，为什么我会收到错误消息设置为 DECIMAL(18,6) ?是不是因为我用的是 CASE陈述？

这是我的整个查询:

DECLARE @BeginningDate  datetime
DECLARE @EndingDate     datetime

SET @BeginningDate  =   '03-01-2012'
SET @EndingDate     =   '03-20-2012'

DECLARE @Proc       TABLE
(
Process_Number      UNIQUEIDENTIFIER
,Process_Name       NVARCHAR(50)
)

Insert into @Proc
(
Process_Number      
,Process_Name
)
(
(Select '60203D01-FEAA-4F4B-BFC8-F6C6BD7D9977','Alex.I-Line')
Union (Select '4F980EDF-9EE4-41A1-9DBD-BE12FE227199', 'Alex.Versa Lam')
Union (Select 'B7AB6C1E-2897-4C3F-A6C4-B53E996288ED', 'WCEWP.I-Line')
Union (Select '44A7E9C7-FEE7-4DEA-8826-D10CCCF5FA0F', 'WCEWP.Versa Lam')
Union (Select '079632C7-ADD7-47AF-89AB-B28CDDBA2AD5','StJ.I-Line')
)

--Select * from @Prod
DECLARE @Plant      TABLE
(
Department_Number   UNIQUEIDENTIFIER
,Process_Number     UNIQUEIDENTIFIER
,Process_Name       NVARCHAR(50)
,Plant_Number       UNIQUEIDENTIFIER
,Plant_Name         NVARCHAR(50)
)

INSERT INTO @Plant
(
Department_Number
,Process_Number
,Process_Name
,Plant_Number
,Plant_Name
)
(
SELECT 

d.department_number
,pr.process_number
,pr.Process_name
, p.plant_number
, p.plant_name

FROM trueopportunity.dbo.Process pr
inner join trueopportunity.dbo.department d
on pr.department_number = d.department_number

inner join @Proc pro
on pr.process_number = pro.process_number

inner join trueopportunity.dbo.plant p
on d.plant_number = p.plant_number
)

--SELECT * FROM @Plant

Declare @Product_Name   Table
(
Product_Group_Number    uniqueidentifier
,Product_Name           nvarchar(50)
)

INSERT INTO @Product_Name
(
Product_Group_Number
,Product_Name
)
(
(SELECT 'FE1EC4A8-FEC4-4F45-B74F-72528967DB4B', 'I-Joists ')
UNION(SELECT '0A32AA2B-2630-4090-ADA1-33FAD888FA27', 'Rimboard')
UNION(SELECT '38B67F82-9259-417D-83AF-493CBF953FD1','Versa Lam')
)

--SELECT * FROM @Product_Name

DECLARE @Production     TABLE

(
Production_Process_Number       uniqueidentifier
,Production_Number              uniqueidentifier
,Process_Number                 uniqueidentifier
,Process_Name                   nvarchar(50)
,Plant_Name                     nvarchar(50)
,Plant_Number                   uniqueidentifier
,Production_Volume              decimal(18,6)
,Production_Date                datetime
,Product_Group_Name             nvarchar(50)
)

INSERT INTO @Production
(
Production_Process_Number
,Production_Number  
,Process_Number 
,Process_Name
,Plant_Number
,Plant_Name
,Production_Volume  
,Production_Date
,Product_Group_Name
)
(SELECT

ppf.Production_Process_Number
,ppf.Production_Number
,pl.process_number
,pl.Process_Name 
,pl.plant_name
,Plant_Number
,CASE   
WHEN  (pg.Product_Group_Name) = 'I-Joist' THEN (sum(ppf.Good_Output)) 
ELSE  (sum(ppf.Good_Output_Product_Units))
END as 'Prod Vol'
, pf.date 
, pg.product_group_name

FROM
trueopportunity.dbo.Production_Fact pf
Inner Join trueopportunity.dbo.Production_Process_Fact ppf
on pf.production_number = ppf.production_number

inner join @Plant pl
on pf.process_number = pl.process_number

inner join trueopportunity.dbo.Product prd
on pf.product_number = prd.product_number

inner join trueopportunity.dbo.Product_Group pg
on pg.product_group_number = prd.product_group_number

inner join @Product_Name pn
on pn.product_group_number = prd.product_group_number 

and
pf.date between @BeginningDate and @EndingDate

GROUP BY
Plant_Number
,pl.plant_name
,pg.product_group_name
,prd.product_name 
,pl.Process_Name
,pl.process_number
,ppf.Production_Process_Number
,ppf.Production_Number
,pf.date
)

ORDER BY
pf.date

--SELECT * FROM @Production

DECLARE @ProdTotal      TABLE
(
Plant_Number            UNIQUEIDENTIFIER
,Plant_Name             NVARCHAR(50)
,Production_Volume      DECIMAL(18,6)
,Product_Group_Name     NVARCHAR(50)
) 
Insert into @ProdTotal 
(
Plant_Number    
,Plant_name 
,Production_Volume
,Product_Group_Name
)
(SELECT 
p.plant_number
,p.plant_name
,sum(p.production_volume) 
,p.product_group_name

FROM @Production p

 WHERE Production_date between @BeginningDate and @EndingDate

GROUP BY
p.plant_number
,p.plant_name
,p.product_group_name

)
order by  p.plant_name

--select * from @ProdTotal  

DECLARE @EWPSales       TABLE
(
Plant_Number            UNIQUEIDENTIFIER
,Plant_Code             NVARCHAR(100)
,Process_Number         UNIQUEIDENTIFIER
,Product_Group_Code     NVARCHAR(100)
,Actual_Volume          DECIMAL(18,6)
,Actual_Sales_Dollars   DECIMAL(18,6)
,Avg_Price              DECIMAL(18,6)
,Production_Date        DATETIME
)

INSERT INTO @EWPSales
(
Plant_Number
,Plant_Code 
,Process_Number 
,Product_Group_Code
,Actual_Volume
,Actual_Sales_Dollars
,Avg_Price
,Production_Date
)

(
SELECT
esl.Plant_Number
,esl.Plant_Code 
,esl.Process_Number 
,esl.Product_Group_Code
,(esl.Actual_Volume )
,esl.Actual_Sales_Dollars
,CASE                    
  WHEN   coalesce (esl.Actual_Volume,0) = 0 and
      coalesce (esl.Actual_Sales_Dollars,0) = 0
      THEN 0
      ELSE (sum(esl.Actual_Sales_Dollars)/sum(esl.Actual_Volume))     
  END AS 'AVG PRICE'
,esl.Production_Date

FROM
WOODPRODUCTION.DBO.EWP_Sales esl


Where esl.Production_Date between @BeginningDate and @EndingDate


GROUP BY
esl.Plant_Number
,esl.Plant_Code 
,esl.Process_Number 
,esl.Product_Group_Code
,esl.Actual_Volume
,esl.Actual_Sales_Dollars
,esl.Production_Date
)
ORDER BY
esl.Production_Date 

--Select * from @EWPSales

Declare @Sales_Total        TABLE
(Plant_Number           UNIQUEIDENTIFIER
,Plant_name             NVARCHAR (50)
,Product_Group_Code     NVARCHAR(100)
,Actual_Volume          DECIMAL(18,6)
,Actual_Sales_Dollars   DECIMAL(18,6)
,Avg_Price              DECIMAL(18,6)
,sales_date             DATETIME
)

Insert into @Sales_Total
(Plant_Number 
,Plant_name 
,Product_Group_Code     
,Actual_Volume
,Actual_Sales_Dollars
,Avg_Price
,Sales_Date)

(
SELECT
s.Plant_Number 
,s.Plant_Code 
,s.Product_Group_Code       
,(s.Actual_Volume)
,(s.Actual_Sales_Dollars)
,CASE                    
WHEN coalesce (sum(s.Actual_Volume),0) = 0 
THEN 0
ELSE (sum(s.Actual_Sales_Dollars)/sum(s.Actual_Volume))     
END     
,s.production_Date


FROM @EWPSales s
WHERE s.production_date between @BeginningDate and @EndingDate

GROUP BY
s.plant_number
,s.plant_code
,s.Product_Group_Code   
,(s.Actual_Volume)  
,s.production_date
,s.Actual_Sales_Dollars
,s.avg_price
)

ORDER BY s.production_date
--Select * from @Sales_Total

Declare @Sales_Prod         TABLE
(
Plant_Number                UNIQUEIDENTIFIER
,Plant_Name                 NVARCHAR (50)
,Product_Group_Code         NVARCHAR(100)
,Actual_Volume              DECIMAL(18,6)
,Actual_Sales_Dollars       DECIMAL(18,6)
,Average_Price              DECIMAL(18,6)
,Production_Volume          DECIMAL(18,6)
,Sales_Date                 DATETIME
)

INSERT INTO @Sales_Prod
(
Plant_Number 
,Plant_name 
,Product_Group_Code
,Actual_Volume
,Actual_Sales_Dollars
,Average_Price
,Production_Volume
,Sales_Date
)

(
SELECT 
st.Plant_Number 
,st.Plant_name 
,st.Product_Group_Code  
,st.Actual_Volume
,st.Actual_Sales_Dollars 
,st.Avg_Price  
,pt.Production_Volume
,st.Sales_Date


FROM @Sales_Total st
inner join @EWPSales s
on st.plant_number = s.plant_number

inner join @ProdTotal pt
on pt.plant_number = st.plant_number

and st.sales_date between @BeginningDate and @EndingDate

GROUP BY
st.Plant_Number 
,st.Plant_name 
,st.Product_Group_Code  
,st.Actual_Volume
,st.Actual_Sales_Dollars 
,st.Avg_Price  
,pt.Production_Volume
,st.Sales_Date

)
Order by st.Plant_name, st.sales_date
Select * from @Sales_Prod

tl;dr:我从 INSERT INTO @Production 开始收到上述错误行，我不知道为什么。

提前谢谢大家!

Answer 1

Plant_Number 是根据声明的唯一标识符。但是您正在尝试插入 pl.plant_name 作为 plant_Number。 SELECT 语句中的列顺序错误