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!!

--- "Goodbar, Loyd (AFS-Water Valley)" <LGoodbar@afs.bwauto.com> wrote:
> Dan...
>
> Thanks, Vern. The revised SQL looks like this:
>
> select
> /* first part */
> cast('AAAA' as char(4)) concat
> /* numeric part, right justify with floating left - sign */
> substr(cast(' ' as char(11)),1,11-length(strip(cast(char(-11.23)
>   as char(11)),b,' '))) concat strip(cast(char(-11.23) as
>   char(11)),b,' ') concat
> /* text */
> cast('Some text' as char(20)) concat
> /* CRLF */
> x'0d25'
> from QSYS2/QSQPTABL
>
> With a result of
>
> AAAA     -11.23Some text           <crlf>
>
> Where "11" is the assumed field length based on the file sample, and
> "-11.23" is the field name for the numeric data.
>
> If the file is similar to the sample provided, you can do this with
> RUNSQLSTM or the QM Query equivalent, then do a CPYTOSTMF.
>
> HTH,
> Loyd