RE: AS/400 app FTPs file to IFS???

Thanks for jumping in, Vern, I was in a meeting for the past hour....

I do think that the below can be used with RUNSQLSTM, if you do the
following:

CRTPF FILE(MYFILE) RCDLEN(82)

In this example, the real record length is 80, with 2 positions for CRLF.

Then, do this in the RUNSQLSTM:

Insert into MYFILE (MYFILE)
(
select
/* first part */
AAA1 concat
/* numeric part, right justify with floating left - sign */
/* this is the leading blanks - 10 because 8,2 */
/* can have a decimal AND an optional sign */
substr(cast(' ' as char(10)),1,
   10 - length(strip(char(BBB2), b, ' '))) concat
/* the number itself with decimal, lefthand sign, */
/* and denuded of blanks */
  strip(char(BBB2),b,' ') concat
/* text */
CCC3 concat
/* CRLF */
x'0d25'
from yourlib/yourfile
)

Then, do a CPYTOSTMF, specifying ENDLINFMT(*FIXED).

Loyd

-----Original Message-----
From: Vern Hamberg [mailto:vhamberg@centerfieldtechnology.com]
Sent: Wednesday, November 06, 2002 3:32 PM
To: midrange-l@midrange.com
Subject: RE: AS/400 app FTPs file to IFS???


Dan - response inline

At 12:19 PM 11/6/2002 -0800, you wrote:
>Loyd, Vern,
>
>I'm sure this may be syntax you're familiar with, but as an SQL
>neophyte, I have no idea what's going on here.

I don't blame you - some of this is pretty to all of us.

>1) Looking at the SQL reference, the syntax for CAST is not quite
>concise.  Is your usage of it used to force "columnized" output?

Yes, basically. E.g., cast(' ' as char(11)) turns that single blank into an
11-character string of spaces. I used to have to do this with a hard-coded
11-space literal, but this is more maintainable, I think. You know how long
a thing you're working with.

>2) Your SQL example uses literals.  Can you restate the example
>    using field names from a database file?  For this example, use:
>       Field: AAA1  defined as   4A
>       Field: BBB2  defined as 8,2S
>       Field: CCC3  defined as  20A

Actually, I don't think SQL strips the trailing blanks of fields in
concatenations, so you probably don't need all the casts on the field names
(right, Loyd?). So maybe this'd work (-- is SQL for comment in QMQRY source)

select
-- first part
AAA1 concat
-- numeric part, right justify with floating left - sign
-- this is the leading blanks - 10 because 8,2
-- can have a decimal AND an optional sign
substr(cast(' ' as char(10)),1,
   10 - length(strip(char(BBB2), b, ' '))) concat
-- the number itself with decimal, lefthand sign,
-- and denuded of blanks
  strip(char(BBB2),b,' ') concat
-- text
CCC3 concat
-- CRLF
x'0d25'
from yourlib/yourfile

Result is

asb   -1234.30This is text          <crlf>

from a file with field values "asb ", -1234.3, "This is text       "

>3) Using RUNSQLSTM, how do I specify to which file the output should
>go?

You can't with RUNSQLSTM, but you can with STRQMQRY, the QM equivalent Loyd
mentioned.

Once you have the output file, do a CPY to IFS with DTAFMT(*BINARY) - you
have no choice, then another CPY that changes it to ASCII.

COOL!!