weird error - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

weird error

Author

Topic

rjhe22
Constraint Violating Yak Guru

283 Posts

Posted - 2013-06-21 : 05:46:58

hi
have this sql

TotalReturn1Year =
	CASE 
		WHERE As_of_Date > DATEADD(yy,DATEDIFF(yy,0,[Date_To]),'19000620')
AND As_of_Date <= DATEADD(yy,DATEDIFF(yy,0,[Date_To]),'19000630')

getting the following error

Msg 102, Level 15, State 1, Line 17
Incorrect syntax near '19000630'.

any idea why i have these in a few places and there is no error with the others

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-21 : 05:50:00

it should be WHEN


TotalReturn1Year =
	CASE 
		WHERE WHEN As_of_Date > DATEADD(yy,DATEDIFF(yy,0,[Date_To]),'19000620')
AND As_of_Date <= DATEADD(yy,DATEDIFF(yy,0,[Date_To]),'19000630') THEN...

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

rjhe22
Constraint Violating Yak Guru

283 Posts

Posted - 2013-06-21 : 05:56:27

ok thanks

rjhe22
Constraint Violating Yak Guru

283 Posts

Posted - 2013-06-21 : 05:57:23

still get the error

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-21 : 06:02:23

quote:
Originally posted by rjhe22

still get the error

show the full code

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

rjhe22
Constraint Violating Yak Guru

283 Posts

Posted - 2013-06-21 : 06:04:57

[code]SET QUOTED_IDENTIFIER OFF

DECLARE
@SqlStmt NVARCHAR(max),
@WorkingDataloadFile NVARCHAR(256),
@FundCode NVARCHAR(256),
@DefaultDate NVARCHAR(256)

SELECT @WorkingDataloadFile = ?
SELECT @FundCode = ?
SELECT @DefaultDate = '1900-01-01'

SELECT @SQLStmt = 'UPDATE ' + @WorkingDataloadFile + '
SET
TotalReturn1Year =
CASE
WHEN As_of_Date > DATEADD(yy,DATEDIFF(yy,0,[Date_To]),'19000620')
AND As_of_Date <= DATEADD(yy,DATEDIFF(yy,0,[Date_To]),'19000630')
THEN Net_Distributions_Paid_Amount
ELSE
0.00
END
,TotalReturn9Months =
CASE
WHEN As_of_Date > DATEADD(yy,DATEDIFF(yy,0,[Date_To]),'19000630')
AND As_of_Date <= DATEADD(yy,DATEDIFF(yy,0,[Date_To]),'19000930')
THEN Net_Distributions_Paid_Amount
ELSE
0.00
END
,TotalReturn6Months =
CASE
WHEN As_of_Date > DATEADD(yy,DATEDIFF(yy,0,[Date_To]),'19000930')
AND As_of_Date <= DATEADD(yy,DATEDIFF(yy,0,[Date_To]),'19001231')
THEN Net_Distributions_Paid_Amount
ELSE
0.00
END
,TotalReturn3Months =
CASE
WHEN As_of_Date > DATEADD(yy,DATEDIFF(yy,0,[Date_To]),'19001231')
AND As_of_Date <= DATEADD(yy,DATEDIFF(yy,0,[Date_To]),'19000331')
THEN Net_Distributions_Paid_Amount
ELSE
0.00
END
,TotalReturn1Month =
CASE
WHEN As_of_Date > DATEADD(yy,DATEDIFF(yy,0,[Date_To]),'19000331')
AND As_of_Date <= DATEADD(yy,DATEDIFF(yy,0,[Date_To]),'19000619')
THEN Net_Distributions_Paid_Amount
ELSE
0.00
END
,TotalReturn10YearCumulative =
CASE
WHEN As_of_Date > DATEADD(yy,DATEDIFF(yy,0,[Date_To]),'19000620')
AND As_of_Date <= DATEADD(yy,DATEDIFF(yy,0,[Date_To]),'19000630')
THEN Distribution_Paid_CPU
ELSE
0.00
END
,TotalReturn9YearCumulative =
CASE
WHEN As_of_Date > DATEADD(yy,DATEDIFF(yy,0,[Date_To]),'19000630')
AND As_of_Date <= DATEADD(yy,DATEDIFF(yy,0,[Date_To]),'19000930')
THEN Distribution_Paid_CPU
ELSE
0.00
END
,TotalReturn6YearCumulative =
CASE
WHEN As_of_Date > DATEADD(yy,DATEDIFF(yy,0,[Date_To]),'19000930')
AND As_of_Date <= DATEADD(yy,DATEDIFF(yy,0,[Date_To]),'19001231')
THEN Distribution_Paid_CPU
ELSE
0.00
END
,TotalReturn3YearCumulative =
CASE
WHEN As_of_Date > DATEADD(yy,DATEDIFF(yy,0,[Date_To]),'19001231')
AND As_of_Date <= DATEADD(yy,DATEDIFF(yy,0,[Date_To]),'19000331')
THEN Distribution_Paid_CPU
ELSE
0.00
END
,TotalReturn2YearCumulative =
CASE
WHEN As_of_Date > DATEADD(yy,DATEDIFF(yy,0,[Date_To]),'19000331')
AND As_of_Date <= DATEADD(yy,DATEDIFF(yy,0,[Date_To]),'19000619')
THEN Distribution_Paid_CPU
ELSE
0.00
END
,TotalReturn10YearAnnualized =
CASE
WHEN As_of_Date > DATEADD(yy,DATEDIFF(yy,0,[Date_To]),'19000620')
AND As_of_Date <= DATEADD(yy,DATEDIFF(yy,0,[Date_To]),'19000630')
THEN Reinvested_Distributions
ELSE
0.00
END
,TotalReturn9YearAnnualized =
CASE
WHEN As_of_Date > DATEADD(yy,DATEDIFF(yy,0,[Date_To]),'19000630')
AND As_of_Date <= DATEADD(yy,DATEDIFF(yy,0,[Date_To]),'19001030')
THEN Reinvested_Distributions
ELSE
0.00
END
,TotalReturn6YearAnnualized =
CASE
WHEN As_of_Date > DATEADD(yy,DATEDIFF(yy,0,[Date_To]),'19001030')
AND As_of_Date <= DATEADD(yy,DATEDIFF(yy,0,[Date_To]),'19001231')
THEN Reinvested_Distributions
ELSE
0.00
END
,TotalReturn3YearAnnualized =
CASE
WHEN As_of_Date > DATEADD(yy,DATEDIFF(yy,0,[Date_To]),'19001231')
AND As_of_Date <= DATEADD(yy,DATEDIFF(yy,0,[Date_To]),'19000331')
THEN Reinvested_Distributions
ELSE
0.00
END
,TotalReturn2YearAnnualized =
CASE
WHEN As_of_Date > DATEADD(yy,DATEDIFF(yy,0,[Date_To]),'19000331')
AND As_of_Date <= DATEADD(yy,DATEDIFF(yy,0,[Date_To]),'19000619')
THEN Reinvested_Distributions
ELSE
0.00
END '
+ "WHERE SSB_Fund_Num = '" + @FundCode + "'"

EXECUTE (@SQLStmt)

SELECT @SQLStmt = "UPDATE " + @WorkingDataloadFile + '
SET
NAVIncludingShadowPriceCalcDate =
CASE
WHEN DATEDIFF("dd",Date_To,Reinvest_Date) > -1
THEN Reinvest_Date
ELSE
' + "'" + @DefaultDate + "'" + "
END "
+ "WHERE SSB_Fund_Num = '" + @FundCode + "'"

EXECUTE (@SQLStmt)

SET QUOTED_IDENTIFIER ON
GO
[/code]

only happens on first one very strange

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-21 : 06:16:24

why do you need dynamic sql here? why tablename come as a parameter?

Anyways if you want to use it inside dynamic string you should have '' around all date values like


..
SELECT @SQLStmt = 'UPDATE ' + @WorkingDataloadFile + '
 SET 
	TotalReturn1Year =
	CASE 
		WHEN As_of_Date > DATEADD(yy,DATEDIFF(yy,0,[Date_To]),''19000620'')
AND As_of_Date <= DATEADD(yy,DATEDIFF(yy,0,[Date_To]),''19000630'')
			THEN Net_Distributions_Paid_Amount 
		 ELSE
			0.00
		 END 
...

similarly in all the other places

also you may be better off using sp_executesql over EXEC for executing dynamic string

I'm still not convinced on use of dynamic sql and making tablename a parameter. perhaps you could give us some overview if you want us to help you out with an alternative.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-21 : 06:17:11

here's a link for more info on sp_executesql

http://msdn.microsoft.com/en-us/library/ms188001.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

rjhe22
Constraint Violating Yak Guru

283 Posts

Posted - 2013-06-21 : 06:17:26

its part of an ssis packages so has to be will try that thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-21 : 06:20:10

quote:
Originally posted by rjhe22

its part of an ssis packages so has to be will try that thanks

in that why not use a variable inside SSIS with an expression to form the query using your dynamic tablename and then use SQLSourceType as variable in execute sql task to execute query from variable?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

rjhe22
Constraint Violating Yak Guru

283 Posts

Posted - 2013-06-21 : 06:23:04

ya think so but not sure i didnt do the code for this i was just asked to update it. i have not worked with ssis packages that much yet

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-21 : 06:27:37

I would suggest doing it the way i told rather than using dynamic sql inside your execute sql query. Thats much better of doing it than currently used method.
see

http://the-simple-programmer.blogspot.com/2010/02/use-variable-as-table-name-in-ssis.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

rjhe22
Constraint Violating Yak Guru

283 Posts

Posted - 2013-06-21 : 06:29:23

thanks will look into it now and see what i can do with it

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-21 : 06:34:34

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

rjhe22
Constraint Violating Yak Guru

283 Posts

Posted - 2013-06-21 : 09:56:38

tried every possible way with dates but does not seem to work.
tried "19000303" '19000303' "1900-03-03" '1900-03-03' still getting that error

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-21 : 10:18:35

quote:
Originally posted by rjhe22

tried every possible way with dates but does not seem to work.
tried "19000303" '19000303' "1900-03-03" '1900-03-03' still getting that error

see this to understand where you're going wrong

http://sqlblogcasts.com/blogs/madhivanan/archive/2008/02/19/understanding-single-quotes.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-21 : 10:26:46

modify like below and check


SET QUOTED_IDENTIFIER OFF

DECLARE
 @SqlStmt     NVARCHAR(max),
 @WorkingDataloadFile  NVARCHAR(256),
 @FundCode  NVARCHAR(256),
 @DefaultDate  NVARCHAR(256)

SELECT @WorkingDataloadFile  = ?
SELECT @FundCode = ?
SELECT @DefaultDate  = '1900-01-01'

SELECT @SQLStmt = 'UPDATE ' + @WorkingDataloadFile + '
 SET 
	TotalReturn1Year =
	CASE 
		WHEN As_of_Date > DATEADD(yy,DATEDIFF(yy,0,[Date_To]),''19000620'')
AND As_of_Date <= DATEADD(yy,DATEDIFF(yy,0,[Date_To]),''19000630'')
			THEN Net_Distributions_Paid_Amount 
		 ELSE
			0.00
		 END 
	,TotalReturn9Months =
	CASE 
		WHEN As_of_Date > DATEADD(yy,DATEDIFF(yy,0,[Date_To]),''19000630'')
AND As_of_Date <= DATEADD(yy,DATEDIFF(yy,0,[Date_To]),''19000930'')
			THEN Net_Distributions_Paid_Amount 
		 ELSE
			0.00
		 END 
	,TotalReturn6Months =
	CASE 
		WHEN As_of_Date > DATEADD(yy,DATEDIFF(yy,0,[Date_To]),''19000930'')
AND As_of_Date <= DATEADD(yy,DATEDIFF(yy,0,[Date_To]),''19001231'')
			 THEN Net_Distributions_Paid_Amount 
		ELSE
			0.00
		END
	,TotalReturn3Months =
	CASE
		WHEN As_of_Date > DATEADD(yy,DATEDIFF(yy,0,[Date_To]),''19001231'')
AND As_of_Date <= DATEADD(yy,DATEDIFF(yy,0,[Date_To]),''19000331'')
			 THEN Net_Distributions_Paid_Amount 
		ELSE
			0.00
		END
	,TotalReturn1Month =
	CASE
		WHEN As_of_Date > DATEADD(yy,DATEDIFF(yy,0,[Date_To]),''19000331'')
AND As_of_Date <= DATEADD(yy,DATEDIFF(yy,0,[Date_To]),''19000619'')
			 THEN Net_Distributions_Paid_Amount 
		ELSE
			0.00
		END
	,TotalReturn10YearCumulative =
	CASE 
		WHEN As_of_Date > DATEADD(yy,DATEDIFF(yy,0,[Date_To]),''19000620'')
AND As_of_Date <= DATEADD(yy,DATEDIFF(yy,0,[Date_To]),''19000630'')
			THEN Distribution_Paid_CPU  
		 ELSE
			0.00
		 END 
	,TotalReturn9YearCumulative =
	CASE 
		WHEN As_of_Date > DATEADD(yy,DATEDIFF(yy,0,[Date_To]),''19000630'')
AND As_of_Date <= DATEADD(yy,DATEDIFF(yy,0,[Date_To]),''19000930'')
			THEN Distribution_Paid_CPU 
		 ELSE
			0.00
		 END 
	,TotalReturn6YearCumulative =
	CASE 
		WHEN As_of_Date > DATEADD(yy,DATEDIFF(yy,0,[Date_To]),''19000930'')
AND As_of_Date <= DATEADD(yy,DATEDIFF(yy,0,[Date_To]),''19001231'')
			 THEN Distribution_Paid_CPU 
		ELSE
			0.00
		END
	,TotalReturn3YearCumulative =
	CASE
		WHEN As_of_Date > DATEADD(yy,DATEDIFF(yy,0,[Date_To]),''19001231'')
AND As_of_Date <= DATEADD(yy,DATEDIFF(yy,0,[Date_To]),''19000331'')
			 THEN Distribution_Paid_CPU 
		ELSE
			0.00
		END 
	,TotalReturn2YearCumulative =
	CASE
		WHEN As_of_Date > DATEADD(yy,DATEDIFF(yy,0,[Date_To]),''19000331'')
AND As_of_Date <= DATEADD(yy,DATEDIFF(yy,0,[Date_To]),''19000619'')
			 THEN Distribution_Paid_CPU 
		ELSE
			0.00
		END 
	,TotalReturn10YearAnnualized =
	CASE 
		WHEN As_of_Date > DATEADD(yy,DATEDIFF(yy,0,[Date_To]),''19000620'')
AND As_of_Date <= DATEADD(yy,DATEDIFF(yy,0,[Date_To]),''19000630'')
			THEN Reinvested_Distributions
		 ELSE
			0.00
		 END 
	,TotalReturn9YearAnnualized =
	CASE 
		WHEN As_of_Date > DATEADD(yy,DATEDIFF(yy,0,[Date_To]),''19000630'')
AND As_of_Date <= DATEADD(yy,DATEDIFF(yy,0,[Date_To]),''19001030'')
			THEN Reinvested_Distributions
		 ELSE
			0.00
		 END 
	,TotalReturn6YearAnnualized =
	CASE 
		WHEN As_of_Date > DATEADD(yy,DATEDIFF(yy,0,[Date_To]),''19001030'')
AND As_of_Date <= DATEADD(yy,DATEDIFF(yy,0,[Date_To]),''19001231'')
			 THEN Reinvested_Distributions
		ELSE
			0.00
		END
	,TotalReturn3YearAnnualized =
	CASE
		WHEN As_of_Date > DATEADD(yy,DATEDIFF(yy,0,[Date_To]),''19001231'')
AND As_of_Date <= DATEADD(yy,DATEDIFF(yy,0,[Date_To]),''19000331'')
			 THEN Reinvested_Distributions
		ELSE
			0.00
		END
	,TotalReturn2YearAnnualized =
	CASE
		WHEN As_of_Date > DATEADD(yy,DATEDIFF(yy,0,[Date_To]),''19000331'')
AND As_of_Date <= DATEADD(yy,DATEDIFF(yy,0,[Date_To]),''19000619'')
			 THEN Reinvested_Distributions
		ELSE
			0.00
		END '
+ "WHERE SSB_Fund_Num = '" + @FundCode + "'"


EXECUTE (@SQLStmt) 

SELECT @SQLStmt = "UPDATE " + @WorkingDataloadFile + '
 SET 
	NAVIncludingShadowPriceCalcDate =
	CASE 
		WHEN DATEDIFF("dd",Date_To,Reinvest_Date) > -1
			THEN Reinvest_Date 
			ELSE 
				' + "'" + @DefaultDate + "'" + "	
		  END "
+ "WHERE SSB_Fund_Num = '" + @FundCode + "'"

EXECUTE (@SQLStmt) 

SET QUOTED_IDENTIFIER ON
GO

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources