No, that SELECT didn't work. It still retrieved the null indicator after
the date.


Mary A. Lewis
Information Technology Specialist 4
Office of Information Services
NYS Division of Parole
(518) 445-7571



"beethove "
<[Email Address Removed]
pe.gov.br> To
[Email address protected]
05/20/2008 08:13 cc
AM
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



Hi !

Perhaps it works if using this query:

SELECT * FROM (
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') ) x ;









mlewis wrote:
> 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