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

[Vern Hamberg <vhamberg@xxxxxxxxxxxxxxxxxxxxxxxxx>]

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