SQL Query - Returning Y/N

At 02:49 PM 3/31/98 +0100, Mike Lay wrote:
> ...
>1,1-jan-1997,2-jan-1997,3-jan-1997
>2,1-jan-1997,2-jan-1997,NULL
>
>in the data would appear as
>
>1,1-jan-1997,2-jan-1997,Y
>2,1-jan-1997,2-jan-1997,N
>
>on the screen.
>
>Question: can this be done in one SQL query...

If you're willing to treat blank dates as if they were NULL (and from your
problem statement it sounds like this may be the case), yes:

select ,
CHAREXTRACT('YN',(LENGTH(VARCHAR(IFNULL(date3,'')))+2)/(LENGTH(VARCHAR(IFNUL
L(date3,'')))+1))
from ...

(x+2)/(x+1) where x is a non-negative int transforms to 2 if x==0, else 1.
thus you have CHAREXTRACT('YN',{2 if date3 null or '', else 1})


Karl R. Schendel, Jr.
K/B Computer Associates   schendel@kbcomputer.com
Ingres and Unix Expertise

NAIUA Member-At-Large     atlarge@naiua.org
Ingres Q & A
Back to William's Home Page
Email William