sql-server - 从查询构建视图

Question

我想从 T-SQL 查询构建一个视图。这是查询：

DECLARE @DateFrom datetime
SET @DateFrom = GETDATE() --Replace GETDATE() with the date in the format (with single speech marks) '2010-01-31 00:00:00.000' 

DECLARE @DateTo datetime
SET @DateTo = GETDATE() --Replace GETDATE() with the date in the format (with single speech marks) '2010-01-31 00:00:00.000' 

DECLARE @OrdType int
SET @OrdType = 0 --Replace the 0 with your search integer

DECLARE @AllType bit
SET @AllType = 0 --Replace the 0 with a 1 to search for true values

DECLARE @SalesRep int
SET @SalesRep = 0 --Replace the 0 with your search integer

DECLARE @AllSalesReps bit
SET @AllSalesReps = 0 --Replace the 0 with a 1 to search for true values

DECLARE @Team varchar(150)
SET @Team = '' --Put your search string in the single speech marks

DECLARE @AllTeam bit
SET @AllTeam = 0 --Replace the 0 with a 1 to search for true values

DECLARE @CustCode varchar(150)
SET @CustCode = '' --Put your search string in the single speech marks

DECLARE @AllCust bit
SET @AllCust = 0 --Replace the 0 with a 1 to search for true values

SELECT '10-0017-006 - Orders Taken By User Team Report' As Filename, 
customer_detail.cd_id As CustomerID, 
customer_detail.cd_ow_account As CustomerCode, 
order_header_detail.ohd_customer_name As CustomerName, 
customer_type.ct_name As CustomerType, 
customer_source.cs_name As CustomerSource, 
order_header.oh_order_number As OrderNo, 
order_header.oh_cust_order_ref As CustOrderRef, 
order_header.oh_datetime As OrderDate, 
order_header.oh_sot_id As SystemOrderTypeID, 
system_order_type.sot_description As SystemOrderType, 
order_type.ot_description As OrderType, 
order_header.oh_ec_order_number As OrderHeaderECommerceNo, 
order_header.oh_pos_mode As POSMode, 
user_detail.ud_username As OrderSalesRep, 
user_team.ut_node_path as 'RepTeam',
order_status.os_description As OrderStatus, 
Case When order_header.oh_sot_id in (4,9) Then -1 Else 1 End * order_header_total.oht_goods_net As OrderGoodsNet, 
Case When order_header.oh_sot_id in (4,9) Then -1 Else 1 End * order_header_total.oht_dissur_net As OrderDiscountSurchargeNet, 
Case When order_header.oh_sot_id in (4,9) Then -1 Else 1 End * order_header_total.oht_del_net As OrderDelNet, 
Case When order_header.oh_sot_id in (4,9) Then -1 Else 1 End * order_header_total.oht_net As OrderNet, 
Case When order_header.oh_sot_id in (4,9) Then -1 Else 1 End * order_header_total.oht_vat As OrderVAT, 
Case When order_header.oh_sot_id in (4,9) Then -1 Else 1 End * order_header_total.oht_gross As OrderGross, 
Case When order_header.oh_sot_id in (4,9) Then -1 Else 1 End * order_header_total.oht_total_cost As OrderActualCost, 
Case When order_header.oh_sot_id in (4,9) Then -1 Else 1 End * order_header_total.oht_total_margin As OrderActualMargin 

FROM customer_detail 
Inner Join order_header On customer_detail.cd_id = order_header.oh_cd_id 
Inner Join order_header_total On order_header.oh_id = order_header_total.oht_oh_id 
Inner Join order_header_detail On order_header.oh_id = order_header_detail.ohd_oh_id 
Inner Join system_order_type On system_order_type.sot_id = order_header.oh_sot_id 
Inner Join order_status On order_status.os_id = order_header.oh_os_id 
Left Join order_header_analysis On order_header.oh_id = order_header_analysis.oha_oh_id 
Inner Join order_type On order_header.oh_ot_id = order_type.ot_id 
Left Join order_customer_analysis On order_header.oh_id = order_customer_analysis.oca_oh_id 
Left Join order_delivery_analysis On order_header.oh_id = order_delivery_analysis.oda_oh_id 
Left Join user_detail On order_header_detail.ohd_sales_rep = user_detail.ud_id 
Inner Join customer_profile On customer_detail.cd_id = customer_profile.cp_customer_id 
Inner Join customer_source On customer_profile.cp_source_id = customer_source.cs_id 
Inner Join customer_type On customer_profile.cp_type_id = customer_type.ct_id 
left join user_team on user_team.ut_id = user_detail.ud_ut_id

WHERE (customer_detail.cd_ow_account = @CustCode Or @AllCust = 1) 
And order_header.oh_datetime >= @DateFrom 
And (order_header.oh_sot_id = @OrdType Or @AllType = 1) 
And (order_header_detail.ohd_sales_rep = @SalesRep Or @AllSalesReps = 1) 
And order_header.oh_datetime <= @DateTo 
And order_header.oh_sot_id <> 10
AND (user_team.ut_node_path like @Team + '%' OR @AllTeam = 1)

这是我的方法。使用 Oleg Melnikov [ Create View - Declare a variable提供的解决方案

CREATE VIEW vwVariantbyUserReportandInputDate
AS
WITH params AS (SELECT  DateFrom = GETDATE(), DateTO = GETDATE(), OrdType = 0, AllType = 0, SalesRep = 0, AllSalesReps = 0, Team = '', AllTeam = 0, CustCode = '', AllCust = 0)

SELECT '10-0017-006 - Orders Taken By User Team Report' As Filename, 
customer_detail.cd_id As CustomerID, 
customer_detail.cd_ow_account As CustomerCode, 
order_header_detail.ohd_customer_name As CustomerName, 
customer_type.ct_name As CustomerType, 
customer_source.cs_name As CustomerSource, 
order_header.oh_order_number As OrderNo, 
order_header.oh_cust_order_ref As CustOrderRef, 
order_header.oh_datetime As OrderDate, 
order_header.oh_sot_id As SystemOrderTypeID, 
system_order_type.sot_description As SystemOrderType, 
order_type.ot_description As OrderType, 
order_header.oh_ec_order_number As OrderHeaderECommerceNo, 
order_header.oh_pos_mode As POSMode, 
user_detail.ud_username As OrderSalesRep, 
user_team.ut_node_path as 'RepTeam',
order_status.os_description As OrderStatus, 
Case When order_header.oh_sot_id in (4,9) Then -1 Else 1 End * order_header_total.oht_goods_net As OrderGoodsNet, 
Case When order_header.oh_sot_id in (4,9) Then -1 Else 1 End * order_header_total.oht_dissur_net As OrderDiscountSurchargeNet, 
Case When order_header.oh_sot_id in (4,9) Then -1 Else 1 End * order_header_total.oht_del_net As OrderDelNet, 
Case When order_header.oh_sot_id in (4,9) Then -1 Else 1 End * order_header_total.oht_net As OrderNet, 
Case When order_header.oh_sot_id in (4,9) Then -1 Else 1 End * order_header_total.oht_vat As OrderVAT, 
Case When order_header.oh_sot_id in (4,9) Then -1 Else 1 End * order_header_total.oht_gross As OrderGross, 
Case When order_header.oh_sot_id in (4,9) Then -1 Else 1 End * order_header_total.oht_total_cost As OrderActualCost, 
Case When order_header.oh_sot_id in (4,9) Then -1 Else 1 End * order_header_total.oht_total_margin As OrderActualMargin 

FROM customer_detail  cross apply params
Inner Join order_header On customer_detail.cd_id = order_header.oh_cd_id 
Inner Join order_header_total On order_header.oh_id = order_header_total.oht_oh_id 
Inner Join order_header_detail On order_header.oh_id = order_header_detail.ohd_oh_id 
Inner Join system_order_type On system_order_type.sot_id = order_header.oh_sot_id 
Inner Join order_status On order_status.os_id = order_header.oh_os_id 
Left Join order_header_analysis On order_header.oh_id = order_header_analysis.oha_oh_id 
Inner Join order_type On order_header.oh_ot_id = order_type.ot_id 
Left Join order_customer_analysis On order_header.oh_id = order_customer_analysis.oca_oh_id 
Left Join order_delivery_analysis On order_header.oh_id = order_delivery_analysis.oda_oh_id 
Left Join user_detail On order_header_detail.ohd_sales_rep = user_detail.ud_id 
Inner Join customer_profile On customer_detail.cd_id = customer_profile.cp_customer_id 
Inner Join customer_source On customer_profile.cp_source_id = customer_source.cs_id 
Inner Join customer_type On customer_profile.cp_type_id = customer_type.ct_id 
left join user_team on user_team.ut_id = user_detail.ud_ut_id
WHERE (customer_detail.cd_ow_account = CustCode Or AllCust = 1) 
And order_header.oh_datetime >= DateFrom 
And (order_header.oh_sot_id = OrdType Or AllType = 1) 
And (order_header_detail.ohd_sales_rep = SalesRep Or AllSalesReps = 1) 
And order_header.oh_datetime <= DateTo 
And order_header.oh_sot_id <> 10
AND (user_team.ut_node_path like Team + '%' OR AllTeam = 1)

我没有得到想要的结果。有什么想法吗？

这是脚本和错误

CREATE OR ALTER FUNCTION [dbo].[OrderDetailedMarginByCustomerReport] ( @DateFrom datetime = '2006-01-01T00:00:00.000', @DateTo datetime = '2006-01-01T00:00:00.000', @OrdType int = '0', @AllType bit = '0', @SalesRep int = '0', @AllSalesReps bit ='0', @Team varchar(150) = '', @AllTeam bit ='0', @CustCode varchar(150) = '', @AllCust bit ='0', @StocLoc int = '0', @AllStocLoc bit ='0', @CatPath varchar(150) = '', @AllCat bit = '', @VarCode varchar(150) = '', @AllVar bit = '0'  )
RETURNS TABLE 
WITH SCHEMABINDING AS 
RETURN (
SELECT customer_detail.cd_id As CustomerID, 
customer_detail.cd_ow_account As CustomerCode, 
order_header_detail.ohd_customer_name As CustomerName, 
customer_type.ct_name As CustomerType, 
customer_source.cs_name As CustomerSource, 
order_header.oh_order_number As OrderNo, 
order_header.oh_cust_order_ref As CustOrderRef, 
order_header.oh_datetime As OrderDate, 
order_header.oh_sot_id As SystemOrderTypeID, 
system_order_type.sot_description As SystemOrderType, 
order_type.ot_description As OrderType, 
order_header.oh_ec_order_number As OrderHeaderECommerceNo, 
order_header.oh_pos_mode As POSMode, 
user_detail.ud_username As OrderSalesRep, 
user_team.ut_description as 'RepTeam',
order_status.os_description As OrderStatus, 
Case When order_header.oh_sot_id = 4 Then order_header_total.oht_goods_net * -1 When order_header.oh_sot_id = 9 Then order_header_total.oht_goods_net * -1 Else order_header_total.oht_goods_net End As OrderGoodsNet, 
Case When order_header.oh_sot_id = 4 Then order_header_total.oht_dissur_net * -1 When order_header.oh_sot_id = 9 Then order_header_total.oht_dissur_net * -1 Else order_header_total.oht_dissur_net End As OrderDiscountSurchargeNet,
Case When order_header.oh_sot_id = 4 Then order_header_total.oht_del_net * -1 When order_header.oh_sot_id = 9 Then order_header_total.oht_del_net * -1 Else order_header_total.oht_del_net End As OrderDelNet, 
Case When order_header.oh_sot_id = 4 Then order_header_total.oht_net * -1 When order_header.oh_sot_id = 9 Then order_header_total.oht_net * -1 Else order_header_total.oht_net End As OrderNet, 
Case When order_header.oh_sot_id = 4 Then order_header_total.oht_dissur_cost * -1 When order_header.oh_sot_id = 9 Then order_header_total.oht_dissur_cost * -1 Else order_header_total.oht_dissur_cost End As OrderDiscountSurchargeCost, 
Case When order_header.oh_sot_id = 4 Then order_header_total.oht_del_cost * -1 When order_header.oh_sot_id = 9 Then order_header_total.oht_del_cost * -1 Else order_header_total.oht_del_cost End As OrderDelCost, 
Case When order_header.oh_sot_id = 4 Then order_header_total.oht_total_cost * -1 When order_header.oh_sot_id = 9 Then order_header_total.oht_total_cost * -1 Else order_header_total.oht_total_cost End As OrderTotalCost, 
Case When order_header.oh_sot_id = 4 Then order_header_total.oht_total_margin * -1 When order_header.oh_sot_id = 9 Then order_header_total.oht_total_margin * -1 Else order_header_total.oht_total_margin End As OrderTotalMargin,
Case When order_header.oh_sot_id = 4 Then order_header_total.oht_total_margin_percent * -1 When order_header.oh_sot_id = 9 Then order_header_total.oht_total_margin_percent * -1 Else order_header_total.oht_total_margin_percent End As OrderMarginPcn  

FROM customer_detail 
Inner Join order_header On customer_detail.cd_id = order_header.oh_cd_id 
Inner Join order_header_total On order_header.oh_id = order_header_total.oht_oh_id 
Inner Join order_header_detail On order_header.oh_id = order_header_detail.ohd_oh_id 
Inner Join system_order_type On system_order_type.sot_id = order_header.oh_sot_id 
Inner Join order_status On order_status.os_id = order_header.oh_os_id 
Inner Join order_type On order_type.ot_id = order_header.oh_ot_id 
Inner Join customer_profile On customer_detail.cd_id = customer_profile.cp_customer_id 
Inner Join customer_source On customer_source.cs_id = customer_profile.cp_source_id 
Inner Join customer_type On customer_type.ct_id = customer_profile.cp_type_id 
Left Join order_customer_analysis On order_header.oh_id = order_customer_analysis.oca_oh_id 
Left Join order_delivery_analysis On order_header.oh_id = order_delivery_analysis.oda_oh_id 
Left Join order_header_analysis On order_header.oh_id = order_header_analysis.oha_oh_id 
Left Join user_detail On order_header_detail.ohd_sales_rep = user_detail.ud_id 
left join user_team on user_team.ut_id = user_detail.ud_ut_id

WHERE (customer_detail.cd_ow_account = @CustCode Or @AllCust = 1) 
And order_header.oh_datetime >= @DateFrom 
And (order_header.oh_sot_id = @OrdType Or @AllType = 1) 
And (order_header_detail.ohd_sales_rep = @SalesRep Or @AllSalesReps = 1) 
And order_header.oh_datetime <= @DateTo 
And order_header.oh_sot_id <> 10
AND (user_team.ut_node_path like @Team + '%' OR @AllTeam = 1),

错误

消息 102，级别 15，状态 1，过程 OrderDetailedMarginByCustomerReport，第 53 行 [批处理开始行 0] ';' 附近的语法不正确。

我的参考资料

https://www.sqlservercentral.com/articles/creating-and-using-inline-table-valued-functions

标签： sql-server

Answer 1

有几个问题需要讨论。

首先，您的query和view之间有一个非常重要的区别。在您的查询中，您有一个已知的、有效的连接结构。在您看来，您会立即通过使用cross apply. 我相信这会以您意想不到的方式破坏连接结构，因此您不会得到想要的结果。我建议您将其cross apply与paramsCTE 一起消除。相反，您可以直接将“参数”值插入源中。所以而不是：

order_header.oh_sot_id = @OrdType

说啊

order_header.oh_sot_id = '0'

因为没那么重要。与在顶部声明 CTE 相比，直接插入的值可以说是视图源的可读性。至少让它在没有 CTE 的情况下工作，以说服自己这是问题的一部分。

但这让我想到了下一个问题，那就是你试图强迫你的视图使用GETDATE(). 这是一个嵌入式限制，我认为它通常违反了视图背后的基本思想之一，即用户可以使用自己的where子句自己指定限制。在你的情况下，这看起来像这样：

select * from vwVariantbyUserReportandInputDate where OrderDate = '2018-12-21 12:13:14.123' 

除非您完全匹配 OrderDate，否则它不适用于您现有的视图代码，这基本上是不可能的。所以看起来你真正想要的更像是一个表值函数，它可以在其声明中包含变量（你可以设置日期和所有其他参数）。或者，存储过程也足够了。

当您编辑上面的帖子并添加表值函数声明时，您可能已经预料到了这一点，您说由于语法错误而无法编译：

';' 附近的语法不正确

但是我们不打算进一步讨论这个问题，因为这应该相对容易解决，而且与原始问题相比，它是题外话。