none
Msg 102 Incorrect syntax near '>'

    Question

  • HI Forum Members

    I'm getting following Error Msg on the Code/statement below (For the CONDITION '>='),

    Error Msg

    Msg 102, Level 15, State 1, Line 11

    Incorrect syntax near '>'.

    Code/statement

    select

    cicmpy.cmp_wwn, cicmpy.cmp_name from cicmpy, xxau_absences

    where 

    cicmpy.syscreator =xxau_absences.ActualResource
    AND

    @thismonth like CASE

    when LTRIM(@thismonth)= 'TRUE'

    then


           

    (cicmpy.syscreated) >= [Date(YEAR(now),MONTH(now),1)] -- Greater Than condition which gives error--

           

    and


           

    (cicmpy.syscreated) <= now

    Looking for a solutuion...

    Thanks alot......

    Sunday, June 23, 2013 2:00 PM

Answers

  • I'm getting following Error Msg on the Code/statement below (For the CONDITION '>='),

    Error Msg

    Msg 102, Level 15, State 1, Line 11

    Incorrect syntax near '>'.

    A CASE expression in SQL returns a scalar value.  It is not a control-of-flow statement as in some other languages.  I don't know what database you are using but the example below should get you started.

    SELECT 
    	cicmpy.cmp_wwn
    	, cicmpy.cmp_name
    FROM cicmpy, xxau_absences
    WHERE  
    	cicmpy.syscreator = xxau_absences.ActualResource 
    	AND cicmpy.syscreated >= 
    		CASE
    			WHEN LTRIM(@thismonth)= 'TRUE' THEN [Date(YEAR(now),MONTH(now),1)]
    			ELSE '1900-01-01' --specify false value here
    		END
    	AND cicmpy.syscreated <= now


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Sunday, June 23, 2013 2:26 PM
    Moderator

All replies

  • Try [DATEFROMPARTS(YEAR(now),MONTH(now),1)] to build the date value you are comparing to. 
    Sunday, June 23, 2013 2:14 PM
  • I'm getting following Error Msg on the Code/statement below (For the CONDITION '>='),

    Error Msg

    Msg 102, Level 15, State 1, Line 11

    Incorrect syntax near '>'.

    A CASE expression in SQL returns a scalar value.  It is not a control-of-flow statement as in some other languages.  I don't know what database you are using but the example below should get you started.

    SELECT 
    	cicmpy.cmp_wwn
    	, cicmpy.cmp_name
    FROM cicmpy, xxau_absences
    WHERE  
    	cicmpy.syscreator = xxau_absences.ActualResource 
    	AND cicmpy.syscreated >= 
    		CASE
    			WHEN LTRIM(@thismonth)= 'TRUE' THEN [Date(YEAR(now),MONTH(now),1)]
    			ELSE '1900-01-01' --specify false value here
    		END
    	AND cicmpy.syscreated <= now


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Sunday, June 23, 2013 2:26 PM
    Moderator