Solved

SQL Syntax Error subtracting two select statements.

Posted on 2016-11-16
5
31 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
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 48

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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

770 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