Justin - I'm assuming you have figured out by now the "easy answer" is No. CPYF can't do it automagically via a parm or something.
I am assuming you are copying a file with a single field (aka flat file) to a file with fields defined and using CPYF *NOCHK.
Maybe someone else can jump in here with an answer to this but would a before trigger on the to file be an option so it replaces the blank with null before the write? I'm not confident if a trigger will work that way. It's been too long since I messed with them. If the trigger will work, that you keep you from substringing your fields to build a fake record out of your flat data just so you can do an SQL insert.
-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Rob Berendt
Sent: Friday, February 19, 2021 2:11 PM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: RE: [EXTERNAL] Re: CPYF: blanks -> Null
Yes you can, but then there's the whole "not easily".
Perhaps an intermediate file where the date is still character or some such thing. Then you can either edit that in place with a simple update myfile set chardate null where chardate = ' '; or some such statement.
Or, instead of editing that file, use an sql feed into the final file(s) with editing on that stored procedure.
Rob Berendt
--
IBM Certified System Administrator - IBM i 6.1 Group Dekko Dept 1600 Mail to: 7310 Innovation Blvd, Suite 104
Ft. Wayne, IN 46818
Ship to: 7310 Innovation Blvd, Dock 9C
Ft. Wayne, IN 46818
http://www.dekko.com
-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Alan Shore via MIDRANGE-L
Sent: Friday, February 19, 2021 1:40 PM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Cc: Alan Shore <ashore@xxxxxxxx>
Subject: RE: [EXTERNAL] Re: CPYF: blanks -> Null
CAUTION: This email originated from outside of the organization. Do not click links or open attachments unless you recognize the sender and know the content is safe.
Hi Justin
Not too sure what you mean by both files are dynamic
I am assuming that when you say the from file is a flat file, Im assuming that you mean its just a string of data
You can still use SQL against a flat file (string of data)
Alan Shore
Solutions Architect
IT Supply Chain Execution
[cid:image001.png@01D706C4.B05C61C0]
60 Orville Drive
Bohemia, NY 11716
Phone [O] : (631) 200-5019
Phone [C] : (631) 880-8640
E-mail : ASHORE@xxxxxxxxxxxxxxxxxxxx
'If you're going through hell, keep going.'
Winston Churchill
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxxxxxxxx] On Behalf Of Justin Taylor
Sent: Friday, February 19, 2021 1:29 PM
To: MIDRANGE-L <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: [EXTERNAL] Re: CPYF: blanks -> Null
Not easily. The from & to files are dynamic, and the from file is a flat
file.
date: Fri, 19 Feb 2021 15:46:47 +0000
from: Alan Shore via MIDRANGE-L <midrange-l@xxxxxxxxxxxxxxxxxx<mailto:midrange-l@xxxxxxxxxxxxxxxxxx>>
subject: RE: [EXTERNAL] CPYF: blanks -> Null
Hi Justin
Can you not use SQL?
Not knowing how many fields we are talking about on the file - lets say 5,
and the 4th one is the date
Insert into COPYTOFILE
(Select Field1, Field2, Field3, current_date, Field5 from COPYFROM)
Alan Shore
Solutions Architect
IT Supply Chain Execution
[cid:image003.png@01D706AC.84698B50]
60 Orville Drive
Bohemia, NY 11716
Phone [O] : (631) 200-5019
Phone [C] : (631) 880-8640
E-mail : ASHORE@xxxxxxxxxxxxxxxxxxxx<mailto:ASHORE@xxxxxxxxxxxxxxxxxxxx>
'If you're going through hell, keep going.'
Winston Churchill
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx<mailto:MIDRANGE-L@xxxxxxxxxxxxxxxxxx>
To subscribe, unsubscribe, or change list options,
visit:
https://lists.midrange.com/mailman/listinfo/midrange-l<
https://lists.midrange.com/mailman/listinfo/midrange-l>
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx<mailto:MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx>
Before posting, please take a moment to review the archives
at
https://archive.midrange.com/midrange-l<
https://archive.midrange.com/midrange-l>.
Please contact support@xxxxxxxxxxxxxxxxxxxx<mailto:support@xxxxxxxxxxxxxxxxxxxx> for any subscription related questions.
Help support midrange.com by shopping at amazon.com with our affiliate link:
https://amazon.midrange.com<
https://amazon.midrange.com>
The Nature’s Bounty Co. is now The Bountiful Company! Our email address will be changing from @nbty.com to @bountifulcompany.com. Please update your email address book so that important communications are not interrupted.
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit:
https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at
https://archive.midrange.com/midrange-l.
Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related questions.
Help support midrange.com by shopping at amazon.com with our affiliate link:
https://amazon.midrange.com
midrange.com
