[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 265
  • Last Modified:

How to handle IS NULL

I need to join three site address fields together into one string. The code works fine where there a re values in each of the three address fields. However as soon as one of the three fields is blank then the record returned is blank.

I have created this code to try and say if a field is blank put a "" . However it just keeps telling me there is a syntax problem.

CASE WHEN site_address2 IS NULL THEN (' ') ELSE site_address2
0
CMChalcraft
Asked:
CMChalcraft
1 Solution
 
Rajkumar GsSoftware EngineerCommented:
Did you add END ?
CASE WHEN site_address2 IS NULL THEN ' ' ELSE site_address2 END
0
 
Paul JacksonCommented:
use

COALESCE(Site_address2, ' ')
0
 
Shaun KlineLead Software EngineerCommented:
You could also just use the ISNULL(<field>, <value>) function.
0
 
LowfatspreadCommented:

use
coalesce(address1+',','')+coalesce(address2+',','')+coalesce(address3,'') as address


coalesce is a function which takes a list of columns/expressions and returns the first non null one from the left....


it is "equivalent" to your case statement...

case when address1+',' is null then '' else address1 end
+case when address2+',' is null then '' else address2 end
+case when address3 is null then '' else address3 end  

0
 
CMChalcraftAuthor Commented:
Thanks to everyone who answered. Lowfatspread was the simplest solution. I have never heard of coalesce before.

Regards

Chris C
0

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now