4/19/2024 7:28:54 AM
slxdeveloper.com
Now Live!
|
|
| |
| 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
|
| |
|
| Stats: |
Article has been read 30057 times
|
Rating:
 - 5.0 out of 5 by 6 users |
|
|
|
fiogf49gjkf0d
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.
Here is how you have to do to let COALESCE do the work for you.
For SQL Server
SELECT COALESCE (
firstname + ' ' + middlename + ' ' + lastname, firstname + ' ' + lastname,
firstname + ' ' + middlename,
middlename + ' ' + lastname,
firstname,
middlename,
lastname,
'John Doe!'
) CONTACTNAME FROM contact
For Oracle (darn Oracle and double pipes, why can't just they be normal)
SELECT COALESCE (
firstname || ' ' || middlename || ' ' || lastname, firstname || ' ' || lastname,
firstname || ' ' || middlename,
middlename || ' ' || lastname,
firstname,
middlename,
lastname,
'John Doe!'
) CONTACTNAME FROM contact
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!'
FIRSTNAME, MIDDLENAME, LASTNAME ---> Result
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'
For SQL Server (SQL Only, let Oracle find out its own way)
SELECT COALESCE (
lastname + ', ' + firstname + ' ' + middlename,
lastname + ', ' + firstname,
lastname + ', ' + middlename,
lastname,
firstname,
middlename,
'Camel Spotting'
) CONTACTNAME FROM contact
More on COALESCE to come in future articles!
PS: You can also use this for USERINFO data!
|
|
Eugenio Gil
(SalesLogix Business Partner)
Castle CRM
fjrigjwwe9r1SiteUser:UserBio fiogf49gjkf0d 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
|
|
|
|
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]
|
|
|
- subject is missing.
- comment text is missing.
|
|
|
| Re: How to use COALESCE with SalesLogix Queries
Posted: 9/6/2005 10:29:26 AM | fiogf49gjkf0d Cool.. (especially since you promoted my name ;-)
--
RJLedger | |
|
| Re: How to use COALESCE with SalesLogix Queries
Posted: 9/20/2005 12:37:01 PM | fiogf49gjkf0d Good stuff Eugenio.
-Ryan | |
|
| Re: How to use COALESCE with SalesLogix Queries
Posted: 1/30/2006 8:43:48 AM | fiogf49gjkf0d 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!
Carla | |
|
| Re: How to use COALESCE with SalesLogix Queries
Posted: 1/30/2006 8:56:43 AM | fiogf49gjkf0d 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...
|
|
|
|
|
|