这些数据不会有问题select sum(cast(a as float))sa from ( select '.0023' a union all select '1.' ) t /* sa ---------------------- 1.0023 */select * from DATA0050 where isnumeric(ANALYSIS_CODE_3)=0
加上isnull就可以了: SUM(isnull(CAST(DATA0050.ANALYSIS_CODE_3 AS FLOAT),0) TOTAL_WEIGHT
我本地都是可以的,sql server 2008 CREATE TABLE #tp (ANALYSIS_CODE_3 DECIMAL(10,6))INSERT INTO #tp select 0.004400 INSERT INTO #tp select 0.016880 INSERT INTO #tp select 0.073200 INSERT INTO #tp select 0.028800 INSERT INTO #tp select 0.028800 INSERT INTO #tp select 0.069460 INSERT INTO #tp select 0.069460 INSERT INTO #tp select NULL INSERT INTO #tp select NULL INSERT INTO #tp select NULL INSERT INTO #tp select NULL INSERT INTO #tp select 0.134260SELECT * FROM #tp tselect SUM(isnull(CAST(ANALYSIS_CODE_3 AS FLOAT),0)) TOTAL_WEIGHT FROM #tp tselect sum(CAST(isnull(ANALYSIS_CODE_3,0) AS FLOAT)) TOTAL_WEIGHT FROM #tp tTOTAL_WEIGHT ---------------------- 0.42526(1 row(s) affected)TOTAL_WEIGHT ---------------------- 0.42526(1 row(s) affected)
use test go if object_id('test.dbo.tb') is not null drop table tb -- 创建数据表 create table tb ( num float ) go --插入测试数据 insert into tb select '0.004400' union all select '0.016880' union all select 0.073200 union all select 0.028800 union all select '0.028800' union all select '.069460' union all select .069460 union all select NULL union all select NULL union all select NULL union all select NULL union all select '0.134260'go --代码实现select num=cast(num as float) from tb /* num ------------- 0.0044 0.01688 0.0732 0.0288 0.0288 0.06946 0.06946 NULL NULL NULL NULL 0.13426 */ select num=sum(cast(num as float)) from tb /* num ----------- 0.42526 */'建议楼主换台装有 SQL 的电脑试试...'
ALTER PROCEDURE [dbo].[USP_REPORT289] ( @CUSTOMER_PART_NUMBER CHAR(20)='', @CP_REV CHAR(5)='', @DEPT_CODE CHAR(10)='', @DEPT_NAME CHAR(30)='', @TTYPE INT=0, --0 ALL,1 Nomal,2 SL @CUST_CODE CHAR(10)='' ) AS SET NOCOUNT ON CREATE TABLE #D0056 ( WO_PTR INT, STEP INT, DEPT_CODE CHAR(10), DEPT_NAME CHAR(30), QTY_BACKLOG INT, QTY_REJECTED INT )INSERT INTO #D0056 ( WO_PTR, STEP, DEPT_CODE, DEPT_NAME, QTY_BACKLOG, QTY_REJECTED ) SELECT WO_PTR, STEP, DATA0034.DEPT_CODE, RTRIM(LTRIM(DATA0034.DEPT_NAME))+(LTRIM(RTRIM(ISNULL(DBO.FN_WIP_DEPT_SPECIALLY_REQUESTS(DATA0056.WO_PTR,DATA0056.D_G_W_PTR,DATA0056.STEP),'')))) DEPT_NAME, --CASE WHEN DATA0034.RKEY = 428 THEN 'I05' ELSE DATA0034.DEPT_CODE END DEPT_CODE, --CASE WHEN DATA0034.RKEY = 428 THEN '锣板' ELSE RTRIM(LTRIM(DATA0034.DEPT_NAME))+(LTRIM(RTRIM(ISNULL(DBO.FN_WIP_DEPT_SPECIALLY_REQUESTS(DATA0056.WO_PTR,DATA0056.D_G_W_PTR,DATA0056.STEP),'')))) end DEPT_NAME, QTY_BACKLOG, QTY_REJECTED FROM DATA0056 WITH(NOLOCK) JOIN DATA0034 WITH(NOLOCK) ON DATA0034.RKEY = DATA0056.D_G_W_PTR WHERE DATA0056.QTY_BACKLOG<>0 AND DATA0034.DEPT_CODE LIKE '%'+RTRIM(@DEPT_CODE)+'%' AND DATA0034.DEPT_NAME LIKE '%'+RTRIM(@DEPT_NAME)+'%' CREATE TABLE #TMP ( DEPT_NAME CHAR(30), CUSTOMER_PART_NUMBER CHAR(20), CP_REV CHAR(5), INV_PART_NUMBER CHAR(20), UNIT_CODE CHAR(3), QTY_BACKLOG INT, QTY_REJECTED INT, PCSH INT, BACKLOG_PCSH NUMERIC(13,2), PCWP INT, BACKLOG_WP NUMERIC(13,2), PCA NUMERIC(13,4), BACKLOG_FT2 NUMERIC(13,2), SINGLE_WEIGHT NUMERIC(13,6), TOTAL_WEIGHT NUMERIC(13,2) )IF @TTYPE = 0 BEGIN INSERT INTO #TMP ( DEPT_NAME, CUSTOMER_PART_NUMBER, CP_REV, INV_PART_NUMBER, UNIT_CODE, QTY_BACKLOG, QTY_REJECTED, PCSH, BACKLOG_PCSH, PCWP, BACKLOG_WP, PCA, BACKLOG_FT2, SINGLE_WEIGHT, TOTAL_WEIGHT ) SELECT --#TMP.DEPT_CODE, #D0056.DEPT_NAME, DATA0050.CUSTOMER_PART_NUMBER, DATA0050.CP_REV, DATA0017.INV_PART_NUMBER, 'PCS' UNIT_CODE, SUM(QTY_BACKLOG) QTY_BACKLOG, SUM(QTY_REJECTED) QTY_REJECTED, DATA0047_1.UNIT_VALUE*DATA0047_2.UNIT_VALUE PCSH, SUM(CASE WHEN DATA0047_1.UNIT_VALUE*DATA0047_2.UNIT_VALUE<>0 THEN #D0056.QTY_BACKLOG/(DATA0047_1.UNIT_VALUE*DATA0047_2.UNIT_VALUE) ELSE 0 END) BACKLOG_PCSH, DATA0047_1.UNIT_VALUE PCWP, SUM(CASE WHEN DATA0047_1.UNIT_VALUE<> 0 THEN #D0056.QTY_BACKLOG/DATA0047_1.UNIT_VALUE ELSE 0 END) BACKLOG_WP, DATA0050.REPORT_UNIT_VALUE3 PCA, SUM(#D0056.QTY_BACKLOG*DATA0050.REPORT_UNIT_VALUE3) BACKLOG_FT2, DATA0050.ANALYSIS_CODE_3 AS SINGLE_WEIGHT, CAST(DATA0050.ANALYSIS_CODE_3 AS FLOAT)TOTAL_WEIGHT --CASE WHEN ISNUMERIC(DATA0050.ANALYSIS_CODE_3)=1 THEN SUM(QTY_BACKLOG*CAST(DATA0050.ANALYSIS_CODE_3 AS FLOAT)) ELSE 0 END TOTAL_WEIGHT FROM #D0056 JOIN DATA0006 WITH(NOLOCK) ON DATA0006.RKEY = #D0056.WO_PTR LEFT JOIN DATA0017 WITH(NOLOCK) ON DATA0017.RKEY = DATA0006.INVENTORY_PTR JOIN ( SELECT * FROM DATA0050 WITH(NOLOCK) WHERE RKEY = PRODUCTION_PART_PTR AND CUSTOMER_PART_NUMBER LIKE '%'+RTRIM(@CUSTOMER_PART_NUMBER)+'%' AND CP_REV LIKE '%'+RTRIM(@CP_REV)+'%' )DATA0050 ON DATA0050.RKEY = DATA0006.CUST_PART_PTR JOIN DATA0010 WITH(NOLOCK) ON DATA0010.RKEY = DATA0050.CUSTOMER_PTR JOIN DATA0047 DATA0047_1 WITH(NOLOCK) ON (DATA0047_1.TTYPE=2) AND (DATA0047_1.SOURCE_POINTER=DATA0050.RKEY) AND (DATA0047_1.UNIT_POINTER=4 ) JOIN DATA0047 DATA0047_2 WITH(NOLOCK) ON (DATA0047_2.TTYPE=2) AND (DATA0047_2.SOURCE_POINTER=DATA0050.RKEY) AND (DATA0047_2.UNIT_POINTER=66 ) WHERE DATA0010.CUST_CODE LIKE '%'+RTRIM(@CUST_CODE)+'%' AND DATA0006.PROD_STATUS IN(3) GROUP BY DATA0050.CUSTOMER_PART_NUMBER, DATA0050.CP_REV, DATA0017.INV_PART_NUMBER, #D0056.DEPT_CODE, #D0056.DEPT_NAME, DATA0047_1.UNIT_VALUE, DATA0047_1.UNIT_VALUE*DATA0047_2.UNIT_VALUE, DATA0050.REPORT_UNIT_VALUE3, DATA0050.ANALYSIS_CODE_3这是我的SQL, 电脑没问题,按楼上的也可以。 CAST(DATA0050.ANALYSIS_CODE_3 AS FLOAT)TOTAL_WEIGHT 这样是可行的, SUM(CAST(DATA0050.ANALYSIS_CODE_3 AS FLOAT))WEIGHT 这样就报那错了。已经把 DATA0050.ANALYSIS_CODE_3 的查询出来的结果看了下,没有发现数据问题。
SUM(QTY_BACKLOG*CAST(DATA0050.ANALYSIS_CODE_3 AS FLOAT)) ================================= 这地方就是你使用的吧? 第一查看QTY_BACKLOG内容是否有异常 第二,CAST(DATA0050.ANALYSIS_CODE_3 AS FLOAT)里面的转换应该是没有问题的最好把你的错误消息贴出来看看。
JOIN ( SELECT * FROM DATA0050 WITH(NOLOCK) WHERE RKEY = PRODUCTION_PART_PTR AND CUSTOMER_PART_NUMBER LIKE '%'+RTRIM(@CUSTOMER_PART_NUMBER)+'%' AND CP_REV LIKE '%'+RTRIM(@CP_REV)+'%' AND ISNUMERIC(ANALYSIS_CODE_3)=1 -- try )DATA0050 ON DATA0050.RKEY = DATA0006.CUST_PART_PTR
SUM(CAST(DATA0050.ANALYSIS_CODE_3 AS FLOAT))WEIGHT GROUP BY DATA0050.ANALYSIS_CODE_3 ---- 什么意思呢?
不应该是加上 sum 才会出现“从数据类型 varchar 转换为 float 时出错”这样的错误!应该是其他什么地方出问题了!
是可以用sum的你这个里面可能有些字段信息转换有问题
0.004400
0.016880
0.073200
0.028800
0.028800
0.069460
0.069460
NULL
NULL
NULL
NULL
0.134260
之前发现里面有几条数据是小数点前的0 没有输入成了 .0023 类似这样的数据。
我已经仔细检查了里面的数据,没有发现问题。我那闷了 为什么了 这样可以 CAST(DATA0050.ANALYSIS_CODE_3 AS FLOAT)
加上SUM 就报那错了。
这些数据不会有问题select sum(cast(a as float))sa from
(
select '.0023' a union all
select '1.'
) t
/*
sa
----------------------
1.0023
*/select * from DATA0050 where isnumeric(ANALYSIS_CODE_3)=0
SUM(isnull(CAST(DATA0050.ANALYSIS_CODE_3 AS FLOAT),0) TOTAL_WEIGHT
CREATE TABLE #tp
(ANALYSIS_CODE_3 DECIMAL(10,6))INSERT INTO #tp select 0.004400
INSERT INTO #tp select 0.016880
INSERT INTO #tp select 0.073200
INSERT INTO #tp select 0.028800
INSERT INTO #tp select 0.028800
INSERT INTO #tp select 0.069460
INSERT INTO #tp select 0.069460
INSERT INTO #tp select NULL
INSERT INTO #tp select NULL
INSERT INTO #tp select NULL
INSERT INTO #tp select NULL
INSERT INTO #tp select 0.134260SELECT * FROM #tp tselect SUM(isnull(CAST(ANALYSIS_CODE_3 AS FLOAT),0)) TOTAL_WEIGHT
FROM #tp tselect sum(CAST(isnull(ANALYSIS_CODE_3,0) AS FLOAT)) TOTAL_WEIGHT
FROM #tp tTOTAL_WEIGHT
----------------------
0.42526(1 row(s) affected)TOTAL_WEIGHT
----------------------
0.42526(1 row(s) affected)
go
if object_id('test.dbo.tb') is not null drop table tb
-- 创建数据表
create table tb
(
num float
)
go
--插入测试数据
insert into tb select '0.004400'
union all select '0.016880'
union all select 0.073200
union all select 0.028800
union all select '0.028800'
union all select '.069460'
union all select .069460
union all select NULL
union all select NULL
union all select NULL
union all select NULL
union all select '0.134260'go
--代码实现select num=cast(num as float) from tb
/*
num
-------------
0.0044
0.01688
0.0732
0.0288
0.0288
0.06946
0.06946
NULL
NULL
NULL
NULL
0.13426
*/
select num=sum(cast(num as float)) from tb
/*
num
-----------
0.42526
*/'建议楼主换台装有 SQL 的电脑试试...'
ALTER PROCEDURE [dbo].[USP_REPORT289]
(
@CUSTOMER_PART_NUMBER CHAR(20)='',
@CP_REV CHAR(5)='',
@DEPT_CODE CHAR(10)='',
@DEPT_NAME CHAR(30)='',
@TTYPE INT=0, --0 ALL,1 Nomal,2 SL
@CUST_CODE CHAR(10)=''
)
AS
SET NOCOUNT ON CREATE TABLE #D0056
(
WO_PTR INT,
STEP INT,
DEPT_CODE CHAR(10),
DEPT_NAME CHAR(30),
QTY_BACKLOG INT,
QTY_REJECTED INT
)INSERT INTO #D0056
(
WO_PTR,
STEP,
DEPT_CODE,
DEPT_NAME,
QTY_BACKLOG,
QTY_REJECTED
)
SELECT
WO_PTR,
STEP,
DATA0034.DEPT_CODE,
RTRIM(LTRIM(DATA0034.DEPT_NAME))+(LTRIM(RTRIM(ISNULL(DBO.FN_WIP_DEPT_SPECIALLY_REQUESTS(DATA0056.WO_PTR,DATA0056.D_G_W_PTR,DATA0056.STEP),'')))) DEPT_NAME,
--CASE WHEN DATA0034.RKEY = 428 THEN 'I05' ELSE DATA0034.DEPT_CODE END DEPT_CODE,
--CASE WHEN DATA0034.RKEY = 428 THEN '锣板' ELSE RTRIM(LTRIM(DATA0034.DEPT_NAME))+(LTRIM(RTRIM(ISNULL(DBO.FN_WIP_DEPT_SPECIALLY_REQUESTS(DATA0056.WO_PTR,DATA0056.D_G_W_PTR,DATA0056.STEP),'')))) end DEPT_NAME,
QTY_BACKLOG,
QTY_REJECTED
FROM DATA0056 WITH(NOLOCK)
JOIN DATA0034 WITH(NOLOCK) ON DATA0034.RKEY = DATA0056.D_G_W_PTR
WHERE DATA0056.QTY_BACKLOG<>0
AND DATA0034.DEPT_CODE LIKE '%'+RTRIM(@DEPT_CODE)+'%'
AND DATA0034.DEPT_NAME LIKE '%'+RTRIM(@DEPT_NAME)+'%'
CREATE TABLE #TMP
(
DEPT_NAME CHAR(30),
CUSTOMER_PART_NUMBER CHAR(20),
CP_REV CHAR(5),
INV_PART_NUMBER CHAR(20),
UNIT_CODE CHAR(3),
QTY_BACKLOG INT,
QTY_REJECTED INT,
PCSH INT,
BACKLOG_PCSH NUMERIC(13,2),
PCWP INT,
BACKLOG_WP NUMERIC(13,2),
PCA NUMERIC(13,4),
BACKLOG_FT2 NUMERIC(13,2),
SINGLE_WEIGHT NUMERIC(13,6),
TOTAL_WEIGHT NUMERIC(13,2)
)IF @TTYPE = 0
BEGIN
INSERT INTO #TMP
(
DEPT_NAME,
CUSTOMER_PART_NUMBER,
CP_REV,
INV_PART_NUMBER,
UNIT_CODE,
QTY_BACKLOG,
QTY_REJECTED,
PCSH,
BACKLOG_PCSH,
PCWP,
BACKLOG_WP,
PCA,
BACKLOG_FT2,
SINGLE_WEIGHT,
TOTAL_WEIGHT
)
SELECT
--#TMP.DEPT_CODE,
#D0056.DEPT_NAME,
DATA0050.CUSTOMER_PART_NUMBER,
DATA0050.CP_REV,
DATA0017.INV_PART_NUMBER,
'PCS' UNIT_CODE,
SUM(QTY_BACKLOG) QTY_BACKLOG,
SUM(QTY_REJECTED) QTY_REJECTED,
DATA0047_1.UNIT_VALUE*DATA0047_2.UNIT_VALUE PCSH,
SUM(CASE WHEN DATA0047_1.UNIT_VALUE*DATA0047_2.UNIT_VALUE<>0 THEN #D0056.QTY_BACKLOG/(DATA0047_1.UNIT_VALUE*DATA0047_2.UNIT_VALUE) ELSE 0 END) BACKLOG_PCSH,
DATA0047_1.UNIT_VALUE PCWP,
SUM(CASE WHEN DATA0047_1.UNIT_VALUE<> 0 THEN #D0056.QTY_BACKLOG/DATA0047_1.UNIT_VALUE ELSE 0 END) BACKLOG_WP,
DATA0050.REPORT_UNIT_VALUE3 PCA,
SUM(#D0056.QTY_BACKLOG*DATA0050.REPORT_UNIT_VALUE3) BACKLOG_FT2,
DATA0050.ANALYSIS_CODE_3 AS SINGLE_WEIGHT,
CAST(DATA0050.ANALYSIS_CODE_3 AS FLOAT)TOTAL_WEIGHT
--CASE WHEN ISNUMERIC(DATA0050.ANALYSIS_CODE_3)=1 THEN SUM(QTY_BACKLOG*CAST(DATA0050.ANALYSIS_CODE_3 AS FLOAT)) ELSE 0 END TOTAL_WEIGHT
FROM #D0056
JOIN DATA0006 WITH(NOLOCK) ON DATA0006.RKEY = #D0056.WO_PTR
LEFT JOIN DATA0017 WITH(NOLOCK) ON DATA0017.RKEY = DATA0006.INVENTORY_PTR
JOIN
(
SELECT * FROM DATA0050 WITH(NOLOCK)
WHERE RKEY = PRODUCTION_PART_PTR
AND CUSTOMER_PART_NUMBER LIKE '%'+RTRIM(@CUSTOMER_PART_NUMBER)+'%'
AND CP_REV LIKE '%'+RTRIM(@CP_REV)+'%'
)DATA0050 ON DATA0050.RKEY = DATA0006.CUST_PART_PTR
JOIN DATA0010 WITH(NOLOCK) ON DATA0010.RKEY = DATA0050.CUSTOMER_PTR
JOIN DATA0047 DATA0047_1 WITH(NOLOCK) ON (DATA0047_1.TTYPE=2) AND (DATA0047_1.SOURCE_POINTER=DATA0050.RKEY) AND (DATA0047_1.UNIT_POINTER=4 )
JOIN DATA0047 DATA0047_2 WITH(NOLOCK) ON (DATA0047_2.TTYPE=2) AND (DATA0047_2.SOURCE_POINTER=DATA0050.RKEY) AND (DATA0047_2.UNIT_POINTER=66 )
WHERE DATA0010.CUST_CODE LIKE '%'+RTRIM(@CUST_CODE)+'%'
AND DATA0006.PROD_STATUS IN(3)
GROUP BY
DATA0050.CUSTOMER_PART_NUMBER,
DATA0050.CP_REV,
DATA0017.INV_PART_NUMBER,
#D0056.DEPT_CODE,
#D0056.DEPT_NAME,
DATA0047_1.UNIT_VALUE,
DATA0047_1.UNIT_VALUE*DATA0047_2.UNIT_VALUE,
DATA0050.REPORT_UNIT_VALUE3,
DATA0050.ANALYSIS_CODE_3这是我的SQL, 电脑没问题,按楼上的也可以。
CAST(DATA0050.ANALYSIS_CODE_3 AS FLOAT)TOTAL_WEIGHT 这样是可行的,
SUM(CAST(DATA0050.ANALYSIS_CODE_3 AS FLOAT))WEIGHT 这样就报那错了。已经把 DATA0050.ANALYSIS_CODE_3 的查询出来的结果看了下,没有发现数据问题。
=================================
这地方就是你使用的吧?
第一查看QTY_BACKLOG内容是否有异常
第二,CAST(DATA0050.ANALYSIS_CODE_3 AS FLOAT)里面的转换应该是没有问题的最好把你的错误消息贴出来看看。
(
SELECT * FROM DATA0050 WITH(NOLOCK)
WHERE RKEY = PRODUCTION_PART_PTR
AND CUSTOMER_PART_NUMBER LIKE '%'+RTRIM(@CUSTOMER_PART_NUMBER)+'%'
AND CP_REV LIKE '%'+RTRIM(@CP_REV)+'%'
AND ISNUMERIC(ANALYSIS_CODE_3)=1 -- try
)DATA0050 ON DATA0050.RKEY = DATA0006.CUST_PART_PTR
GROUP BY DATA0050.ANALYSIS_CODE_3
----
什么意思呢?
@CUSTOMER_PART_NUMBER VARCHAR(20)='',
@CP_REV VARCHAR(5)='',
@DEPT_CODE VARCHAR(10),
@DEPT_NAME VARCHAR(30)='',
@TTYPE INT=0, --0 ALL,1 Nomal,2 SL
@CUST_CODE VARCHAR(10)=''
刚刚把储存过程中的查询参数条件改成 VARCHAR 就没问题了。
之前是用的CHAR 类型, 但不知道为什么改成VARCHAR就OK 了?