Sql Error : If Else with Case When in SQL

Question

Can anyone advice what is wrong here?

I got multiple syntax errors.

IF (('05:00' < CAST(t.Scan_In_Prod AS TIME)) AND (CAST(t.Scan_In_Prod AS TIME) < '08:00'))
BEGIN
    (CASE WHEN (CAST(t.Scan_In_Prod as TIME) < CAST(t.Scan_Out_Prod AS TIME)) AND (CAST(t.Scan_Out_Prod as TIME) < '19:00')
             THEN 'Early Out'
          WHEN ('07:00' < CAST(t.Scan_In_Prod AS TIME)) AND (CAST(t.Scan_In_Prod AS TIME) < CAST(t.Scan_In_Prod AS TIME))
             THEN 'Late In'
     END)
END

Errors are shown below:

Msg 156, Level 15, State 1, Line 25
Incorrect syntax near the keyword 'if'.

Msg 156, Level 15, State 1, Line 29
Incorrect syntax near the keyword 'case'.

Msg 156, Level 15, State 1, Line 42
Incorrect syntax near the keyword 'case'.

Msg 102, Level 15, State 1, Line 107
Incorrect syntax near 't'.

You have a CASE statement sitting there in isolation with no query. This is the problem. — Tim Biegeleisen
– Tim Biegeleisen, Commented Dec 28, 2017 at 3:58
But the CASE has the experssion --> (CAST(t.Scan_In_Prod as TIME) < CAST(t.Scan_Out_Prod as TIME)) and (CAST(t.Scan_Out_Prod as TIME) < '19:00') — user548682
– user548682, Commented Dec 28, 2017 at 3:59

Tim Biegeleisen · Accepted Answer · 2017-12-28 04:02:37Z

1

Try prefacing the inner CASE expression with SELECT:

IF '05:00' < CAST(t.Scan_In_Prod AS TIME) AND
   '08:00' > CAST(t.Scan_In_Prod AS TIME)
BEGIN
    SELECT
        CASE WHEN CAST(t.Scan_In_Prod AS TIME) < CAST(t.Scan_Out_Prod AS TIME) AND
                  CAST(t.Scan_Out_Prod AS TIME) < '19:00'
             THEN 'Early Out'
             WHEN '07:00' < CAST(t.Scan_In_Prod AS TIME) AND
                  CAST(t.Scan_In_Prod as TIME) < CAST(t.Scan_In_Prod AS TIME)
             THEN 'Late In' END
END

answered Dec 28, 2017 at 4:02

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Başar Kaya · Accepted Answer · 2017-12-28 06:04:22Z

0

NO paranthese, you should use select i change only

if (('05:00' < CAST(t.Scan_In_Prod as TIME)) and  (CAST(t.Scan_In_Prod as TIME) < '08:00'))
 begin   
 SELECT case when (CAST(t.Scan_In_Prod as TIME) < CAST(t.Scan_Out_Prod as TIME)) and (CAST(t.Scan_Out_Prod as TIME) < '19:00')
        then 'Early Out'
          when  ('07:00' < CAST(t.Scan_In_Prod as TIME)) and (CAST(t.Scan_In_Prod as TIME) < CAST(t.Scan_In_Prod as TIME))
        then 'Late In'
 end

end

answered Dec 28, 2017 at 6:04

Başar Kaya

3646 silver badges14 bronze badges

1 Comment

Başar Kaya Over a year ago

I am glad user548682 . can you check my answer is useful?

Collectives™ on Stack Overflow

Sql Error : If Else with Case When in SQL

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related