case expression

Thumbnail image for Conditional Unique Index on Multiple Columns

Conditional Unique Index on Multiple Columns

Oracle’s function-based indexes can be used to build conditional uniqueness on table columns. Here’s a working example of a conditional unique index on multiple columns.

Read the Full Article →
Thumbnail image for SQL: Compare Two Tables for Differences in Data

SQL: Compare Two Tables for Differences in Data

How to compare two tables that share the same structure, to identify data differences between the two tables: missing rows, extra rows, or mismatch in attribute values.

Read the Full Article →
Thumbnail image for SQL to Select Rows Conditionally Based On Column Value

SQL to Select Rows Conditionally Based On Column Value

SQL solution to select rows conditionally based on column value – for example, a priority column tagged to a set of rows, with a requirement to select only the rows with top priority.

Read the Full Article →
Thumbnail image for IN = EXISTS, but beware: NOT IN is not the same as NOT EXISTS!

IN = EXISTS, but beware: NOT IN is not the same as NOT EXISTS!

In Oracle SQL queries, IN and EXISTS are interchangeable. Many of us assume therefore, that NOT IN and NOT EXISTS are also interchangeable. That’s not the case though.

Read the Full Article →
Thumbnail image for ORA-06592: CASE_NOT_FOUND Exception

ORA-06592: CASE_NOT_FOUND Exception

Though CASE behaves like IF-THEN-ELSE, it differs in one important way – CASE requires a defined leg for each scenario. If an undefined leg is chosen, the exception ORA-06592: CASE not found while executing CASE statement is raised.

Read the Full Article →
Thumbnail image for Simple CASE vs. Searched CASE

Simple CASE vs. Searched CASE

The CASE construct in Oracle has two variants – the simple CASE and the searched CASE. Here’s a closer look to compare them in structure and functionality.

Read the Full Article →
Thumbnail image for The Difference Between DECODE and CASE

The Difference Between DECODE and CASE

Both DECODE and CASE statements in Oracle are used for IF-THEN-ELSE conditional operations, but there are distinct differences in their power and behavior.

Read the Full Article →