Database Management Systems
If you’re getting SQL Server error number 189 that reads “The greatest function requires 1 to 254 arguments“, it’s probably because you’re either passing too few or too many arguments.
As the error message alludes to, you need to pass at least 1 argument, and no more than 254 arguments when using the GREATEST() function.
To fix this issue, be sure to pass at least 1 argument, and no more than 254 arguments.
Continue readingIn SQL NULL is a special value, or mark, that is used to indicate the absence of any data value. And this is the case with SQL Server.
But SQL Server also has a special use of NULL in certain cases.
In SQL Server, we can use the DATETRUNC() function to truncate a date/time value to a specified precision.
For example, we could use it to truncate a date value like 2024-10-25 to 2024-01-01, or a time value like 10:35:12 to 10:00:00.
The DATETRUNC() function was introduced in SQL Server 2022 (16.x).
If you’re getting an error message that reads “The function ‘LAST_VALUE’ must have an OVER clause with ORDER BY.” when using the LAST_VALUE() function in SQL Server, it’s probably because you’ve omitted the ORDER BY clause from the OVER clause.
The LAST_VALUE() function requires an OVER clause that contains an ORDER BY clause. This error happens when we include the OVER clause but not the ORDER BY clause.
To fix this error, add an ORDER BY clause to the OVER clause.
In SQL Server, we can use the TRIM() function to remove leading and trailing characters from a given string.
A common use case is to remove whitespace from the start and end of the string, but we can also specify other characters to remove.
Also, as from SQL Server 2022, we can specify which side of the string to remove the characters from (i.e. leading, trailing, or both).
Continue readingIn SQL Server, we can use the T-SQL RTRIM() function to remove trailing blanks from a given string. Trailing blanks are white spaces, tabs, etc that come at the end of the string.
Also, as from SQL Server 2022, we can specify other trailing characters to remove from the string.
Continue readingIn SQL Server, we can use the T-SQL LTRIM() function to remove leading blanks from a given string.
Also, as from SQL Server 2022, we can specify other leading characters to remove from the string.
Continue readingIf you’re getting SQL Server error msg 402 that tells you the “data types” are “incompatible in the approx_percentile_cont operator“, it’s probably because you’re trying to apply the APPROX_PERCENTILE_CONT() function against a non-numeric column (or one that doesn’t evaluate to a numeric type).
When using the APPROX_PERCENTILE_CONT() function, the ORDER BY expression in the WITHIN GROUP clause must evaluate to an exact or approximate numeric type. Other data types are not allowed, and will result in the above error.
To fix this issue, be sure to apply the function against a numeric column/expression.
Continue readingIn SQL Server STRING_SPLIT() is a table-valued function that splits a string into rows of substrings, based on a specified separator character.
Each substring is returned on its own row, in a column called value.
In SQL Server the CHOOSE() function returns the item at the specified index from a list of values.