Product:
Microsoft SQL Azure database
Issue:
How in a view, list a value, instead of blank – in case the record have no value in that column?
Solution:
Use
COALESCE(NULLIF([columntocheck],”), [columntoshowinstead]) AS [nameofcolumninview]
Where the function check if equal to ”, and then replace with other value listed.
e,g, if columntocheck is ” we replace it with a value from column columntoshowinstead.
The View can be created with SQL like this:
CREATE VIEW [DBO].[KontoView] ------------------------------------------------------------ -- example, you need to adjust it to your table data ------------------------------------------------------------ AS SELECT [key_Konto] as 'Konto_KEY' ,[Typ] ,[Konto] ,[Konto_Text] ,[Konto_Parent] ,[Konto] + ' ' + [Typ] + ' ' + [Konto_Parent] AS 'Konto_with_parent' ,[Area] , COALESCE(NULLIF([konto_uniq],''), [KONTO]) AS [Konto_Uniq] -- ,[startDate] -- ,[endDate] -- ,[IsActive] FROM [DBO].[KontoTable] WHERE [IsActive] = 'Y'
More information:
https://blog.devart.com/null-vs-empty-values-in-sql-server.html
https://www.w3schools.com/sql/func_sqlserver_nullif.asp
SELECT COALESCE(NULLIF(SomeColumn,''), ReplacementColumn)
FROM SomeTable
https://bytescout.com/blog/coalesce-nullif-isnull-functions-in-sql.html
https://learn.microsoft.com/en-us/sql/t-sql/language-elements/coalesce-transact-sql?view=sql-server-ver17
https://www.w3schools.com/sql/func_sqlserver_coalesce.asp
https://www.w3schools.com/sql/sql_isnull.asp
The SQL Server ISNULL() function lets you return an alternative value when an expression is NULL:
SELECT ProductName, UnitPrice * (UnitsInStock + ISNULL(UnitsOnOrder, 0))
FROM Products;
or we can use the COALESCE() function, like this:
SELECT ProductName, UnitPrice * (UnitsInStock + COALESCE(UnitsOnOrder, 0))
FROM Products;