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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Russ SuterCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Member_2_7967119Author Commented:
Thank you for the quick support.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.