Case when error in sql server

Posted by Klbaiju under Sql Server on 7/9/2014 | Points: 10 | Views : 398 | Status : [Member] | Replies : 2
Hi i want to display id from a table having a condition that if id is null then id=300 else max(id)+1 as id from table .

i tried this with case when statement as follows

select case when (conductor_id is null)then 300 else MAX(conductor_id)+1 end as conductor_id from conductormaster
i got the error as

Column 'conductormaster.conductor_id' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
how to solve this

Regards

Baiju




Responses

Posted by: Sam31dec1990 on: 7/9/2014 [Member] Starter | Points: 25

Up
0
Down
You may use following query:

select CASE WHEN MAX(conductor_id) IS NULL THEN 5000 ELSE MAX(conductor_id)+1 END as conductor_id
from conductormaster

Klbaiju, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 7/15/2014 [Member] [MVP] Platinum | Points: 25

Up
0
Down
Add OVER() clause next to MAX() function or Add GROUP BY clause for Conducter_id at the end of the query...
select case when (conductor_id is null)then 300 else MAX(conductor_id) OVER() +1 end as conductor_id from conductormaster 


select case when (conductor_id is null)then 300 else MAX(conductor_id)  +1 end as conductor_id from conductormaster GROUP BY conducter_id


NOTE: Choose either of the above solution based on your requirement. It depends on the result which you wanted to display...

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Klbaiju, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response