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
Go to Top of Page

rjhe22
Constraint Violating Yak Guru

283 Posts

Posted - 2013-06-21 : 05:56:27
ok thanks
Go to Top of Page

rjhe22
Constraint Violating Yak Guru

283 Posts

Posted - 2013-06-21 : 05:57:23
still get the error
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page
   

- Advertisement -