If you’re using SQL Server’s GENERATE_SERIES() function/relational operator and you’re getting an empty result set, then it could be due to one of the following reasons.
Tag: how to
CREATE TABLE IF NOT EXISTS Equivalent in SQL Server
In SQL, we can use the CREATE TABLE IF NOT EXISTS statement to create a table only if it doesn’t exist. The benefit of doing this is that we won’t get an error if there’s already a table with the same name.
But SQL Server doesn’t support this syntax – at least not in the current version of SQL Server at the time of writing (SQL Server 2022) .
So with SQL Server, we need to do a bit of extra work.
Continue readingFix “Argument value 0 is invalid for argument 3 of generate_series function” in SQL Server
If you’re getting SQL Server error 4199 that reads “Argument value 0 is invalid for argument 3 of generate_series function“, it’s probably because you’re passing zero as the third argument to the GENERATE_SERIES() function.
The GENERATE_SERIES() function accepts an optional third argument, but this argument can’t be zero.
To fix this error, either pass a non-zero expression as the third argument, or omit the argument altogether (in order to use the default step of 1).
Continue readingGenerate a Time Series in SQL Server
SQL Server’s GENERATE_SERIES() function returns a series of numbers within a given interval. But just because it returns numbers, doesn’t mean we can’t leverage its functionality in order to generate a series of time values.
If you need to create a series of time values with evenly spaced intervals between a start and end point, maybe the following technique can help.
Continue readingFix “The ORDER BY in WITHIN GROUP clause of ‘APPROX_PERCENTILE_CONT’ function must have exactly one expression” in SQL Server
If you’re getting SQL Server error 10751 that reads “The ORDER BY in WITHIN GROUP clause of ‘APPROX_PERCENTILE_CONT’ function must have exactly one expression” it’s probably because you’re using too many ORDER BY expressions with the APPROX_PERCENTILE_CONT() function.
The APPROX_PERCENTILE_CONT() function requires the WITHIN GROUP clause, and that clause requires an ORDER BY sub-clause. However, that ORDER BY sub-clause requires exactly one expression – no more, no less. So, you can’t pass multiple expressions, and you can’t pass zero expressions. It must be exactly one expression.
To fix this issue, be sure to have just one ORDER BY expression in the WITHIN GROUP clause when using the APPROX_PERCENTILE_CONT() function.
Fixing the Error: “The function ‘LAG’ must have an OVER clause with ORDER BY” in SQL Server
If you’re getting error message 4112 that reads “The function ‘LAG’ must have an OVER clause with ORDER BY” in SQL Server, it’s probably because you’re omitting the ORDER BY clause from the OVER clause when using the LAG() function.
The LAG() 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.
Why GENERATE_SERIES() Only Returns the First Value in the Series in SQL Server
If you’re using the GENERATE_SERIES() function to create a series of numbers, but you’re finding that only the first value in the series is returned, it could be something very obvious.
The obvious reason this could happen is that your step value is too big. In particular, if the step is so big that it covers the whole series, then it stands to reason that there will only be one value in the series.
If this is an issue for you, you might want to check that you’re using an appropriate step value. Using a smaller step value can help to create a series with more values.
Continue readingOracle Adds Support for IF EXISTS and IF NOT EXISTS Syntax Modifiers
Many RDBMSs implement IF EXISTS and IF NOT EXISTS syntax modifiers that can be used with DDL object creation, modification, and deletion, such as CREATE TABLE and DROP TABLE statements, to name just a couple.
These syntax modifiers allow us to run such statements without getting an error in the event that the object already exists (if we’re trying to create it or modify it) or doesn’t exist (if we’re trying to drop it).
Continue readingFix “An insufficient number of arguments were supplied for the procedure or function GENERATE_SERIES” in SQL Server
If you’re getting error 313 with a message that reads “An insufficient number of arguments were supplied for the procedure or function GENERATE_SERIES” in SQL Server, it’s because you’re not passing enough arguments to the GENERATE_SERIES() function.
The GENERATE_SERIES() function/relational operator accepts a minimum of two arguments, and a maximum of three (at least, this is the case at the time of writing). Passing less than two arguments will result in the above error.
To fix this error, be sure to provide at least two arguments when using the GENERATE_SERIES() function.
Fix Error Msg 9810 “The datepart … is not supported by date function datetrunc for data type” in SQL Server
If you’re getting SQL Server error msg 9810 that tells you that the datepart “is not supported by date function datetrunc for data type“, it’s probably because you’re using an invalid datepart argument when using the DATETRUNC() function.
For example, this error can occur when using a time date part on a date value (i.e. one that doesn’t have a time component). Conversely, it can also occur when using a date date part on a time value (i.e. one that doesn’t have a date component).
Continue reading