That worked! It didn't return the null indicator. Now how do I use the
function and do the original concatenation without the hyphens?



Mary A. Lewis
Office of Information Services
NYS Division of Parole




"Darrin Preble "
<Darrin.[Email Address Removed]
tadirect.com> To
[Email address protected]
05/19/2008 09:23 cc
PM
Subject
RE: DSNTIAUL Question









**A LazyDBA.com subscriber has responded to your lazydba.com post**
**LazyDBA.com mail shield has forwarded you this email,
**and removed any attachments, and kept your email address secret
**from this person, and any viruses/trojans.
**If you reply to this email, the person will see your email address as
normal
**Anything below this line is the original email text



Does a null column function work ? Value(cms_cl_dt,'9999-12-31)
darrin

-----Original Message-----
From: mlewis
[Email address protected]
Sent: Monday, May 19, 2008 1:31 PM
To: LazyDBA Discussion
Subject: DSNTIAUL Question



I'm using DB2 v8 on z/OS mainframe. I'm trying to unload columns from a
table to a pre-existing 23 character disk dataset.
I'm running DSNTIAUL with PARMS('SQL'):


SELECT
CMS_DIN,
SUBSTR(CHAR(CMS_CL_DT),1,4) CONCAT
SUBSTR(CHAR(CMS_CL_DT),6,2) CONCAT
SUBSTR(CHAR(CMS_CL_DT),9,2),
CMS_CL_RSN,
CHAR(' ')
FROM TBCMS_MSTR
WHERE CMS_RECORD_TY = '9'
AND (SUBSTR(CMS_DIN,1,2) >= '00')
AND (SUBSTR(CMS_DIN,3,1) BETWEEN 'A' AND 'Z')
AND CMS_REL_TY NOT IN ('2','3') ;

CMS_CL_DT is defined as DATE and is nullable. The null indicator is also
unloaded following the date field.
Because of the WHERE clause, I will never select a row with a null date
value.
How can unload just the date portion I'm specifying in the SELECT and
NOT get the null indicator?
Is there a way to strip off the null indicator and not unload it?


Thanks for your help.

Mary A. Lewis
Office of Information Services
NYS Division of Parole






IMPORTANT NOTICE: This e-mail is meant only for the use of the intended
recipient(s). This communication and any attachments may contain
confidential information which is legally privileged, confidential or
otherwise protected by law. If you received this e-mail in error or
from someone who was not authorized to send it to you, you are strictly
prohibited from reviewing, using, disseminating, distributing or copying
the e-mail or any attachment. PLEASE NOTIFY THE SENDER IMMEDIATELY OF
THE ERROR BY A REPLY E-MAIL AND DELETE THIS MESSAGE FROM YOUR SYSTEM.
Thank you for your cooperation.


---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY To post a
dba job: http://jobs.lazydba.com To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html










IMPORTANT NOTICE: This e-mail is meant only for the use of the intended recipient(s). This communication and any attachments may contain confidential information which is legally privileged, confidential or otherwise protected by law. If you received this e-mail in error or from someone who was not authorized to send it to you, you are strictly prohibited from reviewing, using, disseminating, distributing or copying the e-mail or any attachment. PLEASE NOTIFY THE SENDER IMMEDIATELY OF THE ERROR BY A REPLY E-MAIL AND DELETE THIS MESSAGE FROM YOUR SYSTEM. Thank you for your cooperation.

DB2 & UDB email list listserv db2-l LazyDBA home page