I have a SQL table with column that hold a sequence Number that should start with a 001.
I want to fetch the max of this sequence number based on value of another column in the same table.
I can use Max(INCIDENT_SEQ ) +1 to increment the previous highest value but if the column value is NULL, it is not working.
Below I am trying to check for the value of the column for NULL and if not I am incrementing it by 1. But the script is throwing me an error.
WHEN b.INCIDENT_SEQ IS NOT NULL OR b.INCIDENT_SEQ <>'' THEN MAX(b.INCIDENT_SEQ) + 1
(SELECT 1 DUMMYCOL) a
LEFT OUTER JOIN INCIDENTS_MANAGEMENT_REGISTRATION b
b.FACILITY_UNIT_ID = @FACILITY_UNIT_ID
I am getting the following error
Msg 8120, Level 16, State 1, Procedure Incidents_Management_Registration_Actions_FetchIncidentSeqInfo, Line 12
Column 'INCIDENTS_MANAGEMENT_REGISTRATION.INCIDENT_SEQ' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.