Here are a couple of ways to return the number of days in a given month in Oracle Database. This could be the number of days in the current month, or the number of days in a month based on a specified date.
Continue readingCategory: Oracle
How to Get the Last Day of the Month in Oracle
In Oracle Database we can use the LAST_DAY() function to return the last day of a given month. This can be the last day of the current month or the last day of month based on a specified date.
2 Ways to Delete Duplicate Rows in Oracle
The following options can be used to delete duplicate rows in Oracle Database.
These examples delete duplicate rows but keep one. So if there are three identical rows for example, it deletes two of them and keeps one. This is often referred to as de-duping the table.
Continue reading2 Ways to Return Rows that Don’t Contain Numeric Values in Oracle
The following examples return only those rows that don’t contain any numeric digits in a given column in Oracle Database.
Continue reading11 Ways to Find Duplicate Rows that have a Primary Key in Oracle
Here are eleven options for returning duplicate rows in Oracle Database when those rows have a primary key or some other unique identifier column and you want to ignore it.
Continue readingFix Error “ORA-01789: query block has incorrect number of result columns”
If you’re getting the error “ORA-01789: query block has incorrect number of result columns” in Oracle Database, then it’s probably because you’re trying to use an operator such as UNION, INTERSECT, or EXCEPT to run a compound query, but the SELECT statements on either side of the operator return a different number of columns.
To fix this, simply ensure that both queries return the same number of columns.
Continue readingDetect Whether a Value Contains at Least One Numerical Digit in Oracle
The following example returns all rows that contain at least one numerical digit in Oracle Database.
Continue readingFind All Non-Numeric Values in a Column in Oracle
If you ever encounter a character column that should be a numeric column, there’s always a possibility that it contains non-numeric data that you don’t know about.
In Oracle Database, you can run a query like the following to return non-numeric data from the column.
Continue readingFix Error “ORA-01790: expression must have same datatype as corresponding expression”
If you’re getting the error “ORA-01790: expression must have same datatype as corresponding expression” in Oracle Database, it’s probably because you’re using an operator such as UNION, INTERSECT, or EXCEPT to run a compound query, but the columns returned by each query use different data type groups.
To fix this issue, you’ll need to ensure that each column returned by the second query uses the same data type group as the corresponding column in the first query.
Continue reading5 Ways to Select Rows with the Minimum Value for their Group in SQL
Here are five options for using SQL to return only those rows that have the minimum value within their group.
These examples work in most major RDBMSs, including MySQL, MariaDB, Oracle, PostgreSQL, SQLite, and SQL Server.
Continue reading