Oracle’s Decode and Case usages


Let me give you a simple example like the below I have coded;

CASE usage;

SELECT firstname,lastname,
 CASE
  WHEN customer_Id >= 0 AND customer_Id <= 1000 THEN ‘Regular Customer’
  WHEN customer_Id >= 1001 AND customer_Id <= 2000 THEN ‘Premium Customer’
  WHEN customer_Id >= 2001 AND customer_Id <= 3000 THEN ‘Corporate Customer’
  ELSE ‘Basic Customer’
 END
FROM customers;

DECODE usage;

The following example, DECODE function takes customer_Id value and compare it with the second value which is NULL at this point and if they are equal then the third value (‘No Id was found’) replaced instead of using NULL.  If it is not NULL then the fourth column is used

–>syntax DECODE(colum_to_be_tested, test_expression,if_the_expression_is_TRUE,if_the_expression_is_FALSE)

SELECT firstname, DECODE(customer_Id,NULL,’No Id was found’,customer_Id), surname
FROM customers;

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s