With a wink and a nod to all those late-night TV infomercials, I ask all you SQL coders out there, “Are you tired of having long and complex syntax in your SQL query? Do you SQL queries look like a never ending stream running down your monitor?” If so, do I have the function for you! And the best part? Unlike a late night sales pitch, this one is free! No shipping, no handling. It’s already built into most database query languages. It’s a handy little function called, “COALESCE.”

COALESCE is a way of taking many lines of SQL and truncating it down into one short statement when looking for a non-null value. This can eliminate lines and lines from the query. It also has the benefit of making the code easier to read and can aid in troubleshooting long queries.

I have a larger client that has JD Edwards EnterpriseOne (JDE E1) as their main backend system. I was working with one of their JDE E1 consultants who is very adept at writing SQL queries directly against the Oracle back end. He was doing a query that joined many tables together and trying to find specific data. He had several places where he was trying to find the first non-null and his query looked like this:

SELECT

CASE

WHEN (expression1 IS NOT NULL) THEN expression1

WHEN (expression2 IS NOT NULL) THEN expression2

WHEN (expression3 IS NOT NULL) THEN expression3

WHEN (expression4 IS NOT NULL) THEN expression4

WHEN (expression5 IS NOT NULL) THEN expression5

WHEN (expression6 IS NOT NULL) THEN expression6

WHEN (expression7 IS NOT NULL) THEN expression7

...

ELSE expressionN

END AS FirstNonNull

His query was getting so enormous and complex it was becoming difficult to keep track of the actual logic. The query was so long there wasn’t enough screen real estate to keep eyes on the entire thing. Scrolling from top to bottom and side to side was only making it more difficult. He was not getting the results he was expecting and was having issues troubleshooting the root cause because of the query’s size.

In this instance, the table structure for JDE E1 is not third normal form and the naming convention has been carried over from years past for backwards compatibility. Sometimes to get the data needed, the query must join many tables together and the SQL gets mammoth. On top of that, the naming convention is not easily understood. For example, to look for sales data, you find the header information on the F4201. For the detail records, you need to join in the F4211 table.

He asked me to take a look at the query to see if I could help him spot the issue. The code was impossible to read because of the size of his CASE statements. I asked him if we could reformat the query to use COALSCE instead. He was unfamiliar with the command so I explained that we could take all those lines in the CASE statement and cut them down to one. I went on to explain that COALESCE was a syntactical shortcut to the CASE statements he wrote. He questioned whether it would work on Oracle. I then brought up the documentation to show him it does indeed work in Oracle.

We were able to eliminate the many lines of CASE statements to one line like this:

SELECT COALESCE(expression1, expression2, expression3, expression4, expression5, expression6, expression7, ..., expressionN) AS FirstNonNull

He had several places where we could take advantage of COALESCE. This made troubleshooting the query much easier and we were able to find the issue quickly once we could more easily read the SQL statement.

Conclusion

COALESCE might seem like syntactic sugar, but it has real world implications. It can make life easier when maintaining complex queries. Keep an eye open for my second post about COALESCE in the next few weeks. I will be writing about some additional features that this wonderful function can do. In the meantime, you can contact me at Don@Codinginnovations.com