[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

SQL Syntax Error subtracting two select statements.

Posted on 2016-11-16
5
Medium Priority
?
48 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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

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 …
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

649 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