Page 1 of 2 12 LastLast
Results 1 to 15 of 18

Thread: Syntax Error "/

  1. #1
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10

    Angry Unanswered: Syntax Error "/

    I have a syntax error of

    Code:
    Server: Msg 170, Level 15, State 1, Line 21
    Line 21: Incorrect syntax near '<'.
    When running the following.
    I have highlighted the offending character.

    Code:
    DECLARE @Today		varchar(30)		SET @Today = GetDate()
    DECLARE @April		varchar(30)		SET @April = '01/04/'
    DECLARE @August		varchar(30)		SET @August = '01/08/'
    DECLARE @LastYear 	varchar(30)		SET @LastYear = YEAR(DATEADD(yy,-1,GetDate()))
    DECLARE @ThisYear 	varchar(30)		SET @ThisYear = YEAR(GetDate())
    
    SELECT	e.employee_number	AS 'Employee Number'	,
    	e.known_as_and_surname	AS 'Name'		,
    	b.benefit_code		AS 'Benefit Code'	
    
    FROM	pwa_master.employee	e,
    	pwa_master.benefit	b
    
    WHERE	e.unique_identifier = b.parent_identifier
    AND	b.benefit_code = 'BONUS'
    AND	e.company_reference IN ('NGB','NGBO','BMS')
    AND	e.payment_method = 'MONTHLY'
    AND	b.benefit_start_date >=
    
    (
    CASE @Today < @April + @ThisYear
    THEN 
    	@LastYear 
    ELSE
    	(
    	CASE @Today > @April + @ThisYear
    	THEN
    		(
    		CASE e.company_reference WHEN 'NGB'
    		THEN
    			@April + @ThisYear
    		ELSE
    			@April + @LastYear
    		END
    		)
    	ELSE
    		(
    		CASE @Today > @August + @ThisYear
    		THEN
    			@April + @ThisYear
    		END
    		)
    	END
    	)
    END
    )
    
    
    GROUP BY employee_number, known_as_and_surname, benefit_code, e.company_reference
    Can anyone tell me why this happens?
    I swear I have made case statements like this before (usign variables and greater-than signs etc).

    It is hopefully me just being a dumba** but I can't see what's wrong with it.

    -GeorgeV

  2. #2
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Isn't WHEN a required part of CASE?
    Inspiration Through Fermentation

  3. #3
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    There are two forms of the case statement. This is the one that requires the keyword WHEN. The other is:
    Code:
    case @var
    when 1 then blah
    when 2 then blah blah
    etc.

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Hmmm, that implies I cannot use greater than?
    Or am I just getting the complete wrong end of the stick?

    thanks for the replies guys <3

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    sorry, wait...
    Being a moron.

    It's "CASE WHEN @Today < @April + @thisYear"

    Thanks again

  6. #6
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Never mind... I see you got it
    Inspiration Through Fermentation

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Ok, I've now discovered another reason why my query isn't working...

    I have this statement

    Code:
    DECLARE @Today		varchar(30)		SET @Today = GetDate()
    DECLARE @April		varchar(30)		SET @April = '01/04/'
    DECLARE @ThisYear 	varchar(30)		SET @ThisYear = YEAR(GetDate())
    
    ...
    
    CASE WHEN @Today < @April + @ThisYear"
    I thought this was combinging to two strings @April and @ThisYear to give me '01/04/2007' but it is adding them together instead.

    What is the syntax for combingin two strings?

    -GeorgeV

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    why are you using strings to do date comparisons? you'd be far better off using DATETIMEs

    try this --

    CASE WHEN GETDATE() < DATEADD(m,4,DATESUB(d,DATEPART(dy,GETDATE())-1,GETDATE()))
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    Code:
    declare @var varchar(10)
    
    set @var = year(getdate())  -- implicit conversion of int to varchar
    
    select 'this' + @var
    The code works as you wrote it. You are probably looking at a problem of expecting date comparisons vs string comparisons. Try declaring the @Today variable as a datetime variable, and converting the "@April + @ThisYear" to datetime to do the comparison.

    EDIT: Sniped! Curse my wanting to comment the example!

  10. #10
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    It's ok - I fixed this just before you posted.

    I firstly swapped all the variables to char(30) because I was getting string comparison issues.

    Then it appeared to be using american dates so I simply swapped '01/04' for '04/01'

    THEN (Yeah, I know) I realised my @today variable was causing more problems so I simply removed it and where I had used it I used the GetDate() function.

    Thanks for all the suggestions, I will try them out on Monday wheen I'm back in the office but my code seems to be working.

    r937 - I am using the variables so that I don't have to repeat complex peices of code throughout... I have nested CASE statements (which I know arn't iedal) and well, if you'd read the rules needed in this extract you'd understand why I made it as readable as possible (damn it was confusing)

    Thanks again dFforumers!

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by georgev
    r937 - I am using the variables so that I don't have to repeat complex peices of code throughout...
    well, yeah, that's all fine and good, but why string variables????

    dates should be handled as dates, not strings, and for proof you have only see what happens when you confuse mm/dd with dd/mm

    this is what it should look like --

    Code:
    DECLARE @Today         datetime
    DECLARE @Jan1ThisYear  datetime
    DECLARE @Apr1ThisYear  datetime
    
    SET @Today = GetDate()
    SET @Jan1ThisYear = DATESUB(d,DATEPART(dy,GETDATE())-1,GETDATE())
    SET @Apr1ThisYear = DATEADD(m,4,@Jan1ThisYear)
    
    ...
    
    CASE WHEN @Today < @Apr1ThisYear
    Last edited by r937; 01-19-07 at 10:57.
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    georgev - how did you colorize your code for the forum post? did you use an online formatter or something? makes it so easy to read!

  13. #13
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    r937 - Good call on the datetime. I can't believe I didn't think of doing it that way.

    Could you explain how your "SET @Jan1ThisYear = ...." bit works?
    I don't quite follow it. I figured that if you used DatDate() it would take the date today...

    ---

    Jezemine - I'm afraid I simply coloured in in the post reply using the RTFer. As I understand it if you're using SQL Server 2005 when you copy thecode from there into, E.g. MSWord, the colour formatting is kept. Give it a try. Afraid I've yet to find a better way to do it on forums.

    I just colour it so taht it makes it easier to read/understand

    -GeorgeV

  14. #14
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by r937
    SET @Jan1ThisYear = DATESUB(d,DATEPART(dy,GETDATE())-1,GETDATE())
    Code:
    "DATESUB" is not a recognized function name
    I'm afraid I need this bit explained to me in lamens terms

  15. #15
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    oh, i am such an idiot

    i have no idea where DATESUB came from, since there is no such function

    change it to this --

    SET @Jan1ThisYear = DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •