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

[Luther, Ron]

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!

</tip>