列名不正确createDate [英] Incorrect column name createDate
本文介绍了列名不正确createDate的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有以下存储过程。当我运行它并插入我的参数时,它会提示我错误作为上面的标题。以下是我的代码:
Hi, i have the following stored procedure. When i run it and i insert my parameters, it would prompt me the error as title above. Below are my codes:
DECLARE @SQL NVARCHAR(MAX)
DECLARE @params NVARCHAR(MAX)
SELECT @SQL = 'SELECT CustomerName,[0] as Pending, [1] as Save_As_Draft, [2] as Spec_Confirmed, [3] as Request_Revision, [4] as Withdraw, [5] as Pending_RND, [6] as Pending_QC, [7] as Manday_Confirmed, [0]+[1]+[2]+[3]+[4]+[5]+[6]+[7] as Total
FROM
(
SELECT P.DocStatus,C.CustomerName,P.CustomerID, YEAR(CreateDate) as createYear
FROM dbo.CRF_Project P
INNER JOIN dbo.CustomerList C
ON P.CustomerID = C.CustomerID
) p
PIVOT
(
COUNT (createYear) FOR DocStatus IN ([0], [1], [2], [3], [4], [5], [6], [7])
) AS pvt '
SELECT @params = '@CUSTNAME VARCHAR(MAX),
@YEAR VARCHAR(MAX)'
IF(@CUSTNAME != '' AND @YEAR = '')
SELECT @SQL = @SQL + ' WHERE CustomerName IN (' + @CUSTNAME + ')'
ELSE IF(@CUSTNAME = '' AND @YEAR != '')
SELECT @SQL = @SQL + ' WHERE createYear = ' + @YEAR +''
ELSE IF(@CUSTNAME != '' AND @YEAR != '')
SELECT @SQL = @SQL + ' WHERE CustomerName IN (' + @CUSTNAME + ') AND createYear = ' + @YEAR +''
ELSE
SELECT @SQL = @SQL
EXEC sp_executesql @SQL, @params, @CUSTNAME, @YEAR
END
我的输入是@CUSTNAME'Osm'和@YEAR'2014'
my input are @CUSTNAME 'Osm' and @YEAR '2014'
推荐答案
< b>的确,它属于dbo.CRF_Project
告诉SQL:
"indeed, it is belongs to dbo.CRF_Project"
So tell SQL that:
SELECT P.DocStatus,C.CustomerName,P.CustomerID, YEAR(P.CreateDate) as createYear
您不能将现有字段名称用作别名:
You can't use an existing field name as an alias:
SELECT P.DocStatus,C.CustomerName,P.CustomerID, YEAR(CreateDate) as createYear
需要
needs to be
SELECT P.DocStatus,C.CustomerName,P.CustomerID, YEAR(CreateDate) as Create_Year
或类似的东西。此外,看起来您使用的是不区分大小写的SQL版本(这不是批评,只是信息:))
or something similar. Also, it looks like you are using a case-insensitive version of SQL (that isn't a criticism, just information :) )
这篇关于列名不正确createDate的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文