CODEWITHSUNDEEP
sqlsql-serversyntax-error
john smith
john smith

Reputation: 27

SQL "Incorrect Syntax near 'IF' - cannot figure out issue

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.sstatic.net/7maMA.png

Upvotes: 0

Views: 1508

Answers (3)

Ravi
Ravi

Reputation: 1172

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

Upvotes: 0

JayaPrakash
JayaPrakash

Reputation: 199

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

Upvotes: 0

Dennes Torres
Dennes Torres

Reputation: 416

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

Upvotes: 0

Related Questions