By john smith


2017-05-26 23:23:45 8 Comments

I can't get rid of "Incorrect Syntax near 'IF'

have made several attempts with CASE and IF Else statements using begin and end.

First IF on second line has error: Incorrect Syntax near 'IF'

What am I missing ?!!!

Thanks, John

snippet of select long query.

CASE when (pt.PRE_POST_MODE = NULL) then
/*Error ->*/IF (CONVERT(DATETIME, CONVERT(DATE, extra.END_DATE)) != null)
                begin
                    if (IsDate(rc.START_DATE)= 1) 
                        begin
                            select (CONVERT(DATETIME, CONVERT(DATE, extra.START_DATE)))
                        end
                    else if  (CONVERT(date, rc.START_DATE) < CONVERT(date, sh.SAIL_DATE_FROM))  
                        begin
                            select 'PRE'
                        end
                    end

                    if (IsDate(extra.END_DATE) = 1)
                    begin
                        if (IsDate(rc.START_DATE) = 1) 
                            begin
                                select CONVERT(DATETIME, CONVERT(DATE,extra.START_DATE))
                                --WHEN CAST(CONVERT(DATETIME, CONVERT(DATE, rc.START_DATE)) AS DATE) >= CAST(sh.SAIL_DATE_TO AS DATE) THEN 'POST'
                                if CONVERT(DATETIME, CONVERT(DATE, rc.START_DATE)) >= sh.SAIL_DATE_TO  
                                begin
                                    select 'POST'
                                end
                            end
                    end

           ELSE 
              SELECT pt.PRE_POST_MODE

        as PRE_POST_MODE,

First IF on second line has error: Incorrect Syntax near 'IF'

enter image description here

thank you !

s://i.stack.imgur.com/7maMA.png

3 comments

@Ravi 2017-05-27 12:23:39

use case instead

CASE when (pt.PRE_POST_MODE = NULL) then
case when (CONVERT(DATETIME, CONVERT(DATE, extra.END_DATE)) != null)
 then (case when (IsDate(rc.START_DATE)= 1) then (CONVERT(DATETIME, CONVERT(DATE, extra.START_DATE))) 
        when (CONVERT(date, rc.START_DATE) < CONVERT(date, sh.SAIL_DATE_FROM)) then 'PRE' end)
when  (IsDate(extra.END_DATE) = 1) then (case when (IsDate(rc.START_DATE) = 1)  then CONVERT(DATETIME, CONVERT(DATE,extra.START_DATE)) 
                                case when CONVERT(DATETIME, CONVERT(DATE, rc.START_DATE)) >= sh.SAIL_DATE_TO  then 'POST' else '' end)  end)
           ELSE 
              pt.PRE_POST_MODE end)
              end

        as PRE_POST_MODE

@ john smith 2017-05-30 17:33:41

Thank you Ravi (and Gordon, Juan, Hatchet, et al) for explaining CASE is for conditional expresions and IF is for flow control

@JayaPrakash 2017-05-27 04:34:53

As If can't be used alike above statement you posted, You can use Nested Case.

http://www.sqlservercurry.com/2016/05/nested-case-statement-in-sql-server_15.html?m=1

@Dennes Torres 2017-05-27 01:10:40

Exactly as others already mentioned, IF is a control flow statement, can't be used inside a select statement.

However, you can use IIF function to achieve this result. You will be able to write the same expression using IIF, however, the result will be way too complex, I would look for other solution.

This link explains the IIF expresion:

https://msdn.microsoft.com/pt-br/library/hh213574.aspx

Related Questions

Sponsored Content

3 Answered Questions

[SOLVED] Incorrect syntax near the keyword 'ELSE'

  • 2014-05-05 22:15:34
  • Muhab
  • 10335 View
  • 5 Score
  • 3 Answer
  • Tags:   sql-server tsql

1 Answered Questions

1 Answered Questions

[SOLVED] Incorrect syntax near 'end'

2 Answered Questions

[SOLVED] I have an incorrect syntax error

  • 2014-04-19 02:17:45
  • user3533747
  • 420 View
  • 6 Score
  • 2 Answer
  • Tags:   sql-server

1 Answered Questions

[SOLVED] SQL Server 2012 Incorrect syntax near '='

2 Answered Questions

[SOLVED] Incorrect Syntax Near '+' - SQL/TSQL

3 Answered Questions

[SOLVED] SQL Query - Incorrect syntax near the keyword 'LIKE'

3 Answered Questions

[SOLVED] Incorrect syntax near 'BEGIN'. in TSQL

  • 2012-12-16 15:00:02
  • Mohamad Shatnawi
  • 7335 View
  • 0 Score
  • 3 Answer
  • Tags:   sql-server tsql

3 Answered Questions

[SOLVED] Incorrect syntax near the keyword 'LEFT'

Sponsored Content