在我的存储过程中,我从三个表中获取数据,但得到了以下错误:
将数据类型varchar转换为数字格式时出错。
我认为问题在于:-
'NULL' AS DATE (in 1st query of this SP)------(1)
CONVERT(VARCHAR, tbl_IPD.ipd_doa, 103) AS DATE (in 2nd query of this SP)-----(2)
我的问题是:如何将(1)行转换为varchar
SELECT
tbl_CompanyProfile.Company_Name, tbl_CompanyProfile.Company_Address,
tbl_CompanyProfile.Company_ContactNo, tbl_CompanyProfile.Company_Email,
tbl_CompanyProfile.Company_Website,
'NULL' AS NAME, 'NULL' AS DATE,
'NULL' AS AMOUNT, 'NULL' AS DEPARTMENT,
0 AS Age, @from AS StartDate, @to AS EndDate
FROM
tbl_CompanyProfile
UNION ALL
SELECT
'--', '--',
'--', '--', '--',
tbl_IPD.ipd_pfname + ' ' + tbl_IPD.ipd_plname AS NAME,
CONVERT(VARCHAR, tbl_IPD.ipd_doa, 103) AS DATE,
ISNULL(SUM(tbl_fee.fee_amount), 0) AS AMOUNT,
tblDepartment.DeptName AS DEPARTMENT, tbl_IPD.ipd_age AS Age,
@from AS StartDate, @to AS EndDate
FROM
tbl_fee
LEFT OUTER JOIN
tbl_IPD ON tbl_fee.ipd_id = tbl_IPD.ipd_id
LEFT OUTER JOIN
tblDepartment ON tbl_IPD.ipd_dpt = tblDepartment.DeptId
WHERE
(tbl_IPD.ipd_doa BETWEEN @from AND @to)
GROUP BY
tbl_IPD.ipd_pfname, tbl_IPD.ipd_plname, tbl_IPD.ipd_doa, tblDepartment.DeptName, tbl_IPD.ipd_age
发布于 2013-03-18 18:09:51
我认为问题实际上在AMOUNT
列中--您在第一个查询中选择了NULL
(字符串),并试图将其连接到第二个查询中的数字结果。如果要在联合中使用实际的NULL
值,只需去掉NULL
引用中的撇号即可。
发布于 2013-03-18 18:03:40
如果您试图返回一个NULL
值,您应该删除'NULL'
上的引号,否则您将返回一个varchar
,这可能不是您想要做的。
如果我的假设是正确的,那么您的查询应该是这样的。
...NULL AS NAME, NULL AS DATE, NULL AS AMOUNT, NULL AS DEPARTMENT,...
https://stackoverflow.com/questions/15483712
复制相似问题