The following example returns all rows that contain at least one numerical digit in Oracle Database.
Continue readingFix “ERROR 1250 (42000): Table ‘…’ from one of the SELECTs cannot be used in ORDER clause” in MariaDB
If you’re getting “ERROR 1250 (42000): Table ‘…’ from one of the SELECTs cannot be used in ORDER clause”, it’s probably because you’re qualifying a column name with its table name when using an operator such as UNION
, INTERSECT
, or EXCEPT
in MariaDB.
To fix this, either remove the table name or use a column alias.
Continue readingExample of a Simple Merge Statement in SQL Server
The following example uses T-SQL to merge two tables in SQL Server. It uses the VALUES
table value constructor as the source table.
PostgreSQL DATEADD() Equivalent
Updated 20 April 2024 to include the date_add()
function.
SQL Server has its DATEADD()
function that adds an interval to a date value. MySQL’s DATE_ADD()
and ADDDATE()
for does the same thing, as does MariaDB’s DATE_ADD()
and ADDDATE()
. SQLite has a DATE()
function that also provides the option of adding an interval to a given date.
Prior to version 16, PostgreSQL didn’t have a DATEADD()
or equivalent function. But with PostgreSQL 16 came with the introduction of the date_add()
function, which allows us to add an interval to a timestamp with time zone.
We can also add and subtract values from dates with date/time operators such as +
and -
.
3 Ways to Convert an Integer to Decimal in SQL Server
Here are three options for converting an integer to a decimal value in SQL Server using T-SQL.
Continue readingPostgreSQL VALUES Command Explained
In PostgreSQL, the VALUES
command returns a set of one or more rows as a table. It’s a table value constructor that can be used as part of a larger command, or as a standalone SQL statement.
SQL Server SHOW TABLES Equivalent
Every now and then I find myself typing SHOW TABLES
in SQL Server, expecting to get a list of tables.
That would make perfect sense if I was using MySQL or MariaDB. But SQL Server/T-SQL doesn’t have a SHOW TABLES
statement like MySQL or MariaDB, so it never works. And I keep forgetting. But fortunately, SQL Server does have alternatives.
Here are five options for getting a list of tables in SQL Server. These can be used whenever you’re trying to find that elusive SHOW TABLES
statement in SQL Server.
SQLite INTERSECT Operator
In SQLite, the INTERSECT
operator is used to create a compound SELECT
statement that returns the intersection of the results of the left and right SELECT
statements. In other words, it combines two queries, but returns only those rows that are returned in both queries.
SQLite EXCEPT Operator
In SQLite, the EXCEPT
operator can be used to create a compound SELECT
statement that returns the subset of rows returned by the left SELECT
that are not returned by the right SELECT
.
Fix Msg 512 “Subquery returned more than 1 value” in SQL Server
If you get error Msg 512 that reads “Subquery returned more than 1 value…” in SQL Server, it’s because you’re using a subquery that returns more than one value in a scenario where this is not allowed.
Continue reading