Posts Tagged ‘switch’

Writing basic case switch statements in Oracle PL/SQL

Oracle CASE

This is the equivalent code for writing a switch statement in Oracle. In Oracle to do the switch we use a function called case. Which looks for a value in the field specified, when it returns true it returns a value else returns a default. This has the same logic as writing an if statement pretty much, e.g. IF (value == “bob”) { do stuff } ELSE….

This is very very handy when having to do some reports and for instance the categories in your database don’t match up to what the report needs or you need combine a few columns together based on their values.

When writing a CASE typically it should be a part of your SELECT columns statement as below. We start it by declaring a CASE, and then a WHEN clause, so when value = xxx THEN do this…

SELECT
table.*,
CASE
  WHEN table.column_name = 'me' THEN 'yes'
  WHEN table.column_name = 'you' THEN 'no'
  ELSE 'neither'
END AS "Me or You"
FROM
table
WHERE ...

So when the value in our column equals ‘me’ then the value output in the results will be ‘yes’. We use the ELSE line to return a default if no WHEN clause is matched. We then end our CASE with END and then we can assign a name to this column using the AS command. Otherwise your column will be named as the entire CASE function which isn’t really that handy.

You can add multiple clauses just like a basic WHERE query on your WHEN clause for example:

  WHEN tableA.column_name = 'me' AND tableB.column_name = 'myself' THEN 'yes'

This is pretty handy when you want to base a case on multiple tables or columns and of course you can use OR, IS NOT etc… in the WHEN clause. For the THEN part you can also return another column instead of an arbitary value. So:

  WHEN tableA.column_name = 'me' AND tableB.column_name = 'myself' THEN tableC.column_some_name

And finally instead of just referring to a pre-defined column name from our tables we can write a SQL SELECT statement for the WHEN clause which is very handy when we want to switch on a record count for instance:

SELECT
column_name,
CASE
  WHEN (SELECT COUNT(tableD.column_name) FROM tableD WHERE column_name = 'me') > 0 THEN 'Yes'
  ELSE 'No'
END AS "me"
FROM
...