TSQL return predefined row when no rows found-CodePudding

Working with Microsoft SQL Server: There are multiple systems, each one has set of errors (four digit numbers) that map to an Error Group (string). There is one exception: if the error string is NOT in the list below, it should fall into a predefined Error Group.

Example:

System 1:
Error Group 1: 2001, 2003, 6538, 6540, 6542, 6544, 6546
Error Group 2: 2002, 6539, 6541, 6543, 6545, 6547, 6549
Error Group 3: 1000, 1001, 1002, 4001, 4007, 5001, 5002 AND ANYTHING ELSE!

System 2:
Error Group 1: 6016, 6533, 6540, 6542, 6544, 6546
Error Group 2: 6541, 6543, 6545, 6547, 6549 AND ANYTHING ELSE!
Error Group 3: 1002, 4001, 4007, 5001, 5002

The table:

SystemId (int): 1, 2, etc
ErrorGroup (string): "Error Type 1", ...
Error (int?): the four digit number

Finding the errorGroup is straight forward:

SELECT ErrorGroup 
FROM table 
WHERE SystemId = 1 AND Error = 2002

The goal is one query that will return the error group when the error is found and the "default" error group when the error is not found. There is flexibility in changing or modifying the data to make this happen.

What would it look like in Linq?

CodePudding user response：

You can use the T-SQL ISNULL system function to return a predefined constant value if the result of the query is empty.

SELECT ISNULL(
    (SELECT ErrorGroup FROM table 
    WHERE SystemId = 1 AND Error = 2002), 
    'Default Group')

Instead of the string literal, another query can also be used.

As for Linq, DefaultIfEmpty can be used to set the default value of the query if there is no result.

CodePudding user response：

If you know you will always just return one row the best way to do this is to use coalesce -- this will work on all versions and platforms of SQL.

SELECT COALESCE(ErrorGroup,'Default Group') AS ErrorGroup
FROM tablename
WHERE SystemId = 1 and Error = 2002

CodePudding user response：

As far as a SQL solution goes here is a possibility.

I created the table as:

CREATE TABLE ErrorData (SystemID int NOT NULL,
                        ErrorGroupID int NOT NULL,
                        ErrorCode int NULL)

I added data based on you info above:

INSERT INTO ErrorData (SystemID, ErrorGroupID, ErrorCode)
values (1,1,3001),(1,1,3003),(1,1,6538),(1,1,6540),(1,1,6542),(1,1,6544),(1,1,6546),
       (1,2,2002),(1,2,6539),(1,2,6541),(1,2,6543),(1,2,6545),(1,2,6547),(1,2,6549), 
       (1,3,1000),(1,3,1001),(1,3,1002),(1,3,4007),(1,3,6542),(1,3,5001),(1,3,5002),
       (1,3,null),
       (2,1,6016),(2,1,6533),(2,1,6540),(2,1,6542),(2,1,6544),(2,1,6546),
       (2,2,6541),(2,2,6543),(2,2,6545),(2,2,6547),(2,2,6549), 
       (2,3,1002),(2,3,4001),(2,3,4007),(2,3,5001),(2,3,5002),
       (2,2,null)

You will notice I inserted a value with NULL for the error code to identify the default Error Group

I can then query the data and get the proper ErrorGroup, or the default one using this query

SELECT TOP 1 ErrorGroupID FROM ErrorData WHERE SystemID = 1 AND (ErrorCode = 3003 OR ErrorCode IS NULL) ORDER BY ISNULL(ErrorCode,99999)

If i query for an Error Code that doesn't exist i will get the default Error Group

SELECT TOP 1 ErrorGroupID FROM ErrorData WHERE SystemID = 1 AND (ErrorCode = 9999 OR ErrorCode ISNULL) ORDER BY ISNULL(ErrorCode,99999)