example case oracle

Oracle Basics

AND & OR
COMPARISON OPERATORS
IS NOT NULL
REGEXP_LIKE

Oracle Advanced

Alter Table
Alter Tablespace
Change Password
Check Constraints
Comments in SQL
Create Schema
Create Schema Statement
Create Table
Create Table As
Create Tablespace
Create User
Declare Variables
Drop Tablespace
Error Messages
Find Default Tablespace
Find Users Logged In
Find Version Information
Global Temporary
Grant/Revoke Privileges
Local Temporary
Primary Keys
Set Default Tablespace
System Tables
Unique Constraints

Oracle Cursors

Close Cursor
Cursor Attributes
Declare Cursor
Fetch Cursor
Open Cursor
Select For Update
Where Current Of

Oracle Exception Handling

Named Programmer-Defined Exception
Named System Exception
WHEN OTHERS Clause

Oracle Foreign Keys

Disable Foreign Key
Drop Foreign Key
Enable Foreign Key
Foreign Key
Foreign Key (cascade delete)
Foreign Key (set null delete)

Oracle Loops/Conditionals

CURSOR FOR LOOP
IF-THEN-ELSE
REPEAT UNTIL LOOP

Oracle Transactions

Commit Transaction
Rollback Transaction
Set Transaction

Oracle Triggers

After Delete Trigger
After Insert Trigger
After Update Trigger
Before Delete Trigger
Before Insert Trigger
Before Update Trigger
Disable All Triggers
Disable Trigger
Drop Trigger
Enable All Triggers
Enable Trigger

String/Char Functions

Concat with ||
REGEXP_INSTR
REGEXP_REPLACE
REGEXP_SUBSTR

Numeric/Math Functions

REGEXP_COUNT
ROUND (numbers)
TRUNC (numbers)

Date/Time Functions

CURRENT_DATE
CURRENT_TIMESTAMP
LOCALTIMESTAMP
MONTHS_BETWEEN
ROUND (dates)
SESSIONTIMEZONE
SYSTIMESTAMP
TRUNC (dates)

Conversion Functions

CHARTOROWID
NUMTODSINTERVAL
NUMTOYMINTERVAL
TO_DSINTERVAL
TO_MULTI_BYTE
TO_SINGLE_BYTE
TO_TIMESTAMP
TO_TIMESTAMP_TZ
TO_YMINTERVAL

Analytic Functions

FIRST_VALUE

Advanced Functions

CARDINALITY
SYS_CONTEXT

Oracle / PLSQL: CASE Statement

This Oracle tutorial explains how to use the Oracle/PLSQL CASE statement with syntax and examples.

Description

The Oracle/PLSQL CASE statement has the functionality of an IF-THEN-ELSE statement. Starting in Oracle 9i, you can use the CASE statement within a SQL statement.

The syntax for the CASE statement in Oracle/PLSQL is:

Parameters or Arguments

The CASE statement returns any datatype such as a string, numeric, date, etc. (BUT all results must be the same datatype in the CASE statement.) If all conditions are not the same datatype, an ORA-00932 error will be returned. If all results are not the same datatype, an ORA-00932 error will be returned. If no condition is found to be true, then the CASE statement will return the value in the ELSE clause. If the ELSE clause is omitted and no condition is found to be true, then the CASE statement will return NULL.

You can have up to 255 comparisons in a CASE statement. Each WHEN ... THEN clause is considered 2 comparisons.

The CASE statement can be used in the following versions of Oracle/PLSQL:

Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i

The CASE statement can be used in Oracle/PLSQL.

You could use the CASE statement in a SQL statement as follows: (includes the expression clause)

Or you could write the SQL statement using the CASE statement like this: (omits the expression clause)

The above two CASE statements are equivalent to the following IF-THEN-ELSE statement:

The CASE statement will compare each owner value, one by one.

One thing to note is that the ELSE clause within the CASE statement is optional. You could have omitted it. Let's look at the SQL statement above with the ELSE clause omitted.

Your SQL statement would look as follows:

With the ELSE clause omitted, if no condition was found to be true, the CASE statement would return NULL.

Comparing 2 Conditions

Here is an example that demonstrates how to use the CASE statement to compare different conditions:

Frequently Asked Questions

Question: Can you create a CASE statement that evaluates two different fields? I want to return a value based on the combinations in two different fields.

Answer: Yes, below is an example of a case statement that evaluates two different fields.

So if supplier_name field is IBM and the supplier_type field is Hardware , then the CASE statement will return North office . If the supplier_name field is IBM and the supplier_type is Software , the CASE statement will return South office .

Home | About Us | Contact Us | Testimonials | Donate

While using this site, you agree to have read and accepted our Terms of Service and Privacy Policy .

Oracle Case Statement Explained with Tips and Examples

We have seen working of Oracle Decode processing in the previous post

Oracle sql decode processing

Now let us see Oracle Case statement processing

Table of Contents

Case statement in Oracle

It is similar to the Decode statement. 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 also. There is a lot else CASE can do though, which DECODE cannot. We’ll go through detailed examples in this article

Let’s start with the Case statement Syntax

expression is optional

We can divide the Case statement into two categories Simple Case statement and Searchable case statement

Simple case statement is just like Decode function.

Example with Simple CASE statement

Searchable Case statement are case statement where we specify a condition or predicate (case statement in oracle with multiple conditions)

Nested Oracle Case statement

This is case statement within the case statement

Important points about Simple and searchable Case statement

(1) The searched CASE evaluates the conditions independently under each of the “when” options. With this structure, far more complex conditions can be implemented with a searched CASE than a simple CASE.

(2) A searched CASE can combine multiple tests using several columns, comparisons, and AND/OR operators.

(3) Both simple and searched CASE constructs, the conditions are evaluated sequentially from top to bottom, and execution exits after the first match are found. So, suppose more than one condition is true, only the first action is considered.

(4) Oracle Database uses short-circuit evaluation. That is, for a simple CASE expression, the database evaluates each comparison_expr value only before comparing it to expr, rather than evaluating all comparison_expr values before comparing any of them with expr. Consequently, Oracle never evaluates a comparison_expr if a previous comparison_expr is equal to expr. For a searched CASE expression, the database evaluates each condition to determine whether it is true, and never evaluates a condition if the previous condition was true

Now lets see the difference between Case and Decode statement

(1) DECODE can work with only scalar values but CASE can work with logical operators, predicates, and searchable subqueries.

We know that decode can work with scalar values. We cannot use it for Logical operators. We have to convert it into scalar values to make use of that.

Case makes the whole process easier. We can easily use logical operator in the Case statement

The above is called searchable Case statements

(2) CASE can work as a PL/SQL construct but DECODE is used only in SQL statements.CASE can be used as a parameter of a function/procedure.

Grade V_x is Excellent.

(3) CASE expects datatype consistency, DECODE does not.

(4) CASE complies with ANSI SQL. DECODE is proprietary to Oracle.

(5) CASE executes faster in the optimizer than does DECODE.

(6) CASE is a statement while DECODE is a function.

Related Articles Oracle sql Tutorials : Listing of all the sql tutorial lessons which can be used to master sql and use in RDBMS (Oracle,MySql) data management and manipulation Oracle interview questions : Check out this page for Top 49 Oracle Interview questions and answers : Basics , Oracle SQL to help you in interviews. oracle PLSQL records : Check out this article on the working of oracle PLSQL records . Also, find out the various ways to define it and assign value to it Oracle SQL developer tool : Check out this page for all the information on the Oracle sql developer tool,How to do Oracle sql developer download, how to install oracle date functions : Check out this post for oracle date functions, oracle date difference in years,oracle date difference in days, oracle date difference in months. https://docs.oracle.com/cd/B19306_01/server.102/b14200/expressions004.htm

Recommended Courses

How to add datafile to tablespace in oracle.

Oracle tablespace stores the data in the Oracle database and as the data grows, we often need to add space to the tablespace. We can…

Kill session in Oracle

Alter system kill session command alter system kill session is a very important command for Oracle DBA. We can kill oracle session in the oracle…

How to create sql baseline from cursor cache in Oracle

SQL Plan Baselines in Oracle Database are a method for maintaining the consistency of SQL statements’ performance.SQL baseline can be created from cursor cache, AWR,…

How to set table level preference setting

Sometimes, we may need to set the optimizer statistics preferences at the table level. We will check in detail about it How to set table…

Oracle PL/SQL Programming, 5th Edition by Steven Feuerstein, Bill Pribyl

Get full access to Oracle PL/SQL Programming, 5th Edition and 60K+ other titles, with a free 10-day trial of O'Reilly.

There are also live events, courses curated by job role, and more.

CASE Statements and Expressions

The CASE statement allows you to select one sequence of statements to execute out of many possible sequences. They have been part of the SQL standard since 1992, although Oracle SQL didn’t support CASE until the release of Oracle8 i Database, and PL/SQL didn’t support CASE until Oracle9 i Database Release 1. From this release onwards, the following types of CASE statements are supported:

Associates each of one or more sequences of PL/SQL statements with a value. Chooses which sequence of statements to execute based on an expression that returns one of those values.

Chooses which of one or more sequences of PL/SQL statements to execute by evaluating a list of Boolean conditions. The sequence of statements associated with the first condition that evaluates to TRUE is executed.

NULL or UNKNOWN?

Earlier I stated that the result from a Boolean expression can be TRUE, FALSE, or NULL. In PL/SQL that is quite true, but in the larger realm of relational theory it’s considered incorrect to speak of a NULL result from a Boolean expression. Relational theory says that a comparison to NULL, such as:

yields the Boolean value UNKNOWN. And UNKNOWN is not the same as NULL. That PL/SQL refers to UNKNOWN as NULL is not something you should lose sleep over. I want you to be aware though, that UNKNOWN is the true third value in three-valued logic. And now I hope you’ll never be caught (as I have been a few times!) using the wrong term when discussing three-valued logic with experts on relational theory.

In addition to CASE statements, PL/SQL also supports CASE expressions. A CASE expression is very similar in form to a CASE statement and allows you to choose which of one or more expressions to evaluate. The result of a CASE expression is a single value, whereas the result of a CASE statement is the execution of a sequence of PL/SQL statements.

Simple CASE Statements

A simple CASE statement allows you to choose which of several sequences of PL/SQL statements to execute based on the results of a single expression. Simple CASE statements take the following form:

The ELSE portion of the statement is optional. When evaluating such a CASE statement, PL/SQL first evaluates expression . It then compares the result of expression with result1 . If the two results match, statements1 is executed. Otherwise, result2 is checked, and so forth.

Following is an example of a simple CASE statement that uses the employee type as a basis for selecting the proper bonus algorithm:

This CASE statement has an explicit ELSE clause; however, the ELSE is optional. When you do not explicitly specify an ELSE clause of your own, PL/SQL implicitly uses the following:

In other words, if you don’t specify an ELSE clause, and none of the results in the WHEN clauses match the result of the CASE expression, PL/SQL raises a CASE_NOT_FOUND error. This behavior is different from what I’m used to with IF statements. When an IF statement lacks an ELSE clause, nothing happens when the condition is not met. With CASE, the analogous situation leads to an error.

By now you’re probably wondering how, or even whether, the bonus logic shown earlier in this chapter can be implemented using a simple CASE statement. At first glance, it doesn’t appear possible. However, a bit of creative thought yields the following solution:

The key point to note here is that the expression and result elements shown in the earlier syntax diagram can be either scalar values or expressions that evaluate to scalar values.

If you look back to the earlier IF-THEN-ELSIF statement implementing this same bonus logic, you’ll see that I specified an ELSE clause for the CASE implementation, whereas I didn’t specify an ELSE for the IF-THEN-ELSIF solution. The reason for the addition of the ELSE is simple: if no bonus conditions are met, the IF statement does nothing, effectively resulting in a zero bonus. A CASE statement, however, will raise an error if no conditions are met—hence the need to code explicitly for the zero bonus case.

To avoid CASE_NOT_FOUND errors, be sure that it’s impossible for one of your conditions not to be met.

While my previous CASE TRUE statement may look like a clever hack, it’s really an explicit implementation of the searched CASE statement, which I talk about in the next section.

Searched CASE Statements

A searched CASE statement evaluates a list of Boolean expressions and, when it finds an expression that evaluates to TRUE, executes a sequence of statements associated with that expression. Essentially, a searched CASE statement is the equivalent of the CASE TRUE statement shown in the previous section.

Searched CASE statements have the following form:

A searched CASE statement is a perfect fit for the problem of implementing the bonus logic. For example:

As with simple CASE statements, the following rules apply:

Execution ends once a sequence of statements has been executed. If more than one expression evaluates to TRUE, only the statements associated with the first such expression are executed.

The ELSE clause is optional. If no ELSE is specified, and no expressions evaluate to TRUE, then a CASE_NOT_FOUND exception is raised.

WHEN clauses are evaluated in order, from top to bottom.

Following is an implementation of my bonus logic that takes advantage of the fact that WHEN clauses are evaluated in the order in which I write them. The individual expressions are simpler, but is the intent of the statement as easily grasped?

If a given employee’s salary is $20,000, then the first expression and second expression will evaluate to FALSE. The third expression will evaluate to TRUE, and that employee will be awarded a bonus of $1,500. If an employee’s salary is $21,000, then the second expression will evaluate to TRUE, and the employee will be awarded a bonus of $1,000. Execution of the CASE statement will cease with the first WHEN condition that evaluates to TRUE, so a salary of $21,000 will never reach the third condition.

It’s arguable whether you should take this approach to writing CASE statements. You should certainly be aware that it’s possible to write such a statement, and you should watch for such order-dependent logic in programs that you are called upon to modify or debug.

Order-dependent logic can be a subtle source of bugs when you decide to reorder the WHEN clauses in a CASE statement. Consider the following searched CASE statement in which, assuming a salary of $20,000, both WHEN expressions evaluate to TRUE:

Imagine the results if a future programmer unthinkingly decides to make the code neater by reordering the WHEN clauses in descending order by salary. Don’t scoff at this possibility! We programmers frequently fiddle with perfectly fine, working code to satisfy some inner sense of order. Following is the CASE statement rewritten with the WHEN clauses in descending order:

Looks good, doesn’t it? Unfortunately, because of the slight overlap between the two WHEN clauses, I’ve introduced a subtle bug into the code. Now an employee with a salary of $20,000 gets a bonus of $1,000 rather than the intended $1,500. There may be cases where overlap between WHEN clauses is desirable, but avoid it when feasible. Always remember that order matters, and resist the urge to fiddle with working code. “If it ain’t broke, don’t fix it.”

Since WHEN clauses are evaluated in order, you may be able to squeeze some extra efficiency out of your code by listing the most likely WHEN clauses first. In addition, if you have WHEN clauses with “expensive” expressions (e.g., requiring lots of CPU and memory), you may want to list those last in order to minimize the chances that they will be evaluated. See Nested IF Statements for an example of this issue.

Use searched CASE statements when you want to use Boolean expressions as a basis for identifying a set of statements to execute. Use simple CASE statements when you can base that decision on the result of a single expression.

Nested CASE Statements

CASE statements can be nested just as IF statements can. For example, the following rather difficult-to-follow implementation of my bonus logic uses a nested CASE statement:

Any type of statement may be used within a CASE statement, so I could replace the inner CASE statement with an IF statement. Likewise, any type of statement, including CASE statements, may be nested within an IF statement.

CASE Expressions

CASE expressions do for expressions what CASE statements do for statements. Simple CASE expressions let you choose an expression to evaluate based on a scalar value that you provide as input. Searched CASE expressions evaluate a list of expressions to find the first one that evaluates to TRUE, and then return the results of an associated expression.

CASE expressions take the following two forms:

A CASE expression returns a single value, the result of whichever result_expression is chosen. Each WHEN clause must be associated with exactly one expression (no statements). Do not use semicolons or END CASE to mark the end of the CASE expression. CASE expressions are terminated by a simple END.

Following is an example of a simple CASE expression being used with the DBMS_OUTPUT package to output the value of a Boolean variable. (Recall that the PUT_LINE program is not overloaded to handle Boolean types.) In this example, the CASE expression converts the Boolean value into a character string, which PUT_LINE can then handle:

A searched CASE expression can be used to implement my bonus logic, returning the proper bonus value for any given salary:

You can use a CASE expression anywhere you can use any other type of expression or value. The following example uses a CASE expression to compute a bonus amount, multiplies that amount by 10, and assigns the result to a variable that is displayed via DBMS_OUTPUT:

Unlike with the CASE statement, no error is raised in the event that no WHEN clause is selected in a CASE expression. Instead, when no WHEN conditions are met, a CASE expression will return NULL.

Get Oracle PL/SQL Programming, 5th Edition now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Don’t leave empty-handed

Get Mark Richards’s Software Architecture Patterns ebook to better understand how to design components—and how they should interact.

It’s yours, free.

Check it out now on O’Reilly

Dive in for free with a 10-day trial of the O’Reilly learning platform—then explore all the other resources our members count on to build skills and solve problems every day.

SQL CASE Statement Explained with Examples

The SQL CASE statement allows you to perform IF-THEN-ELSE functionality within an SQL statement. Learn more about this powerful statement in this article.

This article applies to Oracle, SQL Server, MySQL, and PostgreSQL.

Table of Contents

What Does the SQL CASE Statement Do?

The CASE statement allows you to perform an IF-THEN-ELSE check within an SQL statement .

It’s good for displaying a value in the SELECT query based on logic that you have defined. As the data for columns can vary from row to row, using a CASE SQL expression can help make your data more readable and useful to the user or to the application.

It’s quite common if you’re writing complicated queries or doing any kind of ETL work.

SQL CASE Statement Syntax

The syntax of the SQL CASE expression is:

The CASE statement can be written in a few ways, so let’s take a look at these parameters.

Parameters of the CASE Statement

The parameters or components of the CASE SQL statement are:

expression (optional): This is the expression that the CASE statement looks for. If we’re comparing this to an IF statement, this is the check done inside the IF statement (e.g. for IF x > 10, the expression would be “x > 10”
condtion_1/condition_n (mandatory): These values are a result of the expression parameter mentioned. They are the possible values that expression can evaluate to. Alternatively, they can be an expression on their own, as there are two ways to write an SQL CASE statement (as explained below). They also relate to the IF statement in the IF-THEN-ELSE structure.
result_1/result_n (mandatory): These values are the value to display if the related condition is matched. They come after the THEN keyword and relate to the THEN part of the IF-THEN-ELSE structure.
result (optional): This is the value to display if none of the conditions in the CASE statement are true. It is the ELSE part of the IF-THEN-ELSE structure and is not required for the CASE SQL statement to work.
case_name (optional): This value indicates what the column should be referred to as when displayed on the screen or from within a subquery. It’s also called the column alias.

While we’re here, if you want an easy-to-use PDF guide for the main features in different database vendors, get my SQL Cheat Sheets here:

Simple and Searched CASE Expressions

There are actually two ways to use an SQL CASE statement, which are referred to as a “simple case expression” or a “searched case expression”.

Simple CASE expression

The expression is stated at the beginning, and the possible results are checked in the condition parameters.

For example:

Searched CASE expression

The expressions are used within each condition without mentioning it at the start of the CASE statement.

All data types for the expression and conditions for the Simple expressions, and all of the results for both expression types must be the same or have a numeric data type. If they all are numeric, then the database will determine which argument has the highest numeric precedence, implicitly convert the remaining argument to that data type, and return that datatype. Basically, it means the database will work out which data type to return for this statement if there is a variety of numeric data types (NUMBER, BINARY_FLOAT or BINARY_DOUBLE for example).

In SQL, IF statements in SELECT statements can be done with either of these methods. I’ll demonstrate this using more examples later in this article.

Want to see guides like this for all other Oracle functions? Check out this page here that lists all SQL functions along with links to their guides.

Examples of the CASE Statement

Here are some examples of the SQL CASE statement in SELECT queries. I find that examples are the best way for me to learn about code, even with the explanation above.

Simple CASE Statement

The results are:

This example is using the simple case statement structure. Notice how the expression (in this case the “country” field) comes right after the CASE keyword. This means the WHEN expressions are all compared to that field.

This example, like most of the examples here, shows the continent of each customer, based on their country.

Searched Case

This example performs the same check as the other examples but uses the searched case method. This means that each expression in the WHEN section is evaluated individually. It gives the same result as the previous example though.

Searched Case with Numbers

This example performs a searched CASE using a number field, which is the number of employees. Notice how the second WHEN expression has two checks – to see if the number is between 10 and 50.

CASE Statement With IN Clause

This example looks up the continent of the customer again. However, it uses an IN clause, which means the value is checked to see if it is in the IN parameter. It should have the same result, but it’s a bit cleaner and has less code.

Nested CASE Statement in SQL

This example shows a CASE statement within another CASE statement, also known as a “nested case statement” in SQL.

It first checks the country and then checks for a particular customer name to see if it is male or female (given that Sally is the only female here).

Notice how I didn’t give a name to the inner case statement. I didn’t need to – this is not displayed and the name is already specified for the Continent column.

We can see that the results show different values based on the nested CASE statement.

CASE Statement with Functions

This example uses the MOD function to demonstrate how you can use CASE statements with functions. It checks the number of employees and determines if they have an odd or even number of employees.

Multiple Matches

This example shows what happens if there are records that match with multiple WHEN expressions. For example, some customers may have both <1 employees and <10 employees. What happens here? The CASE statement finds the first matching expression and uses that.

CASE Statement in WHERE Clause

This example shows how the CASE statement is used in a WHERE clause.

This example shows all customers who live in North America, using the CASE statement to restrict the records.

CASE Statement Frequently Asked Questions

What’s an if statement.

In case you’re not sure, an IF statement allows you to do something if a condition is true, and something else if the condition is false.

It’s a common feature of many programming languages.

However, SQL isn’t like other programming languages.

It’s not procedural. It doesn’t make several steps on different variables to get the result you want.

It’s set based. You tell the database everything you want, and it returns a set of results.

So, how can you have an SQL IF statement?

I’ll be writing about how to write the IF statement in SQL.

What If No Match Is Found In A CASE Statement?

If there is no match found in any of the conditions, that’s where the ELSE statement comes in. The value used in the ELSE statement is what is returned if no match is found.

However, this is an optional part of the SQL CASE statement. If there is no result, and there is no ELSE statement, then the value of NULL is returned.

Does The CASE Statement Search All Conditions Or Just Finds The First Match?

A common question on SQL CASE statements is if the database evaluates all of the conditions in the CASE statement, or does it stop after finding the first match?

The answer is that it stops after the first match . It finds the first match, or the first expression that is evaluated to be a match, and does not continue with the rest.

It also performs something called “ short-circuit evaluation ” for Simple CASE expressions. This might not be a concern to you, but it’s good to know for performance reasons. The database will evaluate the first condition, then compare it to the expression, then evaluate the second condition, then evaluate that to the expression, and so on. It doesn’t evaluate all conditions before comparing the first one to the expression.

How Many Conditions or Arguments Can a CASE Statement Use?

The maximum number of conditions in a CASE statement is 255 . This includes:

The initial expression in a simple CASE statement
The optional ELSE expression
The expression for the WHEN condition
The expression for the THEN result

You can use nested CASE statements so that the return value is a CASE expression. However, if you’re reaching the limit of 255 expressions, I would be looking at the efficiency of the query itself, as most queries should not need 255 expressions.

Can You Use An SQL CASE Statement In WHERE Clause?

Yes , you can use an SQL CASE in a WHERE clause. The examples below will show how this is done.

Can You Use An SQL CASE within CASE?

Yes , you can use a CASE within CASE in SQL. The examples below will show how this is done.

Can I Use DECODE Instead Of CASE?

Oracle has a function called DECODE , which lets you check an expression and return different values.

It looks like this:

However, CASE is recommended for several reasons:

DECODE is older, and CASE was made as a replacement for DECODE.
CASE offers more flexibility.
CASE is easier to read.

In terms of performance, they are both very similar. Experiments have shown that unless you’re using millions of records, you won’t get much of a difference, and any difference will be small.

MySQL has a DECODE function but it’s used for something completely different. SQL Server and PostgreSQL don’t have a DECODE function.

There Is No IIF or IF in Oracle

SQL Server 2012 introduced a statement called IIF, which allows for an IF statement to be written.

However, Oracle does not have this functionality. It’s SQL Server only.

The CASE statement should let you do whatever you need with your conditions.

In Oracle, there is no “IF” statement or keyword specifically in Oracle. If you want to use IF logic, then use the CASE statement.

Procedural Languages Have an IF Statement

The procedural languages for each database do have an IF statement:

Oracle PL/SQL
SQL Server T-SQL
PostgreSQL PGSQL

This statement works just like other languages. You have IF, ELSE, ELSIF and END.

However, that’s when you’re working with PL/SQL.

If you’re writing functions or stored procedures , you could use this IF statement.

If you’re just using standard SQL in your application or database, then you can use the CASE statement.

Hopefully, that explains how the SQL CASE statement is used and answers any questions you had. If you want to know more, just leave a comment below.

If you want an easy-to-use PDF guide for the main features in different database vendors, get my SQL Cheat Sheets here:

29 thoughts on “SQL CASE Statement Explained with Examples”

Is it possible to use the same CASE statement for both the SELECT clause and the WHERE clause? For example, using the “continent” example above, could you add something along the lines of WHERE CONTINENT = “Europe”?

Hi Gregg, yes you can use a CASE statement in both the SELECT and WHERE clauses if you wanted to.

I know you can use the CASE statement in either. My question is if you can use the SAME CASE statement in both places in the SAME query, with one referencing the other.

This is a nonsensical example, but could you do something like this:?

SELECT first_name, last_name, country, CASE country WHEN ‘USA’ THEN ‘North America’ WHEN ‘Canada’ THEN ‘North America’ WHEN ‘UK’ THEN ‘Europe’ WHEN ‘France’ THEN ‘Europe’ ELSE ‘Unknown’ END Continent FROM customers >>>> WHERE Continent like ‘%America’ <<<< ORDER BY first_name, last_name;

Again, I recognize you wouldn't write this exact query. I'm just looking conceptually at referencing the CASE statement in the SELECT clause somewhere in the WHERE clause, or vice versa.

Ah, I see what you mean. You can’t reference the CASE statement like the example you gave, because it’s referring to a column alias, which can’t be done inside a WHERE clause. If you want to use the CASE statement in the WHERE clause, you’ll need to copy and paste the same CASE statement, instead of use the “continent” name.

Hi sir i am Bujjibabu from india If don’t mind I want Oracle projects sir please provide me for my practical sir

Thank you so much for this post. It is great because It is what I am looking for.

Hi Ben, Your article is the most complete I have found on the internet discussing CASE. I have some difficult code that I have inherited and has terrible performance time. I want to redo the following using CASE. However, as I said, it is difficult. I think the AVG function and the COUNT might make it impossible. I might need to use nested CASEs.(?) Could you please tell me how to do this or where to start. The code is very similar on both sides of the UNION ALL. Thank you very much, Margaret

select d.seq, ‘Topo’ “Layer Type”, “Avg” from (select 1 seq,trunc(avg(count)) “Avg” from (select to_char(dldate,’YYYY-MM’), count(*) count from GRAPHICS_DOWNLOAD g where itcl_id in (select ic.id from item_class_data ic where ic.product_type in (‘Graphics’) and ic.product_theme=’US Topo’) and exists (select ‘x’ from CELL_STATES cs where cs.cell_id=g.cell_id and cs.name like ‘%’||:P835_STATE||’%’) group by to_char(dldate,’YYYY-MM’))) d union all select d.seq, ‘Scan Map’ “Layer Type”, “Avg” from (select 2 seq,trunc(avg(count)) “Avg” from (select to_char(dldate,’YYYY-MM’), count(*) count from GRAPHICS_DOWNLOAD g where itcl_id = 163 and exists (select ‘x’ from CELL_STATES cs where cs.cell_id=g.cell_id and cs.name like ‘%’||:P835_STATE||’%’) group by to_char(dldate,’YYYY-MM’))) d union all select d.seq, ‘Historical’ “Layer Type”, “Avg” from (select 4 seq,trunc(avg(count)) “Avg” from (select to_char(dldate,’YYYY-MM’), count(*) count from GRAPHICS_DOWNLOAD g where itcl_id in (select ic.id from item_class_data ic where ic.product_type in (‘Graphics’) and ic.product_theme=’Hist’) and (exists (select ‘x’ from CELL_STATES cs where cs.cell_id=g.cell_id and cs.name like ‘%’||:P835_STATE||’%’) or :P835_STATE=’%’ or (g.cell_id is null and :P835_STATE in (‘%’,’MP’))) group by to_char(dldate,’YYYY-MM’))) d order by 1

Hi Margaret, Thanks for the comment. I’ve had a look at your query and yes I think it can be improved with CASE. I’ve updated it here. I created some test data and it seemed to work for me with a performance improvement. Does it work for you?

SELECT * FROM ( SELECT (AVG(NULLIF(count_topo, 0))) AS avg_topo, (AVG(NULLIF(count_scan_map, 0))) AS avg_scanmap, (AVG(NULLIF(count_hist, 0))) AS avg_hist FROM ( SELECT dl_month, SUM(count_topo) AS count_topo, SUM(count_scan_map) AS count_scan_map, SUM(count_hist) AS count_hist FROM ( SELECT dl_month, CASE WHEN sub.product_theme = ‘US Topo’ AND sub.itcl_id != 163 THEN 1 ELSE 0 END count_topo, CASE WHEN sub.itcl_id = 163 THEN 1 ELSE 0 END count_scan_map, CASE WHEN sub.product_theme = ‘Hist’ AND sub.itcl_id != 163 THEN 1 ELSE 0 END count_hist FROM ( SELECT TO_CHAR(g.dldate,’YYYY-MM’) AS dl_month, g.itcl_id, g.cell_id, ic.product_theme FROM graphics_download g INNER JOIN item_class_data ic ON g.itcl_id = ic.id WHERE ( ( ic.product_type = ‘Graphics’ AND ic.product_theme IN (‘US Topo’, ‘Hist’) AND g.itcl_id != 163 ) OR ( g.itcl_id = 163 ) ) AND ( EXISTS ( SELECT ‘x’ FROM cell_states cs WHERE cs.cell_id = g.cell_id AND cs.name LIKE ‘%’||:P835_STATE||’%’ ) OR :P835_STATE=’%’ OR (g.cell_id IS NULL AND :P835_STATE IN (‘%’,’MP’)) ) ) sub ) sub2 GROUP BY dl_month ) sub3 ) UNPIVOT (avg_val FOR seq IN (avg_topo AS 1, avg_scanmap AS 2, avg_hist AS 4)) ;

Thanks, Ben

Ben, That is exactly what I needed to know! You made it make sense. You know how sometimes when you think about something your brain starts to go in circles? Well, you opened a way out. You did it all without any UNION’s. And I had never used UNPIVOT. So thanks for that one also. Appreciate your help with this. Margaret

No problem Margaret, it was a good challenge for me! I haven’t used UNPIVOT much before so it was a good example of using it. Glad it helps!

Hi Ben! Your explanations are really helpfull but i still can’t make work this query. It has a case inside another case, but the second case is being ignored and i don’t know why.

SELECT NUMEROLINEA, FECHAACTIVACION AS ALTA, ESTADOPROVISIONAMIENTO AS ESTADO, NOMBRE, APELLIDO, NUMERODOCUMENTO AS DNI, CALLENOMBRE AS CALLE, CIUDADNOMBRE AS CIUDAD, CASE NUMEROTELEFONO WHEN NULL THEN CASE NUMEROMOVIL WHEN NULL THEN NUMEROTELEFONOCASA ELSE NUMEROMOVIL END ELSE NUMEROTELEFONO END AS TELEFONO, PROVINCIA FROM A001470.PRODUCTOADQUIRIDO PA INNER JOIN A001470.CUENTAFACTURACION CF ON PA.IDCUENTAFACTURACION = CF.IDCUENTAFACTURACION INNER JOIN A001470.INDIVIDUOCUENTAFACTURACION ICF ON CF.IDCUENTAFACTURACION = ICF.IDCUENTAFACTURACION INNER JOIN A001470.INDIVIDUO I ON ICF.IDINDIVIDUO = I.IDINDIVIDUO INNER JOIN A001470.INDIVIDUOCUENTACLIENTE ICC ON I.IDINDIVIDUO = ICC.IDINDIVIDUO INNER JOIN A001470.DIRECCION D ON ICC.IDCUENTACLIENTE = D.IDCUENTACLIENTE WHERE NUMEROLINEA = ‘3584309290’

Hi Juan, Thanks for the comment. That’s strange the second CASE is being ignored. Could you test that the values in NUMEROTELEFONO are actually NULL? To do this, you can replace your CASE statement with:

CASE NUMEROTELEFONO WHEN NULL THEN ‘value is null’ ELSE NUMEROTELEFONO END AS TELEFONO

Or, if you’re just testing for NULL values, you could use COALESCE, which returns the first non-NULL expression in the list:

COALESCE(NUMEROTELEFONO, NUMEROMOVIL, NUMEROTELEFONOCASA) AS TELEFONO

You can read more about COALESCE here .

can’t i use case within case like below, it says column does not exsist?

select count(distinct(vid||active_session)), (CASE WHEN (( current_page_url ilike ‘%optus.com.au/shop/broadband%’ OR current_page_url ilike ‘%optus.com.au/shop/home-phone%’ OR current_page_url ilike ‘%optus.com.au/shop/bundles%’ OR current_page_url ilike ‘%optus.com.au/shop/entertainment%’ OR current_page_url ilike ‘%optus.com.au/shop/deals-bundles%’ OR current_page_url ilike ‘%optus.com.au/for-you/entertainment%’ OR current_page_url ilike ‘%optus.com.au/business/broadband%’ OR current_page_url ilike ‘%addBundleToCart%’) AND current_page_url not ilike ‘%prepaid/checkout%’) THEN (CASE WHEN current_page_url ‘%optus.com.au/shop/broadband/nbn%’ THEN ‘Fixed_NBN’ WHEN current_page_url ‘%optus.com.au/shop/broadband/mobile-broadband%’ THEN ‘Fixed_MBB’ ELSE ‘Fixed_Others’ END) END) as prod, purchase_flag from idm.OPTUS_JOINED_VIEW_V_3_6 where dt between ‘2018-06-15’ and ‘2018-07-17’ group by prod,purchase_flag order by prod

Hi Abhi, If that’s the message you’re getting, which column does it say does not exist? Also, the keyword “ilike” should be “like” to use wildcard searches. Ben

I think I am having the same issue… the column that can’t be see is “prod”… so the question is, if I capture the results of a case statement using “as”, how do I use it in with the “group by” so the count is summarized by the results of the case… ? (in the example above, the case results are captured “as prod” )

Hi Sue, If you want to use the alias (the “AS prod” part) in the GROUP BY, you can’t do this in the same query. This is because the aliases are assigned in the SELECT clause which is processed after the WHERE clause. There is a way to do this though. You can use the SELECT with the CASE and all its clauses as a subquery, then in the outer query use the GROUP BY. A simple example: SELECT columns, prod FROM ( SELECT columms, CASE your_case_criteria AS prod FROM table WHERE criteria ) GROUP BY prod;

The GROUP BY is outside the subquery so it should work.

Very Informative. Helped me tremendously.

hi Ben my question is if you want to put even and odd value in different column then how can i write the query

You can probably write two CASE statements to display it: SELECT CASE WHEN MOD(yourcolumn, 2)=0 THEN yourcolumn ELSE null END AS evenvalue, CASE WHEN MOD(yourcolumn, 2)=1 THEN yourcolumn ELSE null END AS oddvalue FROM yourtable;

This will show even values in one column, odd values in another. I haven’t tested this query so you might need to tweak it if you get a syntax error.

Hello! Thank you very much for your effort on this topic.

I don’ t understand one thing: sometimes (and which are the conditions to be so?)CASE exits when first value/expresion is TRUE, and sometimes it goes through all values/expresions?! Can you please clarify what determines that? Is thatconnected with comparisson signs (=, ) or with CASE expresions types( SIMPLEvs.SEARCHED )?

Hi Miro, Good question. The CASE statement should exit when it reaches the first TRUE condition. This happens for both Simple and Searched expressions. The only time I can think of where the CASE statement runs through each condition is if the first condition is false. It’s like a series of IF ELSE. Only one condition can be true. Hope that answers your question! Thanks, Ben

Hi, if I change your Simple CASE Statement example from above as followed:

SELECT first_name, last_name, country, CASE country WHEN ‘USA’ THEN ‘1’ WHEN ‘Canada’ THEN ‘2’ WHEN ‘UK’ THEN ‘3’ WHEN ‘France’ THEN ‘Europe’ ELSE ‘Unknown’ END Continent FROM customers ORDER BY first_name, last_name;

then the so called the column alias Continent is truncated to Con. Is there a possibility to format the column alias? Thank you.

Hi Claudia, are you running this on SQL*Plus? SQL*Plus and some IDEs may truncate the column heading to be the widest value. Which IDE are you using?

I have a nested Case statement within a where clause with multiple “whens” that errors on the first case.

INSERT INTO [PERMIL_STATUSES] (PERMIL_STATUSES.POS, PER_MILITARY_ID, PERMIL_MIL_STATUS, PERMIL_BRANCH, PERMIL_STATUS_START_DATE, PERMIL_STATUS_END_DATE,PERMIL_PRIMARY_FLAG) SELECT MILITARY_ASSOC.POS, MILITARY_ASSOC.ID, MILITARY_STATUSES, MILITARY_BRANCHES, MILITARY_START_DATES, MILITARY_END_DATES FROM MILITARY_ASSOC JOIN STPR_STATUSES ON SUBSTRING(STPR_STATUSES.STUDENT_PROGRAMS_ID, 1, 7) = (MILITARY_ASSOC.ID) WHERE STPR_STATUSES.POS=1 AND STPR_STATUS=’A’ AND NOT EXISTS (SELECT * (CASE – error (incorrect syntax near ‘CASE’, expecting ‘(‘ or SELECT) WHEN MILITARY_STATUSES = ‘AAIR’,’AANG’,’AARMY’,’ACG’,’AMAR’,’ANAVY’,’ANG’ THEN ‘ACT’ WHEN MILITARY_STATUSES = ‘DODAF’, ‘DODAG’,’DODAR’,’DODCG’,’DODMA’,’DODNA’,’DODNG’ THEN ‘DOD’ WHEN MILITARY_STATUSES = ‘FAMAF’,’FAMAG’,’FAMAR’,’FAMCG’,’FAMMA’,’FAMNA’,’FAMNG’ THEN ‘DEP’ WHEN MILITARY_STATUSES = ‘RAIR’,’RARMY’,’RCG’,’RMAR’,’RNAVY’,’RNG’ THEN ‘RES’ WHEN MILITARY_STATUSES = ‘VAIR’,’VANG’,’VARM’,’VCG’,’VMAR’,’VNAVY’,’VNG’ THEN ‘HON’ ELSE NULL END) PERMIL_MIL_STATUS THEN (CASE WHEN MILITARY_STATUSES (‘AAIR’,’DODAF’,’FAMAF’,’RAIR’,’VAIR’) THEN ‘AF’ WHEN MILITARY_STATUSES (‘AANG’,’DODAG’,’FAMAG’,’VANG’) THEN ‘ANG’ WHEN MILITARY_STATUSES (‘AARMY’,’DODAR’,’FAMAR’,’RARMY’,’VARM’) THEN ‘ARMY’ WHEN MILITARY_STATUSES (‘ACG’,’DODCG’,’FAMCG’,’RCG’,’VCG’) THEN ‘CG’ WHEN MILITARY_STATUSES (‘AMAR’,’DODMA’,’FAMMA’,’RMAR’,’VMAR’) THEN ‘M’ WHEN MILITARY_STATUSES (‘ANAVY’,’DODNA’,’FAMNA’,’RNAVY’,’VNAVY’) THEN ‘NAVY’ WHEN MILITARY_STATUSES (‘ANG’,’DODNG’,’FAMNG’,’RNG’ ,’VNG’) THEN ‘NG’ ELSE NULL END) PERMIL_BRANCH FROM PERMIL_STATUSES WHERE PER_MILITARY_ID=MILITARY_ASSOC.ID AND PERMIL_STATUSES.POS=1 AND PERMIL_PRIMARY_FLAG=’YES’);

Hi Deborah, I think this is because of the * character before the case. Select * means select all columns, but then you have a CASE statement. Are you looking to select all columns from permil_statuses, as well as the result of the CASE statements? If so, it should be SELECT *, (CASE WHEN… Add the comma after *. If you don’t want all columns and only want the results of the two CASE statements, then you can remove the *. SELECT (CASE WHEN … Hope that helps!

I need to use case statement like below written ,Can someone help me in this ?

from txn_logs tl, t_sm_service_master sm, t_wm_wallet_info wi, t_um_entity_detail ued WHERE tl.service_id = sm.service_txn_id and t1.entity_id = ued.entity_id And tl.entity_id = wi.entity_id and wi.wallet_type = 1 and Case when ued.user_type in (85,73,74) then t2.amt_type in (‘TXN_AMT’,’COMM’) else t2.amt_type =’TXN_AMT’ end case

Write a query to display EMPLOYEES having ID 101,102,103 as per the below order: 1. 101, 2. 103, 3. 102 (Hint: Union Operator / Case Statement)

i have employee table with column id, name, dept, salary so i want sal which has greater than avg(sal) ,if sal >avg(sal) then give flag ‘Y’ other wise ‘N’?

COLUMN1 COLUMN2 COLUMN3 30 30 30 10 20 10 20 30 10 15 16 21 34 89 100 121 23 11

q1>if value of all three col is same for a particular row then show excellent, if value of

two is same then very good, if value of all three column are different then good

–>There are thousand of record in this table

CASE Statement and CASE Expression Enhancements in Oracle Database 23ai

In Oracle database 23ai the simple CASE statement and expression are more flexible, allowing dangling predicates and multiple choices in a single WHEN clause.

This brings the PL/SQL simple CASE statement and expression in line with the SQL:2003 Standard [ISO03a, ISO03b] standard.

The Problem

Dangling predicates, multiple choices in a single when clause, sql case expressions.

The examples in this article use the following table.

In previous releases simple CASE statements and expressions were only capable of performing equality checks. If we needed comparisons other than equality checks we would have to use a searched case statement or expression.

In the following example we use a searched CASE statement to evaluate a threshold. We are using a searched CASE statement because most of the comparisons are not simple equality checks.

This example uses a searched CASE expression to do the same thing.

In Oracle 23ai we can do the same thing using a simple CASE statement or expression using dangling predicates. A dangling predicate is an expression with its left operand missing.

This example uses a simple CASE statement with dangling predicates to achieve the same result.

Here is the simple CASE expression equivalent.

A single WHEN clause can include multiple equality checks or dangling predicates as a comma-separated list.

In the following example we use a simple CASE statement to show an error if a value is below 0, exactly 0.5 or greater than 100. We also add values 41 and 42 to the optimal threshold.

This is the simple CASE expression equivalent of the previous example.

This functionality is not supported directly by SQL CASE expressions.

We can achieve a similar result by defining a function in the WITH clause to perform the CASE expression.

For more information see:

CASE Statement
CASE Expressions
CASE Expressions And Statements in Oracle

Hope this helps. Regards Tim...

Back to the Top.

Created: 2023-04-14 Updated: 2024-05-09

About Tim Hall Copyright & Disclaimer

Database.Guide

Oracle case statement.

In Oracle Database, the CASE statement compares a list of conditions and returns one of multiple possible expressions.

Oracle Database’s CASE statement is very similar to the CASE expression (which is defined in the SQL standard (ISO/IEC 9075)). However, Oracle supports both the CASE expression and the CASE statement, and there’s a distinction between the two. The CASE statement can be used to execute of a sequence of PL/SQL statements, whereas the CASE expression returns a single value. Also, there’s a difference in how they deal with the lack of an ELSE clause when a condition is not met.

The CASE statement can take two forms. The first form is known as the simple CASE statement :

The other form is the searched CASE statement :

The searched CASE statement evaluates a set of Boolean expressions to determine the result. When using this form, no case_operand is supplied.

Example – Simple CASE Statement

Here’s an example to demonstrate the simple CASE statement:

If we change the value of the variable, we get a different result:

When the value doesn’t match a condition, the value at ELSE is used:

Example – Searched CASE Statement

We could rewrite the above example to a searched CASE statement. Doing this means we would remove the base expression, but then include it in the WHEN part of the statement:

We get the same result when we pass the same value. However, there’s a difference in the processing. When we use the simple CASE statement, cuisine is only evaluated once. But when we use the searched CASE statement, cuisine is evaluated multiple times.

Omitting the ELSE Clause

If we omit the ELSE clause, and none of the conditions are met, the result is a CASE_NOT_FOUND error:

But we don’t get the error if the case is found:

Note that the CASE expression (as opposed to the CASE statement being discussed here) will return NULL when there’s no ELSE clause and no condition is met.

Interactive Report
Interactive Grid
Faceted Search
Classic Report
Drill Down Interactive Report
Drill Down Interactive Grid
Report Highlighting
Interactive Report Query API
Linking to Interactive Reports
Linking to Interactive Grids
Bind Variables
Non-Tabular Templates
Column Format Masks
Custom Buttons
Reporting from Collections
CASE Statements
Regular Expressions
String Functions
Inline Views
Pipelined Functions
LEAD and LAG
RANK and DENSE_RANK
RATIO_TO_REPORT
Top N Queries
Administration

The Oracle CASE statement is used to perform IF-THEN-ELSE logic in queries. It evaluates a single expression and compares it against several potential values, or evaluates multiple Boolean expressions and chooses the first one that is TRUE. The interactive report below contains 2 CASE statements. The first include an edit link only for projects that are not Closed. Interactive Reports contain the ability to provide an edit link but when used, the link displays for all the records. To conditionally display, you need to use a method like this. To include a link, which contains html, you must change the column display type to 'Standard Report Columns'. It is also advisable to remove all the IR options so that the column cannot be hidden, filtered on, used in a an aggregate, etc. as those functions are not appropriate for a link. Note that when a link is not provided, ' ' is returned so that the "Show Null Values as" value, set in the Report Attributes, will not display.

The second CASE statement provide the Current Estimate for each project. If the project is On-Hold, the estimate is 0. If the project is Closed, the COST is used (instead of the budgeted value). For all other project statuses, the budgeted value is used. The report then contains a sum on Budget, Cost and Current Estimate (which display after the last record) using the interactive report aggregate function.

Column Actions

Judge indefinitely delays Trump's classified documents criminal trial

U.S. District Judge Aileen Cannon has indefinitely postponed former President Donald Trump ’s classified documents trial in Florida pending the resolution of multiple pretrial issues.

The trial had been scheduled to start May 20.

“The Court ... determines that finalization of a trial date at this juncture—before resolution of the myriad and interconnected pre-trial and CIPA issues remaining and forthcoming—would be imprudent and inconsistent with the Court’s duty to fully and fairly consider the various pending pre-trial motions before the Court, critical CIPA issues, and additional pretrial and trial preparations necessary to present this case to a jury,” Cannon wrote Tuesday. “CIPA” is a reference to the Classified Information Procedures Act.

“The Court therefore vacates the current May 20, 2024, trial date (and associated calendar call), to be reset by separate order following resolution of the matters before the Court, consistent with Defendants’ right to due process and the public’s interest in the fair and efficient administration of justice,” she added.

In June 2023, Cannon had scheduled the trial to begin in August of that year , but even that timeline was seen as susceptible to delays.

Cannon on Tuesday also scheduled additional hearings on some of the pending issues, with the first one this month and the last one in late July. That schedule would mean the case is unlikely to go to trial before August. Trump has argued that going to trial in the weeks before the November election would amount to political interference because he should otherwise be campaigning.

Trump is waiting on a Supreme Court decision about his claim of presidential immunity in a separate federal case in Washington, D.C., where he is accused of crimes related to the effort to overturn the 2020 election.

He has argued that any decision in that case could influence his other criminal cases, including the ongoing hush money trial in New York.

Former President Donald Trump appears in court in Fort Pierce, Fla., on March 14, 2024 for a hearing in his classified documents case.

Trump fac es dozens of felony charges , including willful retention of national defense information, false statements and representations, conspiracy to obstruct justice, withholding a document or record and corruptly concealing a document. He has denied any wrongdoing.

Trump’s co-defendants Walt Nauta and Carlos De Oliveira have also pleaded not guilty to the related charges against them. Special counsel Jack Smith has accused Nauta, who was Trump’s valet and continued to work for him after he left the White House, and De Oliveira, a property manager at Trump’s Mar-a-Lago estate, of seeking to erase security video at Mar-a-Lago after the Justice Department sought to obtain it. De Oliveira is also accused of making false statements to prosecutors.

Daniel Barnes reports for NBC News, based in Washington.

Ginger Gibson is the senior Washington editor for NBC News Digital.

Election 2024
Entertainment
Newsletters
Photography
Personal Finance
AP Investigations
AP Buyline Personal Finance
AP Buyline Shopping
Press Releases
Israel-Hamas War
Russia-Ukraine War
Global elections
Asia Pacific
Latin America
Middle East
Election Results
Delegate Tracker
AP & Elections
Auto Racing
2024 Paris Olympic Games
Movie reviews
Book reviews
Personal finance
Financial Markets
Business Highlights
Financial wellness
Artificial Intelligence
Social Media

Supreme Court sides with music producer in copyright case over sample in Flo Rida hit

FILE - Supreme Court is seen on Capitol Hill in Washington, Thursday, April 25, 2024. (AP Photo/J. Scott Applewhite)

FILE - The Supreme Court of the United States is seen in Washington, March 26, 2024. (AP Photo/Amanda Andrade-Rhoades, File)

Copy Link copied

WASHINGTON (AP) — The Supreme Court sided with a music producer in a copyright case Thursday, allowing him to seek more than a decade’s worth of damages over a sample used in a hit Flo Rida song.

The 6-3 decision came in a case filed by Sherman Nealy, who was suing over music used in the 2008 song “In the Ayer,” by the rapper Flo Rida. It also was featured on TV shows like “So You Think You Can Dance.”

Nealy’s suit says he didn’t know his former collaborator had inked a deal with a record company while he was in prison that allowed the sampling of the song “Jam the Box.” He sued in 2018 for damages going back to the song’s release.

Copyright law says suits must be filed within three years of the violation, or the point when it’s discovered. The record company, Warner Chappell, argued that means Nealy would only be entitled to three years’ worth of royalties at most.

The question of how far back damages can go has split appeals courts, and it’s one that industry groups like the Recording Industry Association of America called on the Supreme Court to decide.

The opinion handed down Thursday was written by Justice Elena Kagan, and joined by her liberal colleagues Sonia Sotomayor and Ketanji Brown Jackson as well as conservative justices John Roberts, Brett Kavanaugh and Amy Coney Barrett.

Deputy federal spokesperson for Alternative for Germany party (AfD) Peter Boehringer, left, and assessor on the AfD's federal executive board Roman Reusch stand in the courtroom before the verdict in the appeal proceedings in the dispute over the classification of the AfD by the Federal Office for the Protection of the Constitution at the Higher Administrative Court for the state of North Rhine-Westphalia, in Muenster, Germany, Monday May 13, 2024. The administrative court in Muenster ruled in favor of the BfW intelligence agency on Monday, upholding a 2022 decision by a lower court in Cologne. Alternative for Germany, or AfD, has rejected the designation strongly. The party could still seek to appeal the verdict at a federal court. (Guido Kirchner/dpa/dpa via AP)

“There is no time limit on monetary recovery. So a copyright owner possessing a timely claim is entitled to damages for infringement, no matter when the infringement occurred,” Kagan wrote.

An attorney for Nealy, Wes Earnhardt, said the opinion gives clarity on an important issue.

Three conservative justices dissented. Justice Neil Gorsuch wrote that the majority sidestepped the important question: Whether Nealy’s claim was valid to begin with, or whether copyright holders should have to show some kind of fraud in order to sue over older violations. The dissenters said the suit should have been dismissed.

Database PL/SQL Language Reference
PL/SQL Language Elements

CASE Statement

The CASE statement chooses from a sequence of conditions and runs a corresponding statement.

The simple CASE statement evaluates a single expression and compares it to several potential values or expressions.

The searched CASE statement evaluates multiple Boolean expressions and chooses the first one whose value is TRUE .

SQL Bulk Insert Command Examples

I want my team of SQL Server professionals to improve the efficiency of their file import projects through the application of the bulk insert statement. Please present multiple use case examples for the bulk insert statement so I can motivate them to apply it more regularly to the file import projects we are required to run.

The T-SQL bulk insert statement is specifically designed to input the contents of large files into SQL Server tables. However, bulk insert statements can be readily adapted for importing both small files as well as large files and/or many medium-sized files. If you enjoy programming in T-SQL or conclude that SSIS is overkill for some of your file import projects, bulk insert statements may offer the right level of support and give you a performance benefit.

This tip presents three progressively more advanced bulk insert use case examples. The download includes sample data for each use case example and additional data samples to practice with fresh data.

Use Case #1: Illustrates the basics of invoking a bulk insert statement for importing a TXT file and parsing selected elements of the imported file into a SQL Server results set.
Use Case #2: Focuses on how to write T-SQL code to import each of three files into a single SQL Server table. Each file uses its filename to indicate the category for the type of data in a file.
Use Case #3: Demonstrates how to make bulk insert statements dynamic through the SQL Server replace function and a stored procedure to import a set of files designated by their names into a SQL Server table.

Use Case #1: Importing a TXT File into a SQL Server Results Set

The following screenshot displays the contents of an arbitrary TXT file created to demonstrate the first use case example. The file appears in a Notepad++ session. The header for the session indicates the file resides on the C drive in the My Text Files for SQL Server folder. The filename is Text_Document_1.txt. The filename and path to the file are important because they are required by the FROM clause of a bulk insert statement.

The file contents appear in 10 lines within the Notepad++ session. The CR and LF at the end of the first nine lines are row terminators. These characters correspond to default row terminators for the bulk insert statement. The last line in a file does not require a row terminator.

Notice that the data inside the Notepad++ tab for the Text_Document_1.txt file contains free-form text – that is, the data are not organized or delimited into rows and columns. The data are for three persons named Rick Dobson, Louise Dobson, and Barbara Dobson. The data for each person appears on two successive lines. After the second line for each person, a third line indicates the end of data for a person. Another line type is the last line in the TXT file. This line marks the end of the file.

Importing a TXT File into a SQL Server Results Set

The following script illustrates the T-SQL code for the first use case.

The two statements in the comment block at the top of the script create a fresh copy of the for_char_data_lines database.
The next several lines after the comment block designate the for_char_data_lines database as the default database for the rest of the script and create a fresh copy of the char_data_lines table in the dbo schema of the database.
The bulk insert statement after the create table statement for the char_data_lines table pushes the lines from the Text_Document_1.txt file to the char_data_lines table.
The code after the bulk insert statement parses three sets of values from the rows in the char_data_lines table. A version of this kind of parsing code will typically appear in scripts that import free-form text data.

A good approach to learning about the role and operation of the parsing code is to view the results set from the select statement after the bulk insert statement. The following excerpt is from the Results tab in SQL Server Management Studio after running the preceding script.

There are only six rows in the results set from the script. These rows correspond to the two lines for each person in the TXT file referenced by the from clause within the bulk insert statement. The transformation of the line values in the char_data_lines table rows is implemented with four SQL Server string functions (substring, charindex, len, and left).
The where clause at the end of the select statement excludes lines marking the end of data for each person and the overall source file referenced by the bulk insert statement.
The number following the first instance of the word field on a line.
The number following the second instance of the word field on a line.
The full name of the person on each of the two lines for each person.

Use Case #2: Import and Categorize Three CSV Files into a SQL Server Table

The second case study example imports three CSV files with bulk insert statements into a SQL Server table. All three files have the same layout for their content. The file rows are unique by stock ticker and date. The columns for each row contain different kinds of prices as well as the number of shares traded during a day. This general kind of data layout is common in many business applications, such as tracking salaries, commissions, and bonuses earned by salespersons from different offices or the number and dollar amount of orders input through various pages on a website.

The following table shows an excerpt from each of the three files. The filenames are MARA.csv, MSTR.csv, and COIN.csv. The files reside in the same Windows drive and folder (C:\My Text Files for SQL Server) as the preceding use case example.

The top table cell shows the first eight rows from MARA.csv. This excerpt displays the column headers (Date, …, Volume) followed by date and numeric values.
The middle table cell shows the first eight rows from MSTR.csv. As you can see, the layout of its data is the same as the MARA.csv file. Also, the date values are the same in the top and middle table cells (starting at 2024-01-02 and ending at 2024-01-10).
The bottom table cell shows the last eight rows from COIN.csv. The data layout for this table cell matches the preceding two table cells, except that it does not show column headers. Column headers may optionally be included in CSV files. In this use case example, all three CSV files have column headers in their first file row. Also, all three CSV files have the same ending date (2024-03-28), which appears as the first field value in the bottom row of the bottom table cell.

Import and Categorize Three CSV Files into a SQL Server Table

The following script shows the T-SQL code for importing the three CSV files into a SQL Server table. There are four segments to the script that are separated from one another by a row of single-line comment markers.

SQL Server Table 1: Stores the original contents of each of the three CSV files in the dbo.imported_data_for_a_category table. This table is freshly populated for each of the three CSV files. The columns of this table correspond to the table excerpts that the preceding table displays.
SQL Server Table 2 (dbo . imported_data_across_categories): Stores data from all three CSV files. The first column in the table has the name symbol. This column stores the ticker symbol for each CSV file. In other words, the symbol column categorizes the rows from each CSV file.
Format is for the type of character data being imported – namely, character data from a CSV file,
firstrow designates the beginning data row in the source file,
fieldterminator designates the delimiter character (,) for field values,
rowterminator specifies the character (0x0a) to mark the end of a row; another commonly used rowterminator value is (\n\r)
Step 2: Copies (using an insert into bulk statement) the contents of the dbo.imported_data_for_a_category table to the dbo.imported_data_across_categories table. This step also populates the symbol column of the dbo.imported_data_across_categories table with mara, which is the name of the source CSV file for the segment.
Step 3: Echoes the contents of the dbo.imported_data_across_categories table into the Results tab within SQL Server Management Studio.
Both of these segments begin with a truncate table statement for the dbo.imported_data_for_a_category table. The truncate table statement is required to clear any prior data from the table before running the bulk insert statement to copy fresh data from another CSV file. The second segment does not require a truncate table statement because the dbo.imported_data_for_a_category table was not previously populated.
In the third segment, the string value assigned to the symbol column is mstr.
In the fourth segment, the string value assigned to the symbol column is coin.

The following screenshot shows three excerpts that are, respectively, from the last select statements at the end of the second, third, and fourth segments in the preceding script.

Top Excerpt: The first eight data rows for the mara symbol.
Middle Excerpt: The first eight data rows for the mstr symbol.
Bottom Excerpt: The bottom eight data rows for the coin symbol.

Use Case #3: Using Dynamic SQL and a Stored Procedure to Import CSV Files

The script in the preceding section illustrates how to import multiple CSV files into a SQL Server table with the help of the bulk insert statement. Unfortunately, the bulk insert statement requires a literal value for each source file to be imported. If you only have a few files to import, it will not be a significant issue to update a few filenames. However, as the number of new files to import grows, these manual updates can introduce errors in the script for importing the files.

This section describes a new script to remedy this issue involving dynamic SQL embedded in a stored procedure. The stored procedure accepts an input parameter with the name of the file to be imported. The T-SQL inside the stored procedure replaces a portion of the SQL that invokes the bulk insert statement and populates a target table with values from a source file. Therefore, you can easily run the solution in this section by specifying the stored procedure’s input parameter for each source filename.

There are three segments to the script that are separated by a row of single-line comment markers.

The first segment creates two tables—one for accepting a single CSV file and a second for saving the imported contents, with a new column for each CSV filename.
The second segment creates a stored procedure with a bulk insert statement specified as a SQL string. This segment also updates the SQL string with the name of the file to be imported and then populates the first table for accepting file contents. Next, the stored procedure code populates the second table containing the filename with its imported contents. The final operation in the stored procedure truncates the first table so that an empty table is available for any re-run of the stored procedure with a new CSV filename.
Lines 1-3: Invoke the stored procedure for each of the three CSV file names.
Line 4: Displays the contents of the second table with values from all source files, which are MARA.csv, MSTR.csv, and COIN.csv, in the following code sample.

Here is the script described above. Notice that the first and second segments operate only once. After each segment runs once, you can re-run the stored procedure for as many files as you must import. Each file imported to SQL Server must reside in the My Text Files for SQL Server folder of the C drive.

Since the instructions for running the code in this section are for the same files as the preceding section, you will get the same Results tab output at the end of the prior section.

This tip introduces the basics of using the bulk insert statement with three different use case examples. I regularly use the CSV file examples in this tip to download financial data from the Yahoo Finance site to SQL Server tables. This code can also be adapted for many other kinds of data, such as human resources data about company employees or orders coming from user responses to a web page.

Download the files for this article.

There are at least three next steps.

Run the use case examples as described in this tip. You can scrape the code for the use case examples. You can find the source data files for use case examples in the download for this tip.
The download includes two additional CSV files (spy.csv and qqq.csv) not referenced in this tip. You can run the third use case example with these additional files to verify your ability to adapt and run the code with fresh data.
Lastly, you can adapt any use case examples with appropriate data from your business.

About the author

Comments For This Article

Stormy Daniels Delivers Intense Testimony in Trump’s Trial: 6 Takeaways

A long day on the stand put Ms. Daniels’s credibility to the test as defense lawyers challenged her motives.

Share full article

Stormy Daniels walking as her hair blows in the wind.

By Kate Christobek and Jesse McKinley

Published May 7, 2024 Updated May 10, 2024

Follow our live coverage of Trump’s hush money trial in Manhattan.

“The people call Stormy Daniels.”

So began the intense and often uncomfortable testimony of Ms. Daniels, who spent almost five hours in a Manhattan courtroom on Tuesday recounting her story of a 2006 encounter with Donald J. Trump and the ensuing hush-money cover-up that has become the bedrock of the prosecution’s case.

Ms. Daniels spoke quickly and at length about her first meeting with Mr. Trump at a celebrity golf tournament near Lake Tahoe in Nevada.

After the lunch break, Mr. Trump’s lawyer Todd Blanche moved for a mistrial, arguing that the prosecution’s questions had been designed to embarrass Mr. Trump and prejudice the jury.

The judge, Justice Juan M. Merchan of State Supreme Court, agreed that some of Ms. Daniels’s testimony might have “been better left unsaid,” but he denied a mistrial.

The former president is accused of falsifying business records to cover up a $130,000 payment to Ms. Daniels just before the 2016 election. Mr. Trump, 77, has denied the charges and says he did not have sex with Ms. Daniels. If convicted, he could face prison time or probation.

Here are six takeaways from Mr. Trump’s 13th day on trial.

Prosecutors took a risk with their witness.

Jurors heard a vivid account of the Lake Tahoe encounter and met the woman who had received the hush-money payment. This could have presented a risk for prosecutors, depending on whether the jury viewed Ms. Daniels’s story as prurient or powerful.

Ms. Daniels described meeting Mr. Trump at the golf event and accepting his dinner invitation after her publicist said, “What could possibly go wrong?”

She recalled that Mr. Trump had been wearing pajamas when she met him at his hotel suite and that she asked him to change. They discussed the porn industry, and he asked about residuals, unions and testing for sexually transmitted diseases, she said.

She said they had talked about his family, including his daughter, whom he likened to Ms. Daniels — “People underestimate her as well,” Ms. Daniels recalled him as saying. They also discussed his wife; Mr. Trump said they did not “even sleep in the same room.” He suggested that Ms. Daniels might appear on “The Apprentice.”

When she later emerged from the bathroom, Ms. Daniels found Mr. Trump partially undressed, she said. The sex was consensual, she said, but there was a power “imbalance.”

The Links Between Trump and 3 Hush-Money Deals

Here’s how key figures involved in making hush-money payoffs on behalf of Donald J. Trump are connected.

Trump received another warning.

Justice Merchan has already held the former president in contempt 10 times, fined him $10,000 and twice threatened to send him to jail. On Tuesday, Mr. Trump again drew the judge’s ire after Justice Merchan said he had been “cursing audibly” and “shaking his head.”

The judge asked Mr. Trump’s lawyers privately to talk to their client, saying Mr. Trump’s actions might intimidate the witness, Ms. Daniels.

“You need to speak to him,” the judge said. “I won’t tolerate that.”

The motive for the payoff is a point of contention.

Prosecutors asked Ms. Daniels about a 2018 statement in which she denied the sexual encounter. Ms. Daniels said she had not wanted to sign it and that it was not true.

Defense lawyers, capitalizing on what they seem to perceive as Ms. Daniels’s shortcomings as a witness, came out blazing. One of them, Susan Necheles, implied in her cross-examination that Ms. Daniels was trying to “extort money” from Mr. Trump.

Ms. Daniels replied sharply, “False.”

Daniels’s story bothers Trump.

Before court even started Tuesday morning, Trump telegraphed his frustration with Ms. Daniels in an angry post on Truth Social, saying he had just learned about a coming witness and that his lawyers had “no time” to prepare. The post was removed shortly thereafter, possibly because of concerns over violating the gag order.

Mr. Trump, who has spent much of the trial with his eyes closed, remained attentive for part of the day, often with a sour expression on his face. He continually whispered to his lawyers and at one point mouthed an expletive.

But by the afternoon, he had returned to his habit of closing his eyes, even during a combative cross-examination.

Daniels’s credibility is a hurdle for prosecutors.

Ms. Daniels’s motivations are a major focus of the defense. In a sharp moment, Ms. Necheles confronted her about what Ms. Necheles described as her hatred of the former president and asked whether she wanted him to go to jail. Ms. Daniels responded, “I want him to be held accountable.”

Ms. Necheles also asked Ms. Daniels about making money by claiming to have had sex with Mr. Trump. Ms. Daniels responded, “I have been making money by telling my story,” and later added, “It has also cost me a lot of money.”

Who Are Key Players in the Trump Manhattan Criminal Trial?

The first criminal trial of former President Donald J. Trump is underway. Take a closer look at central figures related to the case.

Trump’s words haunt him.

Prosecutors have tried several times to use Mr. Trump’s prior statements against him.

Before Ms. Daniels testified Tuesday, a witness read aloud passages from books by Mr. Trump. Some spoke to his frugality. Others spoke to his penchant for revenge.

“For many years I’ve said that if someone screws you, screw them back,” the witness read. The passage continued, “When somebody hurts you, just go after them as viciously and as violently as you can.”

Not long after, one of his enemies — Ms. Daniels — took the stand. Her cross-examination resumes on Thursday.

Kate Christobek is a reporter covering the civil and criminal cases against former president Donald J. Trump for The Times. More about Kate Christobek

Jesse McKinley is a Times reporter covering upstate New York, courts and politics. More about Jesse McKinley

Our Coverage of the Trump Hush-Money Trial

News and Analysis

Michael Cohen was paid to fix Donald Trump’s problems. Now, as he prepares to take the stand in Trump’s criminal trial, he’s one of them .

Ahead of Cohen’s testimony, Justice Juan Merchan told prosecutors to keep Cohen from speaking about the case .

Witnesses have described Trump monitoring the minutiae of his business , a portrait prosecutors are drawing to help convince the jury that he couldn’t have helped but oversee the hush-money payment.

SEC’s Shadow Trading Win Opens Door for Department of Justice

A first-of-its-kind enforcement action from the Securities and Exchange Commission may pique the interest of federal prosecutors, who have been willing to pursue novel securities fraud theories that were first tested successfully by the agency.

The SEC convinced a California jury last month that a former executive at biopharmaceutical company Medivation Inc. committed insider trading by buying options in a comparable company, Incyte Corp., within minutes of receiving material nonpublic information, or MNPI, about Medivation’s imminent acquisition.

The Department of Justice has followed in the SEC’s footsteps before. For example, the DOJ last year filed criminal charges against the CEO of a publicly traded health-care company based on his use of a Rule 10b5-1 trading plan while allegedly in possession of MNPI. There, the DOJ pursued its case on the heels of a settled SEC enforcement action in 2022 charging two technology company executives under a similar theory.

Still, the DOJ likely recognizes that pursuing a shadow trading case criminally would present its own distinct challenges, such as addressing burden of proof and showing criminal intent.

Observers have called this fact pattern “shadow trading.” While a logical extension of insider trading law, shadow trading differs from typical insider trading where one trades securities of the same company about which one has MNPI.

The SEC’s victory in this case may encourage similar enforcement cases and prompt the DOJ to prosecute shadow trading as well. For market participants whose personnel regularly encounter MNPI, this development may well warrant updates to compliance programs, employee training, and/or company policies.

The SEC prevailed against Matthew Panuwat using the misappropriation theory of liability, under which a trader “misappropriates” MNPI “in breach of a duty owed to the source of the information.” Although Panuwat likely will appeal , the agency has demonstrated that shadow trading can resonate with a jury, and it earlier convinced the judge that this theory is sound.

In any criminal case, the government must prove each element beyond a reasonable doubt. By contrast, civil cases such as Securities and Exchange Commission v. Panuwat require proof by a preponderance of the evidence.

Proving criminal intent is the most important challenge the DOJ would face in a shadow insider trading case. To bring an insider trading case criminally, the DOJ must prove that the trader acted willfully , meaning with “ knowledge that his conduct was unlawful.”

By contrast, the SEC is permitted to rely on a “recklessness” standard —that the trader acted “highly unreasonabl[y]” in “an extreme departure from ordinary care, which is either known to the [trader] or is so obvious that [trader] must have been aware of it.”

While the SEC can satisfy this standard by showing the defendant should have known information about one company was material to another, the DOJ would need to prove beyond a reasonable doubt that information about one company was in fact material to another, that the defendant knew it was material to the company traded, and that the defendant intended to use inside information in a way that was prohibited.

Given the SEC’s victory in Panuwat , companies should ensure that their compliance and training programs appropriately address the new enforcement risk that this novel enforcement theory presents. Companies should caution employees through training programs about their obligation not to use MNPI they learn at work to trade in any comparable company’s stock. Traders should note the broadened range of nonpublic information that might be deemed material to their trading.

Finally, the scope of the duty at issue—which companies’ stock a party can’t trade—is often determined by a private confidentiality or nondisclosure agreement.

Asset managers and others who enter into such agreements should be mindful when drafting those agreements not to limit needlessly their trading in companies beyond the parties to the agreement. More broadly, companies should ensure that they are being deliberate as to what their own internal policies prohibit, so that the duty of employees not to trade doesn’t extend more broadly than what serves the company’s own interests.

The case is Securities and Exchange Commission v. Panuwat , N.D. Cal., No. 21-cv-06322, verdict 4/5/24.

This article does not necessarily reflect the opinion of Bloomberg Industry Group, Inc., the publisher of Bloomberg Law and Bloomberg Tax, or its owners.

Author Information

Jeffrey A. Brown and Hartley M.K. West are co-chairs of Dechert’s enforcement and investigations practice group.

Jonathan Streeter is partner in Dechert’s enforcement and investigations practice group.

D. Brett Kohlhofer and Peter McGinley contributed to this article.

Write for Us: Author Guidelines

To contact the editors responsible for this story: Daniel Xu at [email protected] ; Melanie Cohen at [email protected]

Learn more about Bloomberg Law or Log In to keep reading:

Learn about bloomberg law.

AI-powered legal analytics, workflow tools and premium legal & business news.

Already a subscriber?

IMAGES

Oracle case statement Complete Guide with examples
Oracle CASE statement || SQL CASE statement with 2 Example
SQL CASE statement: Everything you need to know
Using Oracle CASE Expression By Practical Examples
CASE Expression in Oracle Explained with Example || Oracle Database Tutorial
Oracle CASE statement || SQL CASE statement with 2 Example

VIDEO

Oracle Aggregate Function, Case and Decode
Oracle DECODE and CASE statements with examples/ Difference between DECODE and CASE statements
Samsung N145
Oracle Case and Character based function
Oracle PL/SQL Tutorial 19-Loop Statement in PL/SQL with Example in Hindi
Oracle

COMMENTS

Using Oracle CASE Expression By Practical Examples
You can use a CASE expression in any statement or clause that accepts a valid expression. For example, you can use the CASE expression in statements such as SELECT, UPDATE, or DELETE, and in clauses like SELECT, WHERE, HAVING, and ORDDER BY. Oracle CASE expression has two formats: the simple CASE expression and the searched CASE expression.
Oracle / PLSQL: CASE Statement
This Oracle tutorial explains how to use the Oracle / PLSQL CASE statement with syntax and examples. The Oracle / PLSQL CASE statement has the functionality of an IF-THEN-ELSE statement. Starting in Oracle 9i, you can use the CASE statement within a SQL statement. ... Answer: Yes, below is an example of a case statement that evaluates two ...
CASE Expressions
Oracle Database Data Warehousing Guide for examples using various forms of the CASE expression Simple CASE Example For each customer in the sample oe.customers table, the following statement lists the credit limit as "Low" if it equals $100, "High" if it equals $5000, and "Medium" if it equals anything else.
IF-THEN logic in SELECT and WHERE with CASE expressions in Oracle SQL
There are several ways you can do this. One is to add the student and exam ids checks in separate when clauses at the top: Or you could have one clause that checks both the exam and student ids. Then place the original expression in the else clause: case. when exam_id = 0 or student_id = 0 then 'TEST'. else.
13.8 CASE Statement
simple_case_statement. selector. Expression whose value is evaluated once and used to select one of several alternatives. selector can have any PL/SQL data type except BLOB, BFILE, or a user-defined type.. WHEN selector_value THEN statement. selector_value can be an expression of any PL/SQL type except BLOB, BFILE, or a user-defined type.. The selector_value s are evaluated sequentially.
CASE Statement
The CASE statement chooses from a sequence of conditions, and executes a corresponding statement. The CASE statement evaluates a single expression and compares it against several potential values, or evaluates multiple Boolean expressions and chooses the first one that is TRUE. Syntax. searched_case_statement ::=. CASE { WHEN boolean_expression ...
Oracle Case Statement Explained with Tips and Examples
There is a lot else CASE can do though, which DECODE cannot. We'll go through detailed examples in this article. Let's start with the Case statement Syntax. CASE [expression] when condition_1 then value_1. when condition_2 then value_2. when condition_2 then value_2. …. else value_n.
ORACLE-BASE
The CASE expression is like a more flexible version of the DECODE function. The value match CASE expression, or simple CASE expression, compares the value of the expression ( DEPTNO ), with the list of comparison expressions (10 - 40). Once it finds a match, the associated value is returned. The optional ELSE clause allows you to deal with ...
CASE Expressions
In a searched CASE expression, Oracle searches from left to right until it finds an occurrence of condition that is true, and then returns return_expr. ... Searched CASE Example. The following statement finds the average salary of the employees in the sample table oe.employees, using $2000 as the lowest salary possible:
CASE Statement in Oracle PL/SQL with Examples
The 'END' marks the end of the CASE statement and, it is a mandatory part of CASE. Example 1: Arithmetic Calculation using Searched Case. In this example, we are going to do arithmetic calculation between two numbers 55 and 5. DECLARE a NUMBER :=55; b NUMBER :=5; arth_operation VARCHAR2(20) :='DIVIDE'; BEGIN.
CASE Statements and Expressions
The CASE statement allows you to select one sequence of statements to execute out of many possible sequences. They have been part of the SQL standard since 1992, although Oracle SQL didn't support CASE until the release of Oracle8 i Database, and PL/SQL didn't support CASE until Oracle9 i Database Release 1. From this release onwards, the following types of CASE statements are supported:
SQL CASE Statement Explained with Examples
SQL CASE Statement Syntax. The syntax of the SQL CASE expression is: CASE [expression] WHEN condition_1 THEN result_1. WHEN condition_2 THEN result_2 ... WHEN condition_n THEN result_n. ELSE result. END case_name. The CASE statement can be written in a few ways, so let's take a look at these parameters.
CASE .. WHEN expression in Oracle SQL
SELECT status, CASE status WHEN 'a1' THEN 'Active' WHEN 'a2' THEN 'Active' WHEN 'a3' THEN 'Active' WHEN 'i' THEN 'Inactive' WHEN 't' THEN 'Terminated' END AS StatusText FROM stage.tst Is there any other way of doing this where I don't need to write When expression 3 times for Active Status and the entire active status can be checked in one ...
ORACLE-BASE
In Oracle 23c we can do the same thing using a simple CASE statement or expression using dangling predicates. A dangling predicate is an expression with its left operand missing. This example uses a simple CASE statement with dangling predicates to achieve the same result.
Oracle CASE Expression
The CASE expression isn't limited to just SELECT statements. We can use it pretty much anywhere a valid expression can be used. For example, we can use the CASE expression as part of an UPDATE statement when updating data in a database. We can also use it in an INSERT statement, and even in an ORDER BY clause. See SQL CASE Statement for examples.
Oracle CASE Statement
In Oracle Database, the CASE statement compares a list of conditions and returns one of multiple possible expressions.. Oracle Database's CASE statement is very similar to the CASE expression (which is defined in the SQL standard (ISO/IEC 9075)). However, Oracle supports both the CASE expression and the CASE statement, and there's a distinction between the two.
Complex Case Statement in Oracle SQL
In Oracle, empty string values are treated as NULL, so you only need to check for NULL. Use NVL2 to produce 0 if NULL and 1 otherwise: SELECT 5 - ( NVL2(SUBBRAND, 1, 0) + NVL2(COLLECTIONS, 1, 0) + NVL2(PLCYCLE, 1, 0) + NVL2(COLORFAMILY, 1, 0) ) AS Priority I guess there is no case of all 4 columns to be NULL.
CASE Statement
CASE Statement. The Oracle CASE statement is used to perform IF-THEN-ELSE logic in queries. It evaluates a single expression and compares it against several potential values, or evaluates multiple Boolean expressions and chooses the first one that is TRUE. The interactive report below contains 2 CASE statements.
Examples of Copying Element Entries During an Employee Transfer
Example 1: Recurring element entry dates are before the global transfer date. An employee is paid a car allowance of $500 that starts on 1 January and ends on 30 January. On 1 August, you transfer them to a different legal employer. In this case, the process doesn't copy the car allowance element entry as its start and end date are before the ...
Defining Work Order Material Availability Rules: Worked Example
Material Availability Rules: Forced Assignment Criteria. Click Add in the Forced Assignment Criteria tab to exclude certain work orders from being lowered in priority on manual adjustment of the material assignment of other work orders. For example, if a work order WO123 is reserved against sales orders for a customer of high priority, you can ...
Find Guides, Readiness Material, and Other Resources
In this case, the first result is a guide, but you might also see other types of resources, such as videos, reference architecture and solution playbooks, or readiness material. And they might come from places other than the Help Center, for example My Oracle Support (https://support.oracle.com), oracle.com, or YouTube.
Judge indefinitely delays Trump's classified documents criminal trial
U.S. District Judge Aileen Cannon has indefinitely postponed former President Donald Trump 's classified documents trial in Florida pending the resolution of multiple pretrial issues. The trial ...
Examples of Impact on Summary and Detail Tax Lines for Tax Detail Changes
The second example illustrates the impact on the detail tax lines when you change the summary tax line from 300 to 600: The application adjusts the detail tax lines according to their calculated taxes. For example, Line-2 tax amount is the summary tax amount of 600 USD multiplied by 100/300 (600 * 33.333% = 200).
Supreme Court sides with music producer in copyright case over sample
Updated 3:51 PM PDT, May 9, 2024. WASHINGTON (AP) — The Supreme Court sided with a music producer in a copyright case Thursday, allowing him to seek more than a decade's worth of damages over a sample used in a hit Flo Rida song. The 6-3 decision came in a case filed by Sherman Nealy, who was suing over music used in the 2008 song "In the ...
CASE Statement
simple_case_statement. selector. Expression whose value is evaluated once and used to select one of several alternatives. selector can have any PL/SQL data type except BLOB, BFILE, or a user-defined type.. WHEN { selector_value | dangling_predicate} [ , ..., { selector_value | dangling_predicate} ] THEN statement selector_value can be an expression of any PL/SQL type except BLOB, BFILE, or a ...
SQL Bulk Insert Command Examples
The download includes sample data for each use case example and additional data samples to practice with fresh data. Use Case #1: Illustrates the basics of invoking a bulk insert statement for importing a TXT file and parsing selected elements of the imported file into a SQL Server results set. Use Case #2: Focuses on how to write T-SQL code to ...
The Kendrick Lamar vs. Drake Beef, Explained
The long-building and increasingly testy rap beef between Kendrick Lamar and Drake exploded into full-bore acrimony and unverifiable accusations over the weekend. Both artists rapid-fire released ...
6 Takeaways From Stormy Daniels's Testimony in Trump's Manhattan Trial
The judge, Justice Juan M. Merchan of State Supreme Court, agreed that some of Ms. Daniels's testimony might have "been better left unsaid," but he denied a mistrial. The former president is ...
SEC's Shadow Trading Win Opens Door for Department of Justice
For example, the DOJ last year filed criminal charges against the CEO of a publicly traded health-care company based on his use of a Rule 10b5-1 trading plan while allegedly in possession of MNPI. There, the DOJ pursued its case on the heels of a settled SEC enforcement action in 2022 charging two technology company executives under a similar ...