|
View previous topic :: View next topic
|
| Author |
Message |
muffirulz
New User
Joined: 14 Sep 2005
Posts: 74
Location: Atlanta, (USA)
|
|
|
|
Hi,
I have a table in which the end-date field is nullable. While doing a select I want to have spaces when the end-date is null. I ahve tried using the value clause but it says that it requires a valid date.
For example the below query is termed as invalid by DB2
| Code: |
SELECT A.EFF-DATE
,VALUE(A.END-DATE,' ')
FROM EMPLOYEE A
|
Instead I have to use the below query
| Code: |
SELECT A.EFF-DATE
,VALUE(A.END-DATE,'01-01-0001')
FROM EMPLOYEE A
|
Please let me know if I am missing a command or syntax to select end-date as spaces whenever it is null.
Thnx
M |
|
| Back to top |
|
 |
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
spaces will only work with CHAR datatype.
You need to cast the data-column to char for the VALUE clause to work.
even then I am not sure (I am not going to test it).
if that does not work, use a CASE statement. (But that may not work either).
it would help to know the complete sql (generally) and how you are using it.
if it is imbedded sql I would just use the program to move spaces if null indicator on. |
|
| Back to top |
|
|
sushanth bobby
Senior Member
Joined: 29 Jul 2008
Posts: 1020
Location: India
|
|
|
|
M,
Try this, make modifications according to your need
| Code: |
| SELECT FIRSTNME, COALESCE(CHAR(HIREDATE),'NO DATA') FROM BOBT1.EMPLOYEE; |
Sushanth |
|
| Back to top |
|
|
Marso
REXX Moderator
Joined: 13 Mar 2006
Posts: 1353
Location: Israel
|
|
| Back to top |
|
|
|
|
