?
Solved

SQL Syntax Error subtracting two select statements.

Posted on 2016-11-16
5
Medium Priority
?
41 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 66

Expert Comment

by:Jim Horn
ID: 41890348
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 2000 total points
ID: 41890373
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
ID: 41890382
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 49

Expert Comment

by:PortletPaul
ID: 41890408
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
ID: 41890430
Thank You
0

Featured Post

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…

777 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