I have an Ingres table with date field (data type ingresdate) which has a not null restriction. However blank i.e. empty values are allowed. How can you check for a blank value?
Of course testing for null values using ifnull() does not work - as per example below.
INGRES TERMINAL MONITOR Copyright 2008 Ingres Corporation
Ingres SPARC SOLARIS Version II 9.2.3 login
continue
create table test ( id integer not null, date_field ingresdate not null with default )\g
insert into test (id, date_field) values ( 1, '' )\g
insert into test (id, date_field) values ( 2, '31/12/2014' )\g
continue
(1 row)
continue
select id, date_field, ifnull( date_field, '01/01/2014' ) as test_field from test\g
(1 row)
continue
+-----------------------------------------------------------------+
¦id ¦date_field ¦test_field ¦
+-------------+-------------------------+-------------------------¦
¦ 1¦ ¦ ¦
¦ 2¦31/12/14 ¦31/12/14 ¦
+-----------------------------------------------------------------+
(2 rows)
continue
\q
Your SQL statement(s) have been committed.
Ingres Version II 9.2.3 logout
Actually, thanks to PaulM, I figured it out:
INGRES TERMINAL MONITOR Copyright 2008 Ingres Corporation
Ingres SPARC SOLARIS Version II 9.2.3 login
select id, date_field,
case when date_field = '' then '01/01/2014' else date_field end as test_field
from test
+-----------------------------------------------------------------+
¦id ¦date_field ¦test_field ¦
+-------------+-------------------------+-------------------------¦
¦ 1¦ ¦01/01/14 ¦
¦ 2¦31/12/14 ¦31/12/14 ¦
+-----------------------------------------------------------------+
(2 rows)
Ingres Version II 9.2.3 logout