Need SQL help - IBM AS400

This is a discussion on Need SQL help - IBM AS400 ; select distinct(pid), ababtx from jkingsley/pidgba How can I get this via STRSQL to an outfile Also, I have created a QSQLSRC file, what is the syntax in the source member to run this. I have this and it does not ...

+ Reply to Thread
Results 1 to 5 of 5

Thread: Need SQL help

  1. Need SQL help

    select distinct(pid), ababtx from jkingsley/pidgba

    How can I get this via STRSQL to an outfile

    Also, I have created a QSQLSRC file, what is the syntax in the source
    member to run this. I have this and it does not work.

    select distinct(pid), ababtx from jkingsley/pidgba


  2. Re: Need SQL help

    Other than CREATE TABLE followed by INSERT INTO SELECT FROM as
    effective output file, the F13=Services and then "1. Change session
    attributes" allows specifying [SELECT output=3] as an output file. The
    outfile feature is intended for ad-hoc; i.e. /real/ files should be
    created from source, with CREATE TABLE [or CRTPF from DDS source].
    Although the 4=Save session in source file [by default] directs to
    the file name QSQLSRC, there is no specific meaning for this file by
    name, nor is there any source type identity for that output. The save
    to source option is just to enable saving the tested statements, from
    which they can then be copied; mostly for green screen editors like
    STRSEU & EDTF. Generally a client copy/paste would suffice. The output
    source is not directly executable, and so either specific statements
    must be chosen for copy or the usable statements left after purging the
    unwanted statements. For use in a QMQRY source, then just one statement
    is used; CRTQMQRY then STRQMQRY. If for use in a RUNSQLSTM source,
    follow each statement with a semicolon to make a script of statements
    [SELECT not supported; use QMQRY, REXX, or another HLL with SQL support
    instead].
    Thus as the single SELECT statement given, create a *QMQRY and then
    run [start] that QM query; optionally create a *QMFORM for its
    reporting. If output is to an OUTFILE, then specify that on STRQMQRY.

    Regards, Chuck
    --
    All comments provided "as is" with no warranties of any kind
    whatsoever and may not represent positions, strategies, nor views of my
    employer

    jacko wrote:
    > select distinct(pid), ababtx from jkingsley/pidgba
    >
    > How can I get this via STRSQL to an outfile
    >
    > Also, I have created a QSQLSRC file, what is the syntax in the source
    > member to run this. I have this and it does not work.
    >
    > select distinct(pid), ababtx from jkingsley/pidgba


  3. Re: Need SQL help

    create table library/tablename as
    (select distinct pid, ababtx from jkingsley/pidgba)
    with data

    If you want to run from a QSQLSRC File you can use the RUNSQLSTM
    command. Syntax is the same as regular interactive SQL but with a
    semi-colon separating each statement.

    Hope this helps

    Chris


    On Aug 8, 10:20 am, jacko wrote:
    > select distinct(pid), ababtx from jkingsley/pidgba
    >
    > How can I get this via STRSQL to an outfile
    >
    > Also, I have created a QSQLSRC file, what is the syntax in the source
    > member to run this. I have this and it does not work.
    >
    > select distinct(pid), ababtx from jkingsley/pidgba




  4. Re: Need SQL help

    I was a bit more restrictive than intended in describing other than
    *OUTFILE support for generating an output file; having omitted more
    ad-hoc usages. More like:
    CREATE TABLE AS SELECT, and DECLARE GLOBAL TEMPORARY TABLE AS or
    CREATE TABLE [LIKE] followed by INSERT...

    Regards, Chuck
    --
    All comments provided "as is" with no warranties of any kind
    whatsoever and may not represent positions, strategies, nor views of my
    employer

    CRPence wrote:
    > Other than CREATE TABLE followed by INSERT INTO SELECT FROM as
    > effective output file, the F13=Services and then "1. Change session
    > attributes" allows specifying [SELECT output=3] as an output file. The
    > outfile feature is intended for ad-hoc; i.e. /real/ files should be
    > created from source, with CREATE TABLE [or CRTPF from DDS source].
    > <>
    > Regards, Chuck


  5. Re: Need SQL help

    On 8 Aug, 13:20, jacko wrote:
    > select distinct(pid), ababtx from jkingsley/pidgba
    >
    > How can I get this via STRSQL to an outfile
    >
    > Also, I have created a QSQLSRC file, what is the syntax in the source
    > member to run this. I have this and it does not work.
    >
    > select distinct(pid), ababtx from jkingsley/pidgba


    In V5R3 and later:
    CREATE TABLE lib/table AS (select distinct(pid), ababtx from jkingsley/
    pidgba)
    WITH DATA


+ Reply to Thread