[thelist] Tip: SQL - Oracle: Selecting a character flag based on one (or more) of two fields not being null

Luther, Ron Ron.Luther at hp.com
Wed Jun 26 11:05:01 CDT 2002

Hi Gang,

I've been busy playing 'catch-up' around here so I haven't been very vocal lately.

It may not be the most efficient solution, but here's a tip I came up with yesterday that someone else may find useful:

<tip type="SQL - Oracle: Selecting a character flag based on one (or more) of two fields not being null"  author="RonL.">

Set-up: The client wanted a flag, (a capital "X"), to appear in a column in the result set if there was a date in field_1 or a date in field_2.  If both fields are null, then the column should show a blank.  If either field is not null, then the column should show an "X".

Solution: Select some_stuff, DECODE(NVL (  TO_CHAR(AL1.FIELD_1,'YYYY'), 'Z' ) || NVL(  TO_CHAR(AL1.FIELD_2,'YYYY'),'Z'), 'ZZ', '','X'), some_more_stuff from tables where conditions order by desired_sort

Let's break that down:

The TO_CHAR(field,'YYYY') turns each date into a four digit character string, like '2002'.

The NVL(above, 'Z') turns each null value into a "Z".

So, at this point each date field is either a "Z" or a number-like string "2002".

The || concatenates the two string variables together.  So we've either got "20022002", "Z2002", "2002Z", or "ZZ".  [And we only get "ZZ" if both date fields were originally null.]

The DECODE(above, 'ZZ', '', 'X') turns any "ZZ" into a blank, and anything else into our "X" flag.  [So if both dates were null we get a blank space, if either date (or both) have a value then we get our "X".]

<scratches head />Funny, it didn't seem so straight forward yesterday ...

Anyway, this can be extended to more than two fields through nesting and concatenation, but that would get even uglier!


More information about the thelist mailing list