Home | Forums | Contact | Search | Syndication  
 [login] [create account]   Friday, January 18, 2019 
How to use COALESCE with SalesLogix Queries  
Description:  Eugenio takes a look at how to use COALESCE in your SalesLogix queries to make the task of building name strings from multiple fields without the need for checking for NULLs.

Category:  Architect How To Articles
Author:  Eugenio Gil
Submitted:  9/5/2005
Article has been read 27729 times

Rating: - 5.0 out of 5 by 6 users

How to use COALESCE with SalesLogix Queries

I can't describe the happiness when I discovered the COALESCE function, and that only can be compared to the happiness I experienced when I found out that it works for SQL Server and Oracle!

This great function is intended to return the first not NULL value from a list of possible values. Though by simply looking at it would not add too much to our day, with a bit little of twisting it can resolve many problems for us before hand, so we don't have to deal with them in our code later on.

For example, who can say that he/she didn't have to concatenate the separate components of the contact/user name, having to deal with the spaces between the components, the comma, etc. It would take you at least 4-10 lines of code to have that done in VBScript, depending how neat you want it to look like.

Building a Contact Name

Here is how you have to do to let COALESCE do the work for you.

For SQL Server

		firstname + ' ' + middlename + ' ' + lastname, firstname + ' ' + lastname, 
		firstname + ' ' + middlename, 
		middlename + ' ' + lastname, 
		'John Doe!' 

For Oracle (darn Oracle and double pipes, why can't just they be normal)

		firstname || ' ' || middlename || ' ' || lastname, firstname || ' ' || lastname, 
		firstname || ' ' || middlename, 
		middlename || ' ' || lastname, 
		'John Doe!' 

Now let's analyze how COALESCE resolves this. The first possible value is the concatenation of the 3 components. As you might now (if you don't know, then you will soon find out) concatenating any value with a NULL will result in a NULL value (all hail NULL)

So there you have, if any of the three components is NULL, the first result will be NULL, so our beloved COALESCE will discard that, and check the next possible value. Now takes precedence the existance of first and last name, if any is NULL, then the next evaluation is on firstname and middle name, if any is NULL, then middle name and last name, if any is NULL null then is going to take the firstname, if that is null, the middle name, and if that is null, the last name.

If all the components are NULL, then the result will return the string constant 'John Doe!'

Bench Test with Sample Data


		NULL, NULL, NULL ---> 'John Doe!' 

		'Mike', NULL, 'Boysen' ---> 'Mike Boysen' 

		'Prince', NULL, NULL ---> 'Prince' 

		NULL, 'Prince', NULL ---> 'Prince' 

		NULL, NULL, 'The artist previously known as' ---> 'The artist previously known as' 

		NULL, 'RJ', 'Ledger' ---> 'RJ Ledger' 

		'Eugenio', 'Emilio', 'Gil' ---> 'Eugenio Emilio Gil'

To Result Last, First (and Middle)

For SQL Server (SQL Only, let Oracle find out its own way)

		lastname + ', ' + firstname + ' ' + middlename, 
		lastname + ', ' + firstname, 
		lastname + ', ' + middlename, 
		'Camel Spotting' 

More on COALESCE to come in future articles!
PS: You can also use this for USERINFO data!


About the Author

Eugenio Gil
(SalesLogix Business Partner)
Castle CRM

My name is Eugenio Emilio Gil, I've been working with SalesLogix since 1999 (when I went to AZ for the administrator's training). At that time we were using SalesLogix 3.1.6 (Yes when the application CD case would show Pat's face!!!)

My BP was TCG in Argentina, I worked for them for 3 years having to learn (as probably all of us) from our own trials and errors.

After graduating from a technical high school as an Electromechanical Technician, my first Job was at Sullair Sudamericana (Arg) from 1992 to 1993, worked with Macintosh LCII programming in foxbase for mac and drawing air compressor parts with Minicad on Mac. At the time Windows 3.0 was just comming out, and the best CAD for PC was AutoCad 10 (which was quite auful and limmited compared to Mac). I also suffered the transition between Foxbase and FoxPro 2.0 (when MS acquired foxpro) - Clipper fans don;t say a word please!

After that I worked for a Company that manufactured Self Vending machines for Public Transports. I had to program some E2PROM and I also learned to program PLC (Programmable Logic Controllers, power inputs and outputs) in a language call "Ladder", because it actually looked like a ladder. By the time windows 3.11 was getting "reliable", I spend my days drawing parts for these vending machines with Minicad for Macs. I also interfaced MS Foxpro 2.5/2.6 for windows with the E2PROMS to acquire the data from the vending machines, and put it on the foxpro MIS system I had to develop. I had to travel to Europe (visiting Madrid, Barcelona, London, and Italy) to train myself in other product we were prospecting.

After that, I worked for an Accounting Software company, learning all the tricks in the accounting world, also with foxpro.

In 1996, I worked for Unisys Sudamericana (yes, when Unisys was still a respectable company!) that was my first contact with VB3 (the project I worked on also involved self vending machines, this time for the national postal service)

In 1997 I got a job in the largest Logistics company in Argentina, and learnt quite a lot of Logistics, Distribution, Ware house management, etc. I managed to create a full Logistics system with wireless data collectors with bar code reading in FPW, inventory, storing, picking, distribution, etc.

After graduating from School as a Bachelor in Computers Science, I got married, I came to the states, and worked for Harvest Solutions (BP in Mass), and currently I'm working for Castle CRM in NYC (remotelly, and doing some on-site installations, upgrades, trainings, etc)

I love/play Soccer (football for us 8^)), tennis, squash, ping pong and any raques sport. I read about lots of subjects, specially philosophy, mithologies, and religions. I'm a trekkie, a Star Wars fan, also any Science fiction stuff from guys such us Asimov, Niven, Clarke and others too.

View online profile for Eugenio Gil

[ back to top] [ send to a friend]  

Rate This Article you must log-in to rate articles. [login here] 
Please log in to rate article.

Comments & Discussion you must log-in to add comments. [login here] 
Author Article Comments and Discussion
Bob (RJ)Ledger

slxdeveloper.com Forum Top 10 Poster!

Re: How to use COALESCE with SalesLogix Queries
Posted: 9/6/2005 10:29:26 AM
Cool.. (especially since you promoted my name ;-)
Ryan Farley

slxdeveloper.com Site Administrator
slxdeveloper.com Forum Top 10 Poster!

Re: How to use COALESCE with SalesLogix Queries
Posted: 9/20/2005 12:37:01 PM
Good stuff Eugenio.

Carla Tillman

Re: How to use COALESCE with SalesLogix Queries
Posted: 1/30/2006 8:43:48 AM
Sometimes its learning the simple commands that make a difference. I have used this command in several situations/variations since reading this article several months ago. Thanks Eugenio!

Eugenio Gil

Re: How to use COALESCE with SalesLogix Queries
Posted: 1/30/2006 8:56:43 AM
Thanks :)

       Visit the slxdeveloper.com Community Forums!
Not finding the information you need here? Try the forums! Get help from others in the community, share your expertise, get what you need from the slxdeveloper.com community. Go to the forums...

 slxdeveloper.com is brought to you courtesy of Ryan Farley & Customer FX Corporation.
 This site, and all contents herein, are Copyright © 2019 Customer FX Corporation. The information and opinions expressed here are not endorsed by Sage Software.

code of conduct | Subscribe to the slxdeveloper.com Latest Article RSS feed
page cache (param): 1/18/2019 2:29:31 AM