When we need to enforce uniqueness on a combination of table columns *only if* a certain condition is true, Oracle helps us implement this using a function-based index.
{ 2 comments }
case expression
An approach for comparing two tables for differences in data, using a pure SQL solution:
In a recent project, a shadow table had to be compared periodically with its main source table to identify the differences between the two tables.
The nature of differences fell into one of these buckets:
- Insert Required [INS]: If the source had rows not present in the shadow table
- Update Required [UPD]: If one or more columns in a row (identified by a primary key) had changed values
- Delete Required [DEL]: If the shadow table had rows not present in the source table
Comparing two tables for differences (INS/UPD/DEL) needed a different solution from a simple MERGE SQL statement. Here an upsert was not to be executed, only the nature of differences to be identified.
{ 0 comments }
Let’s say a table contains multiple rows for an id. The requirement is to select only one of those rows, based on the value in a "type" column which determines the row’s priority.
A typical example is selecting one contact number for a customer, based on contact types.
{ 0 comments }
In Oracle SQL queries, IN and EXISTS are interchangeable. Many of us assume therefore, that NOT IN and NOT EXISTS are also interchangeable.
A big mistake.
See how NOT IN and NOT EXISTS behave differently in this small example.
{ 2 comments }
CASE is a smarter rewrite for IF-THEN-ELSE, we said. It is for sure, but there is a difference in the way the ELSE part of it is handled.
Compare the code units below, one using CASE WHEN the other using IF-ELSIF. Both are identical in logic – two defined conditions, no ELSE path.
{ 0 comments }
The CASE construct in Oracle has two variants – the simple CASE and the searched CASE. We saw examples of both kinds in the topic The Difference Between DECODE and CASE.
Let’s have a closer look to compare simple CASE and searched CASE in structure and functionality.
{ 0 comments }
DECODE and CASE statements in Oracle both provide a conditional construct, of this form:
if A = n1 then A1
else if A = n2 then A2
else X
Databases before Oracle 8.1.6 had only the DECODE function. CASE was introduced in Oracle 8.1.6 as a standard, more meaningful and more powerful function.
Everything DECODE can do, CASE can. There is a lot else CASE can do though, which DECODE cannot. We’ll go through detailed examples in this article.
{ 71 comments }
