SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 weird error
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

rjhe22
Constraint Violating Yak Guru

251 Posts

Posted - 06/21/2013 :  05:46:58  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 06/21/2013 :  05:50:00  Show Profile  Reply with Quote
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
Go to Top of Page

rjhe22
Constraint Violating Yak Guru

251 Posts

Posted - 06/21/2013 :  05:56:27  Show Profile  Reply with Quote
ok thanks
Go to Top of Page

rjhe22
Constraint Violating Yak Guru

251 Posts

Posted - 06/21/2013 :  05:57:23  Show Profile  Reply with Quote
still get the error
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 06/21/2013 :  06:02:23  Show Profile  Reply with Quote
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
Go to Top of Page

rjhe22
Constraint Violating Yak Guru

251 Posts

Posted - 06/21/2013 :  06:04:57  Show Profile  Reply with Quote
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


only happens on first one very strange
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 06/21/2013 :  06:16:24  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 06/21/2013 :  06:17:11  Show Profile  Reply with Quote
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
Go to Top of Page

rjhe22
Constraint Violating Yak Guru

251 Posts

Posted - 06/21/2013 :  06:17:26  Show Profile  Reply with Quote
its part of an ssis packages so has to be will try that thanks
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 06/21/2013 :  06:20:10  Show Profile  Reply with Quote
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
Go to Top of Page

rjhe22
Constraint Violating Yak Guru

251 Posts

Posted - 06/21/2013 :  06:23:04  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 06/21/2013 :  06:27:37  Show Profile  Reply with Quote
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
Go to Top of Page

rjhe22
Constraint Violating Yak Guru

251 Posts

Posted - 06/21/2013 :  06:29:23  Show Profile  Reply with Quote
thanks will look into it now and see what i can do with it
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 06/21/2013 :  06:34:34  Show Profile  Reply with Quote
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

rjhe22
Constraint Violating Yak Guru

251 Posts

Posted - 06/21/2013 :  09:56:38  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 06/21/2013 :  10:18:35  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 06/21/2013 :  10:26:46  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.19 seconds. Powered By: Snitz Forums 2000