sql-server - SQL 存储过程 - 列名无效
问题描述
我正在 SSMS 中构建一个存储过程,但遇到了一堵砖墙。
USE [VINTCMPA]
go
/****** Object: StoredProcedure [dbo].[OpenOrderReport] Script Date: 12/05/2021 12:20:35 ******/
SET ansi_nulls ON
go
SET quoted_identifier ON
go
-- =============================================
-- Author: Bancroft Wines
-- Create date: 12/05/2021
-- Description:
-- =============================================
ALTER PROCEDURE [dbo].[Openorderreport]
-- Add the parameters for the stored procedure here
@startdate DATETIME = NULL,
@enddate DATETIME = NULL
AS
BEGIN
SET @startdate = CONVERT(DATETIME, '2021-04-30 23:59:59', 102)
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET nocount ON;
-- Insert statements for procedure here
SELECT Customers.cust_acc AS CUSTCODE,
[Sales Order Details].vop_ordn AS ORDERNO,
'Invoiced SOP' AS TYPE,
[Sales Order Header].ordidate AS DATE,
Round([Sales Order Details].vol_qtty /
[Sales Order Details].volcsize
*
[Sales Order Details].volcprcn /
[Sales Order Header].ordexch1, 2)
AS VALUE,
Round(Round([Sales Order Details].vol_qtty /
[Sales Order Details].volcsize *
[Sales Order Details].volcprcn /
[Sales Order Header].ordexch1, 2) -
[Sales Order Details].casecost *
[Sales Order Details].vol_qtty
/
[Sales Order Details].volcsize, 2) AS MARGIN
FROM vopoline AS [Sales Order Details]
INNER JOIN voporder AS [Sales Order Header]
ON [Sales Order Details].vop_ordn =
[Sales Order Header].vop_ordn
INNER JOIN vslname1 AS Customers
ON [Sales Order Header].cust_acc = Customers.cust_acc
AND [Sales Order Header].cust_acc = Customers.custaccs
INNER JOIN vstmain1 AS Products
ON [Sales Order Details].prd_code = Products.prd_code
WHERE ( [Sales Order Header].ordidate >=
CONVERT(DATETIME, '2021-04-30 23:59:59', 102)
)
AND ( [Sales Order Header].ordidate <=
CONVERT(DATETIME, '2021-05-12 23:59:59',
102)
)
AND ( [Sales Order Header].ordacode <> 'S' )
AND ( [Sales Order Header].ord_stat = 5 )
AND ( Products.grp_code >= '001' )
AND ( Customers.custanl1 = 'NO' )
OR ( [Sales Order Header].ordidate >=
@startdate
)
AND ( [Sales Order Header].ordidate <=
@enddate
)
AND ( [Sales Order Header].ordacode = 'S' )
AND ( [Sales Order Header].ord_stat = 5 )
AND ( Products.grp_code >= '001' )
AND ( Customers.custanl1 = 'NO' )
AND ( [Sales Order Header].credstyl = 1 )
ORDER BY custcode,
orderno
END
go
引发错误:
列名“VALUE”无效。
我已经定义了 VALUE,所以我不确定为什么它没有检测到它。
编辑:
我重新格式化了代码,现在它没有显示无效列错误,但是它似乎没有向表中输入任何内容。
解决方案
不能引用其他计算列,这select
发生在查询过程的不同阶段并且不可用。您需要重复条件或[value]
在包装第一个条件的外部选择中使用。
如果您将所选列分开到单独的行或至少在逻辑组中,并删除不必要的括号,您会发现更容易阅读。
在你的尝试以下select
Round(Round((([Vol_Qtty]/[VolCsize])*[VolCprcN])/[OrdExch1],2)-([CaseCost]*[Vol_Qtty]/[VolCsize]),2) AS MARGIN
推荐阅读
- powershell - 列出 VM 和分配的 GPU
- python-3.x - 为什么模拟的类型与模拟不匹配?
- ruby-on-rails - 如何在没有root用户的情况下在rails上为ruby运行docker容器?
- r - 如何使用 transition_states() 显示叠加图的过渡?
- c - 浮点值问题以及如何避免函数 f(x) 的无限值
- java - 在调用 oracle 数据库中的存储过程后,HikariCp 连接池无法重用会话
- javascript - 如何在页面刷新时重做方程?
- r - as_factor 函数的因子顺序异常
- azure-active-directory - 使用 msal.js 异步向租户权限和 Microsoft 个人帐户权限进行身份验证时出现 nonce_mismatch_error
- swift - 如何获得特定的日期格式?