CREATE PROCEDURE [dbo].[spReport]
@FromDate DATETIME = NULL,
@ToDate DATETIME = NULL,
@TenantID int ,
@BusinessUnitId int
AS
BEGIN
Declare @listStr Varchar(max), @listValue Varchar(max)
Select
@listStr = COALESCE(@listStr+',' ,'') + FieldLabel
From
(Select Distinct Top 100
FieldLabel, ControlTypeId
From
PaymentCustomFieldDefinitions PCFD
Inner Join
Product P On P.Id = PCFD.ProductId
Where
P.TenantId = @TenantId
Order By
ControlTypeId Desc) R
Set @listStr = ',' + @listStr
Set @listStr = IsNull(@listStr, '')
Select
'Confirmation Number,Business Unit,Bank Account,Merchant Account,Product Name,Payment Date,Payment Time,Total Amount,Status,First Name,Last Name,Payment Method' + @listStr
Union
SELECT
p.ConfirmationNumber + ',' + bu.Name + ',' + p.TenantBankAccountName + ',' + Case When ma.Name IS NULL then '' ELSE ma.Name END + ',' + pd.Name + ',' +
cast(P.PaymentDate as Varchar(11)) + ',' + convert(VARCHAR(8), P.PaymentDate,108) + ',' + Cast(p.TotalDue As Varchar(20)) + ',' + p.PaymentStatusText + ',' + p.PayorFirstName + ',' + p.PayorLastName + ',' + p.PaymentMethodText + ',' + [dbo].[GetPaymentReferenceAndCustomFields](p.Id,@listStr)
FROM
Payment p
INNER JOIN
Product pd ON p.ProductId = pd.Id
INNER JOIN
BusinessUnit bu ON pd.BusinessUnitId = bu.Id
INNER JOIN
ProductDetailPayment pdp ON p.ProductId = pdp.ProductId
LEFT OUTER JOIN
MerchantAccount ma ON ma.Id = pdp.MerchantAccountId
WHERE
p.PaymentDate BETWEEN @FromDate AND @ToDate
AND p.TenantId = @TenantId AND pd.BusinessUnitId= @BusinessUnitId
ORDER BY
p.ProductId desc
END
使用Order by
:
Msg 4104,级别16,状态1,过程spReport,第41行
无法绑定多部分标识符"p.p productid"。
Msg 104,级别16,状态1,过程spQueryPaymentDetailReport,第41行
如果语句中包含UNION、INTERSECT或EXCEPT操作符,则ORDER BY项必须出现在select列表中。
你不能在ORDER BY中引用p.ProductId
,因为第一个子查询没有p
数据集。
您不能在ORDER BY中引用ProductId
列,因为在组合结果集中没有该名称的列。
要解决这些问题,您可以添加一个ProductId
列,并且,如果最终输出必须像在查询中那样包含单个列,则使用派生表:
SELECT
p.CSV
FROM (
Select
ProductId = 2147483647,
CSV = 'Confirmation Number,Business Unit,Bank Account,Merchant Account,Product Name,Payment Date,Payment Time,Total Amount,Status,First Name,Last Name,Payment Method' + @listStr
Union
SELECT
p.ProductId,
p.ConfirmationNumber + ',' + bu.Name + ',' + p.TenantBankAccountName + ',' + Case When ma.Name IS NULL then '' ELSE ma.Name END + ',' + pd.Name + ',' +
cast(P.PaymentDate as Varchar(11)) + ',' + convert(VARCHAR(8), P.PaymentDate,108) + ',' + Cast(p.TotalDue As Varchar(20)) + ',' + p.PaymentStatusText + ',' + p.PayorFirstName + ',' + p.PayorLastName + ',' + p.PaymentMethodText + ',' + [dbo].[GetPaymentReferenceAndCustomFields](p.Id,@listStr)
FROM
Payment p
INNER JOIN
Product pd ON p.ProductId = pd.Id
INNER JOIN
BusinessUnit bu ON pd.BusinessUnitId = bu.Id
INNER JOIN
ProductDetailPayment pdp ON p.ProductId = pdp.ProductId
LEFT OUTER JOIN
MerchantAccount ma ON ma.Id = pdp.MerchantAccountId
WHERE
p.PaymentDate BETWEEN @FromDate AND @ToDate
AND p.TenantId = @TenantId AND pd.BusinessUnitId= @BusinessUnitId
) p
ORDER BY
p.ProductId desc
;
第一行被分配为MaxInt
ID值,因为,据我所知,它是标题行,必须首先根据指定的ORDER BY p.ProductId DESC
子句(并且因为我假设ProductId
是int
,当然)。