Solved

SQL Syntax Error subtracting two select statements.

Posted on 2016-11-16
5
39 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 500 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

[Webinar] How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

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

I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
If you're a developer or IT admin, you’re probably tasked with managing multiple websites, servers, applications, and levels of security on a daily basis. While this can be extremely time consuming, it can also be frustrating when systems aren't wor…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

705 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