SQL Query Error

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.


SELECT CASE
WHEN  b.INCIDENT_SEQ  IS NOT NULL OR  b.INCIDENT_SEQ <>'' THEN MAX(b.INCIDENT_SEQ) + 1
ELSE '001'
END TOTAL_COUNT
FROM
(SELECT 1 DUMMYCOL) a
LEFT OUTER JOIN INCIDENTS_MANAGEMENT_REGISTRATION b
ON  
      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.
Member_2_7967119Asked:
Who is Participating?
 
Russ SuterConnect With a Mentor Commented:
OK, then the issue is not that the column is null but that the rowset is empty. In that case, reverse the order of calling MAX and COALESCE like this:
SELECT
    COALESCE(MAX(INCIDENT_SEQ), 0) + 1
FROM
    INCIDENTS_MANAGEMENT_REGISTRATION
WHERE
    FACILITY_UNIT_ID = @FACILITY_UNIT_ID

Open in new window

1
 
Russ SuterCommented:
You'll need to coalesce the null values for this to work correctly. Try this:
SELECT
    MAX(COALESCE(INCIDENT_SEQ), 0) + 1
FROM
    INCIDENTS_MANAGEMENT_REGISTRATION
WHERE
    FACILITY_UNIT_ID = @FACILITY_UNIT_ID

Open in new window

0
 
Member_2_7967119Author Commented:
On executing the following statement
 
SELECT
    MAX(COALESCE(INCIDENT_SEQ , 0))   + 1
FROM
    INCIDENTS_MANAGEMENT_REGISTRATION

I am still getting a NULL.
0
 
Member_2_7967119Author Commented:
Thank you for the quick support.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.