Tag: how to

Fix “Invalid object name ‘GENERATE_SERIES'” in SQL Server

Posted on by Ian

If you’re getting SQL Server error 208 that reads “Invalid object name ‘GENERATE_SERIES’“, it could be that you’re calling GENERATE_SERIES() in a version of SQL Server that doesn’t support this function.

The GENERATE_SERIES() function was introduced in SQL Server 2022 (16.x), and so if we try to call it in an earlier version of SQL Server, we’ll get the above error.

So if you’re running this on an earlier version, you’ll need to upgrade before you can run it successfully. You’ll also need to run it on a database with a compatibility level of at least 160.

Continue reading →

Fix “Parse error: all VALUES must have the same number of terms” in SQLite when using the VALUES Stand Alone Statement

Posted on by Ian

If you’re getting an error that reads “Parse error: all VALUES must have the same number of terms” in SQLite when using the VALUES clause as a stand alone statement, it’s probably because you’re not providing the same number of columns in all rows.

When we use VALUES to create a constant table, we must provide the same number of columns in each row.

To fix this issue, be sure to provide the same number of columns across all rows.

Continue reading →

Fix Error “The function ‘DENSE_RANK’ must have an OVER clause” in SQL Server

Posted on by Ian

If you’re getting error 10753 when using a window function in SQL Server, it’s probably because you’re calling the function without an OVER clause.

When using the DENSE_RANK() function, the error message reads “The function ‘DENSE_RANK’ must have an OVER clause”.

The DENSE_RANK() function requires an OVER clause (and that clause must have an ORDER BY clause).

To fix this issue, add an OVER clause to the DENSE_RANK() function.

Continue reading →

Fix SQLite ‘Parse error: near “ORDER”‘ When Using VALUES as a Stand Alone Statement

Posted on by Ian

If you’re getting an error that reads ‘Parse error: near “ORDER”‘ in SQLite, it could be that you’re trying to use the ORDER BY clause when using the VALUES clause as a stand alone statement.

Although we can certainly use the VALUES clause as a stand alone SQL statement, we can’t apply the ORDER BY clause against it.

However all is not lost. Below is an example of how we can sort the results of the VALUES statement.

Continue reading →

Fix “‘GENERATE_SERIES’ is not a recognized built-in function name” in SQL Server

Posted on by Ian

If you’re getting an error that reads “‘GENERATE_SERIES’ is not a recognized built-in function name” in SQL Server, it could be that you’re calling GENERATE_SERIES() in the wrong context.

In SQL Server, GENERATE_SERIES() is a relational operator that returns a series of values from a given start and stop point.

When we call GENERATE_SERIES() in a SQL query, we don’t include it in the SELECT list, like we would with many SQL functions. Instead, we reference it in the FROM clause, like we would reference a table.

So if you’re getting the above error, it could be that you’re inadvertently making this mistake. To fix, be sure to call GENERATE_SERIES() in the correct context.

Continue reading →

How to use VALUES as a Stand Alone SQL Statement in PostgreSQL

Posted on by Ian

Many of us who work with SQL databases have used the VALUES keyword with the INSERT statement when inserting data into a database.

But perhaps a lesser known fact is that some RDBMSs allow us to use VALUES as a stand alone SQL statement. PostgreSQL is one such RDBMS.

When used as a stand alone statement, we can use a VALUES statement instead of a SELECT statement.

Continue reading →

How to Fix “The function ‘CUME_DIST’ must have an OVER clause with ORDER BY” Error in SQL Server

Posted on by Ian

If you’re getting error message 4112 that reads “The function ‘CUME_DIST’ must have an OVER clause with ORDER BY” when using the CUME_DIST() function, it’s probably because you’re omitting the ORDER BY clause from the OVER clause.

When using the CUME_DIST() function in SQL Server, we must include an OVER clause that contains an ORDER BY clause. This error happens when we provide the OVER clause but not the ORDER BY clause.

To fix this error, add an ORDER BY clause to the OVER clause.

Continue reading →

Check if Table Exists in SQL

Posted on by Ian

With SQL we can use various methods to check whether or not a table (or other object) exists in the database. The method we use will often depend on the RDBMS we’re using, as well as the task we’re trying to undertake.

There’s usually a reason we’re trying to check for the existence of a table, and often the syntax we use will be tied to that reason. For example the ...IF EXISTS clause is a handy addition to the DROP TABLE statement, and the ...IF NOT EXISTS clause can often be used with the CREATE TABLE statement.

Other times we may simply want to see if the table exists without performing any immediate actions against that table. In such cases, we would need to run code specifically to see if the table exists.

Below are examples of code we can use in each of the above scenarios.

Continue reading →