There are also abundant single row functions that are available, and some of them are easy to use, but sometimes no apparent usage immediately stands out. I make it no secret that my favourite online reference manual is the Oracle Database SQL Reference. I have a shortcut to a local version in my browser toolbar. Personally I prefer the 10g layout to the 11g one, but beggars can't be choosers I suppose.
I thoroughly recommend beginners and even the more mature programmers to peruse this manual, in particular the chapter on SQL Functions. I believe awareness is often the key to writing a successful application - or at least not re-inventing the wheel.
So to stop beating around the bush, what about the NULL-related functions, specifically
NULLIF. Have you used it?
The manual illustrates an example showing those employees whose job has changed since they were hired. If their job hasn't changed, then null is shown.
SELECT e.last_name, NULLIF(e.job_id, j.job_id) "Old Job ID" FROM employees e, job_history j WHERE e.employee_id = j.employee_id ORDER BY last_name; LAST_NAME Old Job ID ------------------------- ---------- De Haan AD_VP Hartstein MK_MAN Kaufling ST_MAN Kochhar AD_VP Kochhar AD_VP Raphaely PU_MAN Taylor SA_REP Taylor Whalen AD_ASST WhalenWhat about if an employee record has multiple e-mail address columns - an alternative point of contact. In some scenarios/records this could potentially be unclean, so instead of coding with these older statements:
CASE WHEN email_address != email_address_alternate THEN email_address_alternate ELSE null END email_address_alternate DECODE(email_address, email_address_alternate, null, email_address_alternate) email_address_alternateWe can use a tidier:
NULLIF(email_address_alternate, email_address)A similar situation applies to a mailing/delivery/home address (albeit ideally on a separate table) or a work/mobile/home number, but in this case the
COALESCEfunction is more appropriate. I plan a blog entry on this function in the near future, along with performance considerations.
Perhaps you are performing a data load and you need to ignore certain indicative data coming through:
NULLIF(surname, 'INACTIVE')The final scenario that I've started to use regularly is when faced with the old mathematical enigma of dividing by zero. (The wiki entry is a thrilling read, almost as fun as the images returned by google search)
Instead of facing this throughout a report:
select 1/0 from dual * ERROR at line 1: ORA-01476: divisor is equal to zeroWe can do this to return null where price is 0 instead of an error.
SELECT (price-cost)/NULLIF(price,0) gp FROM sales;Sweet.