How to query several database fields based on a a single case structure

Question

In my Oracle database, I have a table called Customers. There are fields for name, zip, city etc., but there are also fields for invoicename, invoicezip and invoicecity. For some records the invoicespecific fields are not set, and in that case the invoice method should use the information from name, zip and city.

I use the following query now:

select
  case when c.InvoiceName is null then c.Name else c.InvoiceName end as Name,
  case when c.InvoiceName is null then c.Zip  else c.InvoiceZip  end as Zip,
  case when c.InvoiceName is null then c.City else c.InvoiceCity end as City  
  <...>
from
  Customers c
where
  Accountnumber=:accountnumber

Note that the check is on InvoiceName only. If that value is set I want to return all the invoice-specific fields, if not I want to return all the "non-invoice-specific" fields.

What I would like to do is something like this

select
  case when c.InvoiceName is not null
    then 
      c.InvoiceName as Name, 
      c.InvoiceZip as Zip, 
      c.InvoiceCity as City
      <...>
    else 
      c.Name, 
      c.Zip, 
      c.City
      <...>
  end
from
  customers c

Is there way to do this or something like it?

Answer 1

I think you need the COALESCE function to clean up your select statement. It will take the first non null value in the list.

select 
  coalesce(c.InvoiceName, c.Name) Name, 
  coalesce(c.InvoiceZip, c.Zip) Zip, 
  coalesce(c.InvoiceCity, c.City) City 

EDIT: By shahkalpesh
I am assuming that fields with Invoice in it will be NULL, when InvoiceName is NULL.

select 
  coalesce(c.InvoiceName, c.Name) Name, 
  coalesce(c.InvoiceName, c.Zip, c.InvoiceZip) Zip, 
  coalesce(c.InvoiceName, c.City, c.InvoiceCity) City 

Answer 2

A CASE expression can only return one value, not 3, so you do need the 3 CASE expressions. Using DECODE would be slightly more concise, but DECODE is Oracle-specific whereas CASE is ANSI standard and so preferable. But for info it would look like:

select
  decode(c.InvoiceName,null,c.Name,c.InvoiceName) as Name,
  decode(c.InvoiceName,null,c.Zip,c.InvoiceZip) as Zip,
  decode(c.InvoiceName,null,c.City,c.InvoiceCity) as City  
  <...>
from
  Customers c
where
  Accountnumber=:accountnumber

Another rarely-used but very applicable Oracle-specific function is NVL2:

select
  nvl2(c.InvoiceName,c.InvoiceName,c.Name) as Name,
  nvl2(c.InvoiceName,c.InvoiceZip,c.Zip) as Zip,
  nvl2(c.InvoiceName,c.InvoiceCity,c.City) as City  
  <...>
from
  Customers c
where
  Accountnumber=:accountnumber