Q
Problem solve Get help with specific problems with your technologies, process and projects.

# DECODE versus CASE, continued

This example illustrates the advantage of CASE over DECODE...

The next example illustrates the advantage of CASE over DECODE. It computes shipping charges based on a subtotal, according to these rules

• Subtotal under \$10.00:  Shipping is \$3.00
• \$10.00 to \$19.99:  \$5.50
• \$20.00 to \$99.99:  \$7.50
• \$100.00 or more:  7% of subtotal (10% outside US)
 DECODE Searched CASE ```SELECT subtotal, country, DECODE ( SIGN (subtotal - 20.00), -1, DECODE ( SIGN (subtotal - 10.00), -1, 3.00, 5.50 ), DECODE ( SIGN (subtotal - 100.00), -1, 7.50, DECODE ( country, 'US', .07, .10 ) * subtotal ) ) AS shipping FROM order ... ;``` ```SELECT subtotal, country, CASE WHEN subtotal < 10.00 THEN 3.00 WHEN subtotal < 20.00 THEN 5.50 WHEN subtotal < 100.00 THEN 7.50 WHEN country = 'US' THEN subtotal * .07 ELSE subtotal * .10 END AS shipping FROM order ... ;```

CASE, like DECODE, works from left to right: it evaluates the WHEN-conditions in order, and as soon as it finds one that is true, it returns the corresponding THEN-value and quits. For example, in computing the shipping charges above, suppose the subtotal is 15.00. CASE will first test to see if subtotal is less than 10.00. It isn't, so CASE will move on to the next condition, and test if subtotal is less than 20.00. It is, so CASE immediately returns 5.50: it does not perform any of the remaining tests.

Anything you can do with CASE can be done in DECODE, but it's likely to be tortuous. CASE is almost always easier to read and understand, and therefore it's easier to debug and maintain.

This was last published in August 2003

#### Have a question for an expert?

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

#### Start the conversation

Send me notifications when other members comment.

## SearchDataManagement

• ### Originators form group to boost Presto SQL query engine

The Presto engine arose as an alternative to Hive for big data queries. Now, the Presto Software Foundation has formed to promote...

• ### 5 best practices for managing real-time data integration

Real-time data integration isn't like traditional data integration -- "it's moving, it's dirty and it's temporal," cautions one ...

• ### Web integration platform eases way to machine learning models

StoryFit data scientists employ machine learning algorithms to gauge film script scenarios' prospects. They use Import.io tools ...

• ### Avoid turbulence when shifting to data analytics in the cloud

When migrating BI and data analytics to the cloud, factor existing analytics processes, software evaluation, data protection and ...

• ### Upswing in cloud business intelligence spawns migration issues

More organizations are looking to run BI and analytics applications in the cloud. This handbook offers insights into moving ...

• ### Information Builders CEO on cloud-first approach, machine learning

Information Builders' new CEO, Frank Vella, said the company's focus on cloud and machine learning will help it solidify a firm ...

## SearchSAP

• ### SAP managed services weed out IT complexity at Ball Horticulture

Ball Horticultural outsourced its SAP Basis administration to Symmetry when the complexity of its SAP environment grew, but ...

• ### Haribo can't bear SAP S/4HANA implementation

Haribo's recent deployment of S/4HANA may have led to problems getting its products into stores, but experts believe they could ...

• ### 10 record-to-report tips in SAP ECC for audit compliance

Ensuring a comprehensive segregation of duties and limiting the financial postings that can be made manually are some of the ways...

## SearchSQLServer

• ### SQL Server auditing best practices: 3 key questions for DBAs

Acing a SQL Server database audit starts with careful monitoring of how sensitive data is accessed and used so you can answer the...

• ### Talend Stitch tools load data to Azure SQL Data Warehouse

Cloud data warehouses see serious data transformation work moving to the cloud. Talend's first integration with Stitch, which it ...

• ### 5 trends for SQL Server environments as SQL Server 2019 looms

SQL Server is undergoing new changes, as Microsoft prepares to release the 2019 version of the database software. Other changes ...

## TheServerSide.com

• ### Convert JPEG to SVG to improve webpage performance

Vector images open the door for multiple benefits on HTML webpages. Consider SVGs instead of raster images to boost HTML speed ...

• ### Master-slave terminology alternatives you can use right now

Software companies have found alternatives for master-slave terminology to describe their distributed systems. It's time for the ...

• ### RESTful parameters antipattern considerations for queries, paths

Choose carefully for path and query parameters in URL design. Lackluster choices in the design phase can plague client resource ...

## SearchDataCenter

• ### A data center planning and design guide for IT pros

Data center design and management now goes beyond servers and networking cables. Defining budget and IT goals helps set an ...

• ### Data center budget process should review business goals first

Discover four factors data center managers should evaluate when allocating financial resources and areas to address to ensure ...

• ### How to prepare your data center for an HCI appliance

Vendors tout hyper-converged infrastructure as a plug-and-play offering, but before you install anything in your data center, be ...

## SearchContentManagement

• ### How content services can speed up cloud content migration

During a continuous, or hot, migration, a content services platform can move content from the legacy repository to a new one -- a...

• ### Personalized content solidifies the customer experience

Today's customers expect customized, curated digital experiences. Learn how to deliver what customers need and stay ahead of the ...

• ### Create a winning personalized customer journey

Web content management platforms, customer data platforms and matchmaking can all help companies create and implement a ...

## SearchHRSoftware

• ### AI in recruiting may be above HR's head

Video job interviews are powerful tools. HireVue's AI algorithm can watch a candidate without losing focus and notice things a ...

• ### Cybersecurity education: How HR can plan for the inevitable

Cybersecurity is a major concern for any organization, but employees continue to be a top threat. HR can help mitigate insider ...

• ### LinkedIn's recruiting platform billed as 'Intelligent Hiring Experience'

LinkedIn's use of AI requires the sharing of data. It is one reason why the company is combining its talent tools into one system...

Close