Build a view from a query

Question

I want to build a view from a T-SQL query. Here is the query:

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)

Here is my approach. Using the solution provided by 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)

I don’t get the desired result. Any thoughts?

Here is the script and error

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),

error

Msg 102, Level 15, State 1, Procedure OrderDetailedMarginByCustomerReport, Line 53 [Batch Start Line 0] Incorrect syntax near ';'.

My references

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

Answer 1

There are several issues to discuss.

First, there is one very important difference between your query and your view. In your query, you have a known, working join structure. In your view, you immediately interfere with the known, working join structure by using a cross apply. I believe this is breaking the join structure in a way you aren't expecting, and so you don't get your desired results. I would suggest you eliminate the cross apply along with the params CTE. Instead, you could just insert the "params" values directly into the source. So instead of:

order_header.oh_sot_id = @OrdType

Just say

order_header.oh_sot_id = '0'

Because it doesn't matter that much. The source of the view is arguably just as readable with the values directly inserted compared to declaring the CTE at the top. At least get it working without the CTE to convince yourself it is part of the problem.

But that brings me to the next issue, which is the fact that you are trying to force your view to return results for a single day using GETDATE(). This is an imbedded restriction, and I think it generally violates one of the basic ideas behind a view, which is that the user can specify the restriction themselves using their own where clause. In your case, that would look something like this:

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

Which wouldn't work with your existing view code unless you matched the OrderDate exactly, which would be essentially impossible. So it seems like what you really want is more like a table valued function, which can contain variables inside its declaration (you could set the date and all your other parameters). Or, a stored procedure would also suffice.

You have probably already anticipated this as you edited your post above and added a table valued function declaration, which you say can't be compiled because of a syntax error:

Incorrect syntax near ';'

But we're not going to discuss that further because that should be relatively easy to resolve, and besides, it is off topic compared to the original question.