How to use If else in SQL Select statement

Question

I have the below select statement.

WITH cte AS
(
   SELECT MAX(logDate) AS Daily
   FROM PhysicalDriveSize
   GROUP BY DATEADD(m, DATEDIFF(m, 0, logDate), 0)
)
SELECT CAST(MIN(t.logDate) AS DATE) AS [Date], Drive, MIN(t.Free) AS [PercentagFree]
FROM PhysicalDriveSize AS t
JOIN cte AS m
ON t.logDate = m.Daily
WHERE t.Free <= 10 AND t.Free >= 0 
GROUP BY Drive, logDate

I want to do some actions based on the results for ex.

WITH cte AS
(
   SELECT MAX(logDate) AS Daily
   FROM PhysicalDriveSize
   GROUP BY DATEADD(m, DATEDIFF(m, 0, logDate), 0)
)
SELECT MIN(t.Free) AS [PercentagFree]
FROM PhysicalDriveSize AS t
JOIN cte AS m
ON t.logDate = m.Daily
WHERE t.Free <= 10 AND t.Free >= 0 
GROUP BY Drive, logDate
IF t.Free <= 10 AND t.Free >= 5
PRINT 'Warning'
Else
PRINT 'Critical'

I get the below error if I perform if statement:

Msg 4104, Level 16, State 1, Line 13 The multi-part identifier "t.Free" could not be bound. Msg 4104, Level 16, State 1, Line 13 The multi-part identifier "t.Free" could not be bound

how can I engage if statement without errors??

Msg 4104, Level 16, State 1, Line 13 The multi-part identifier "t.Free" could not be bound. Msg 4104, Level 16, State 1, Line 13 The multi-part identifier "t.Free" could not be bound. — user1071629
– user1071629, Commented Nov 4, 2015 at 10:09

Rahul Tripathi · Accepted Answer · 2015-11-04 10:25:59Z

2

Try to use case when then like this:

WITH cte AS
(
   SELECT MAX(logDate) AS Daily
   FROM PhysicalDriveSize
   GROUP BY DATEADD(m, DATEDIFF(m, 0, logDate), 0)
)
SELECT MIN(t.Free) AS [PercentagFree],
case when t.Free <= 10 AND t.Free >= 5 then 'Warning'
     else 'Critical' end
FROM PhysicalDriveSize AS t
JOIN cte AS m
ON t.logDate = m.Daily
WHERE t.Free <= 10 AND t.Free >= 0 
GROUP BY Drive, logDate

EDIT:

If you want to send the message you can try to create a temp variable like

DECLARE @x int;
WITH cte AS
(
   SELECT MAX(logDate) AS Daily
   FROM PhysicalDriveSize
   GROUP BY DATEADD(m, DATEDIFF(m, 0, logDate), 0)
)
SELECT @x = MIN(t.Free) AS [PercentagFree]
FROM PhysicalDriveSize AS t
JOIN cte AS m
ON t.logDate = m.Daily
WHERE t.Free <= 10 AND t.Free >= 0 
GROUP BY Drive, logDate
if(@x is not null)
   PRINT 'Warning'
Else
   PRINT 'Critical'

edited Nov 4, 2015 at 10:25

answered Nov 4, 2015 at 10:10

Rahul Tripathi

173k33 gold badges292 silver badges341 bronze badges

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

6 Comments

user1071629 Over a year ago

I got the below error "Incorrect syntax near the keyword 'case'."

Rahul Tripathi Over a year ago

@user1071629:- Comma was misisng. Update that. try now!

user1071629 Over a year ago

The code is working, but the purpose is to send alert mail after then instead of warning and critical.

Rahul Tripathi Over a year ago

@user1071629:- Not sure if I understand it correctly. I have updated my code. Were you looking for this?

user1071629 Over a year ago

Thanx Rahul its working with temp variable, appreciate your help.

|

Lalji Nakum · Accepted Answer · 2015-11-04 11:43:32Z

1

you can use like this :

IF(t.Free <= 10 AND t.Free >= 0, 'Warning', 'Critical') as free

edited Nov 4, 2015 at 11:43

answered Nov 4, 2015 at 10:11

Lalji Nakum

3801 silver badge15 bronze badges

1 Comment

Rahul Tripathi Over a year ago

What about the t.Free >= 0 condition?

Collectives™ on Stack Overflow

How to use If else in SQL Select statement

2 Answers 2

6 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related