Case when error in sql server

Posted by Klbaiju under Sql Server on 7/9/2014 | Points: 10 | Views : 705 | Status : [Member] | Replies : 2

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

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

Reply | Reply with Attachment

Alert Moderator

Responses

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

0	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

0	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

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

Latest Posts