4 Replies - 3312 Views - Last Post: 31 January 2015 - 03:52 AM

#1 jjsaw5  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 92
  • View blog
  • Posts: 3,063
  • Joined: 04-January 08

CASE statement help

Posted 07 May 2014 - 01:04 PM

I am trying to write a CASE statement in my select to do the following.
SELECT
FIRST_NAME,
LAST_NAME,
(CASE WHEN ADDRESS1 IS NOT NULL THEN ADDRESS1 ELSE ADDRESS2) ADDRESS
FROM
PEOPLE




I need to check the value in ADDRESS1 and if it is NOT NULL then i need to use that value. If it is NULL then i need to use the value in ADDRESS2.

Having issues with the syntax, I'm getting a missing expression error.

Is This A Good Question/Topic? 0
  • +

Replies To: CASE statement help

#2 astonecipher  Icon User is offline

  • Too busy for this
  • member icon

Reputation: 2329
  • View blog
  • Posts: 9,356
  • Joined: 03-December 12

Re: CASE statement help

Posted 07 May 2014 - 01:29 PM

I don't know Oracle database but, and I am sure you have looked , the docs state the expression is optional. I have a question for you though. Is that a stray field name after the case () Address ? Or is it missing the comma?
Was This Post Helpful? 0
  • +
  • -

#3 allenb_3  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 23
  • Joined: 07-February 11

Re: CASE statement help

Posted 30 January 2015 - 09:13 PM

You should be able to put END right before the second parenthesis.
This is cleaner and easier version of that query.
SELECT 
   FIRST_NAME,  
   LAST_NAME,
   NVL2(ADDRESS1, ADDRESS1, ADDRESS2)
FROM PEOPLE



NVL2( string1, value_if_NOT_null, value_if_null )

string1 is the string to test for a null value.
value_if_NOT_null is the value returned if string1 is not null.
value_if_null is the value returned if string1 is null.

This post has been edited by allenb_3: 30 January 2015 - 09:15 PM

Was This Post Helpful? 0
  • +
  • -

#4 andrewsw  Icon User is online

  • lashings of ginger beer
  • member icon

Reputation: 6337
  • View blog
  • Posts: 25,550
  • Joined: 12-December 12

Re: CASE statement help

Posted 31 January 2015 - 02:56 AM

The word END is required.

View Postastonecipher, on 07 May 2014 - 08:29 PM, said:

I have a question for you though. Is that a stray field name after the case () Address ? Or is it missing the comma?

ADDRESS is a column alias.
Was This Post Helpful? 0
  • +
  • -

#5 baavgai  Icon User is offline

  • Dreaming Coder
  • member icon


Reputation: 6966
  • View blog
  • Posts: 14,572
  • Joined: 16-October 07

Re: CASE statement help

Posted 31 January 2015 - 03:52 AM

As noted, don't forget the end ( I do that all the time. )

In this case, a simple NVL ( not NVL2 because, why? ) would do the trick.

SELECT
    FIRST_NAME, LAST_NAME,
    NVL(ADDRESS1,ADDRESS2) AS ADDRESS
  FROM PEOPLE


Was This Post Helpful? 0
  • +
  • -

Page 1 of 1