Solved

SQL Syntax Error subtracting two select statements.

Posted on 2016-11-16
5
14 Views
Last Modified: 2016-11-16
Receiving syntax error Msg 102, Level 15, State 1, Procedure KLLsp_InventoryRoll_Variance, Line 23
Incorrect syntax near 'v2'.

USE [dataKLL];
GO
/****** Object:  StoredProcedure [dbo].[KLLsp_InventoryRoll_Variance]    Script Date: 11/16/2016 12:36:20 PM ******/
SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
-- =============================================
-- Author:		Michael Burk
-- Create date: 11/16/2016
-- Description:	Retrieve records for Inventory Roll Variance.
-- =============================================
CREATE PROCEDURE [dbo].[KLLsp_InventoryRoll_Variance]
	-- Add the parameters for the stored procedure here
AS
    BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
        SET NOCOUNT ON;

    -- Insert statements for procedure here
select v1.Value1 - v2.Value2 from  

(SELECT  SUM(CalInvPc + AdjPc + RecPc + RetPc - ShipInvPc) AS Value1
FROM    KLL_Cust.dbo.KLLInventoryRoll
WHERE   ( Date = DATEADD(d, DATEDIFF(d, 0, GETDATE()), -1) ) )as v1   
CROSS JOIN
(  (SELECT   SUM(RepInvEndPc) as Value2 
FROM    KLL_Cust.dbo.KLLInventoryRoll
WHERE   ( Date = DATEADD(d, DATEDIFF(d, 0, GETDATE()), -1) )  ) )AS v2

Open in new window

0
Comment
Question by:mburk1968
5 Comments
 
LVL 65

Expert Comment

by:Jim Horn
Comment Utility
Please translate 'line 23' in the error message to the line in the code block, which can be done by double-clicking on the error message, observing where the cursor jumps to in code, noting that line, and relating it to the above code block.

Also amplify what you mean by 'subtracting two SELECT statements'
0
 
LVL 52

Accepted Solution

by:
_agx_ earned 500 total points
Comment Utility
EDIT: Agreed, more clarification is needed. My guess is too many parenthesis.  Also, "Date" is the name of a data type, so using it as a column name could also cause a syntax error.

That said, why the derived queries and CROSS JOIN? If the WHERE condition is the same for both, I think you can just subtract the SUM's.  Not tested, but somethign like ...

from the other, ie 
	SELECT  SUM(CalInvPc + AdjPc + RecPc + RetPc - ShipInvPc) 
			- SUM(RepInvEndPc) AS TheResult
	FROM    KLL_Cust.dbo.KLLInventoryRoll
	WHERE   [Date] = DATEADD(d, DATEDIFF(d, 0, GETDATE()), -1) 

Open in new window

1
 

Author Comment

by:mburk1968
Comment Utility
The error highlights the following line
 WHERE   ( Date = DATEADD(d, DATEDIFF(d, 0, GETDATE()), -1) )  ) )AS v2 

Open in new window


I want to subtract the result from Value1

SELECT  SUM(CalInvPc + AdjPc + RecPc + RetPc - ShipInvPc) 
			- SUM(RepInvEndPc) AS TheResult
	FROM    KLL_Cust.dbo.KLLInventoryRoll
	WHERE   [Date] = DATEADD(d, DATEDIFF(d, 0, GETDATE()), -1)

Open in new window


From Value2

SELECT   SUM(RepInvEndPc) as Value2 
FROM    KLL_Cust.dbo.KLLInventoryRoll
WHERE   ( Date = DATEADD(d, DATEDIFF(d, 0, GETDATE()), -1)

Open in new window

0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
as stated by  _agx_
"date" is a reserved word, if you insist on calling a column that name then use [Date] in your queries

also the revised query by _agx_ should solve your problem, you do not need 2 queries to arrive at the single calculation

no points please
0
 

Author Closing Comment

by:mburk1968
Comment Utility
Thank You
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now