Hi,

My SQL returns a NULL in a datefield if there is no date in that field.
If there is a NULL in this column, I want to replace it with spaces in
my SELECT statement when I am selecting these records. Can any of you
experts out there help me with a sample SQL.

SELECT COALESCE(VARCHAR(datefield), '') FROM X...

satishrajana@gmail.com wrote:

Hi,
>
My SQL returns a NULL in a datefield if there is no date in that field.
If there is a NULL in this column, I want to replace it with spaces in
my SELECT statement when I am selecting these records. Can any of you
experts out there help me with a sample SQL.

Thank you. I was trying the coalesce but did not cast it to VARCHAR.
I really appreciate your help. It works now.

Have a good day !
jefftyzzer wrote:

SELECT COALESCE(VARCHAR(datefield), '') FROM X...
>
satishrajana@gmail.com wrote:

My pleasure. Glad it worked!

--Jeff

satishrajana@gmail.com wrote:

Thank you. I was trying the coalesce but did not cast it to VARCHAR.
I really appreciate your help. It works now.
>
Have a good day !
jefftyzzer wrote:

This is pretty darn cool! Much nicer than using "indicator variables" if
you don't really care that it's a NULL field.

I don't suppose that there's anything similar going the opposite direction.
In other words,

MOVE SPACES TO NULLABLE-CHAR-FIELD

EXEC SQL
INSERT INTO MYTABLE (NULLABLE_CHAR_FIELD)
VALUES (NULL_IF_BLANK(:NULLABLE-CHAR-FIELD))
END-EXEC

Does anything like my made up function "NULL_IF_BLANK" exist, where it
returns the input field unless the input field is all spaces in which case
it would return NULL?
If not, I may have to give a shot to making my own. Sounds fairly simple, I
think.

Thoughts?

Frank

---
Frank Swarbrick
Senior Developer/Analyst - Mainframe Applications
FirstBank Data Corporation - Lakewood, CO USA

My pleasure. Glad it worked!

--Jeff

satishrajana@gmail.com wrote:

Thank you. I was trying the coalesce but did not cast it to VARCHAR.
I really appreciate your help. It works now.
>
Have a good day !
jefftyzzer wrote:

field.

in

you

You might try:
INSERT INTO MYTABLE (NULLABLE_CHAR_FIELD)
VALUES (CASE WHEN :NULLABLE-CHAR-FIELD = ''
THEN NULL
ELSE :NULLABLE-CHAR-FIELD
)

Phil Sherman

Frank Swarbrick wrote:

This is pretty darn cool! Much nicer than using "indicator variables" if
you don't really care that it's a NULL field.
>
I don't suppose that there's anything similar going the opposite direction.
In other words,
>
MOVE SPACES TO NULLABLE-CHAR-FIELD
>
EXEC SQL
INSERT INTO MYTABLE (NULLABLE_CHAR_FIELD)
VALUES (NULL_IF_BLANK(:NULLABLE-CHAR-FIELD))
END-EXEC
>
Does anything like my made up function "NULL_IF_BLANK" exist, where it
returns the input field unless the input field is all spaces in which case
it would return NULL?
If not, I may have to give a shot to making my own. Sounds fairly simple, I
think.
>
Thoughts?
>
Frank
>
---
Frank Swarbrick
Senior Developer/Analyst - Mainframe Applications
FirstBank Data Corporation - Lakewood, CO USA
>

My pleasure. Glad it worked!
>
--Jeff
>
satishrajana@gmail.com wrote:

field.

in

you

>
>
>

Frank Swarbrick wrote:

>
MOVE SPACES TO NULLABLE-CHAR-FIELD
>
EXEC SQL
INSERT INTO MYTABLE (NULLABLE_CHAR_FIELD)
VALUES (NULL_IF_BLANK(:NULLABLE-CHAR-FIELD))
END-EXEC
>
Does anything like my made up function "NULL_IF_BLANK" exist, where it
returns the input field unless the input field is all spaces in which case
it would return NULL?
If not, I may have to give a shot to making my own. Sounds fairly simple, I
think.

Can you use NULLIF? Like this.
EXEC SQL
INSERT INTO MYTABLE (NULLABLE_CHAR_FIELD)
VALUES (NULLIF(:NULLABLE-CHAR-FIELD,''))
END-EXEC

Frank Swarbrick wrote:

This is pretty darn cool! Much nicer than using "indicator variables" if
you don't really care that it's a NULL field.

The main thing to recognize here is that the result is a string and not a
date value. An empty string '' is not a valid date in the first place.

I don't suppose that there's anything similar going the opposite
direction. In other words,
>
MOVE SPACES TO NULLABLE-CHAR-FIELD
>
EXEC SQL
INSERT INTO MYTABLE (NULLABLE_CHAR_FIELD)
VALUES (NULL_IF_BLANK(:NULLABLE-CHAR-FIELD))
END-EXEC
>
Does anything like my made up function "NULL_IF_BLANK" exist, where it
returns the input field unless the input field is all spaces in which case
it would return NULL?
If not, I may have to give a shot to making my own. Sounds fairly simple,
I think.

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, '', ' '), '')

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

Tonkuma<tonkuma@jp.ibm.com11/22/06 11:17 PM >>>

>
>Frank Swarbrick wrote:

case

simple, I

>Can you use NULLIF? Like this.
>EXEC SQL
INSERT INTO MYTABLE (NULLABLE_CHAR_FIELD)
VALUES (NULLIF(:NULLABLE-CHAR-FIELD,''))
>END-EXEC

That is perfect! Thank you!

---
Frank Swarbrick
Senior Developer/Analyst - Mainframe Applications
FirstBank Data Corporation - Lakewood, CO USA

Knut Stolze<stolze@de.ibm.com11/23/06 1:00 AM >>>

>Frank Swarbrick wrote:
>

if

>
>The main thing to recognize here is that the result is a string and not a
>date value. An empty string '' is not a valid date in the first place.

Yes, but within a COBOL program that seems to me to be perfectly
acceptable.

case

simple,

>
>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

Frank Swarbrick wrote:

>
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