Frank Swarbrick wrote:
>>You can use the built-in NULLIF function for that.
If you have a string consisting of whitespaces only and you want to have
this string interpreted as NULL date, then a CASE expression is needed:
CASE
WHEN TRANSLATE(str, '', ' ') = ''
THEN NULL
ELSE str
END
If it is permissable to remove whitespaces from the string even if the
string is not empty (and should not be stored as NULL), you can nest
TRANSLATE into NULLIF:
NULLIF(TRANSLATE(str, '', ' '), '')
I'm not sure I understand why either of these is necessary. I just did
the following:
CREATE TABLE TEST2 (TIMESTAMP1 TIMESTAMP , NULL1 VARCHAR(100) );
EXEC SQL
BEGIN DECLARE SECTION
END-EXEC
01 SQL-FIELDS.
05 TIMESTAMP1 PIC X(26).
05 NULL1VC PIC X(100).
EXEC SQL
END DECLARE SECTION
END-EXEC
PROCEDURE DIVISION.
EXEC SQL
CREATE TABLE TEST2 (TIMESTAMP1 TIMESTAMP , NULL1 VARCHAR(100)
);
END-EXEC
MOVE '2006-11-24-11.58.53.012345'
TO TIMESTAMP1
MOVE 'This is a test' TO NULL1VC
PERFORM INSERT-TEST2
MOVE SPACES TO TIMESTAMP1, NULL1VC
PERFORM INSERT-TEST2
.
INSERT-TEST2.
EXEC SQL
INSERT INTO TEST2 (
TIMESTAMP1,
NULL1
)
VALUES (
NULLIF(:TIMESTAMP1,''),
NULLIF(RTRIM(:NULL1VC),'')
)
END-EXEC
.
I ended up with two rows, the first having the timestamp and varchar field
set appropriately, and the second having both set to NULL. Seems right to
me.
You should verify your results. If I read the above code correctly and
translate it to pure SQL, then this would be equivalent:
CREATE TABLE test2( t timestamp, n varchar(100) );
INSERT INTO test2
VALUES ( NULLIF('2006-11-24-11.58.53.012345', ''),
NULLIF(RTRIM('This is a test'), '') );
INSERT INTO test2
VALUES ( NULLIF('2006-11-24-11.58.53.012345', ''),
NULLIF(RTRIM(' '), '') );
The result of the first insert cannot have NULL in the second column because
the strim 'This is a test' is not empty, even if trimmed:
SELECT * FROM test2
T N
-------------------------- ---------------------------
2006-11-24-11.58.53.012345 This is a test
2006-11-24-11.58.53.012345 -
Anyway, my whole point about using TRANSLATE (RTRIM is equally fine) for
converting from string to timestamp is that a string like ' ' is _not_ a
valid timestamp. You have to remove the spaces so that NULLIF will truly
get an empty string and convert that to NULL.
VALUES timestamp(' ')
SQL0180N The syntax of the string representation of a datetime value is
incorrect. SQLSTATE=22007
create table test2( t timestamp, n varchar(100) )
While we're on the subject of date/time stuff, is there a function that
takes in, say, a DECIMAL(8) or DECIMAL(9) field set to something like
20061103 and translates it into a DB2 DATE field? I see there's a DATE()
function, but that doesn't quite do what I'm looking for, as far as I can
tell. Of course it's simple enough to fiddle around using a separate
COBOL working-storage field to get it to work, but it would be nice if I
didn't have to.
Maybe the TO_DATE function works for you?
Other than that, you could apply some calculations and stick with the DATE
function:
CREATE FUNCTION getDate(d INT)
RETURNS DATE
RETURN DATE('0001-01-01') +
((d / 10000) - 1) YEARS +
(MOD(d / 100, 100) - 1) MONTHS +
(MOD(d, 100) - 1) DAYS;
VALUES getDate(20061127)
1
----------
11/27/2006
--
Knut Stolze
DB2 Information Integration Development
IBM Germany
