SQL Question - IBM AS400

This is a discussion on SQL Question - IBM AS400 ; I have a query where I am joining 3 files. One of which I only need to access the first record which matches the join by key from file 1. Does anyone know how I can accomplish this? If I ...

+ Reply to Thread
Results 1 to 8 of 8

Thread: SQL Question

  1. SQL Question

    I have a query where I am joining 3 files. One of which I only need to
    access the first record which matches the join by key from file 1.

    Does anyone know how I can accomplish this? If I do inner join or right
    join, I get all the matching records, when I only want the first
    record that matches file 1 from file 3.

    --
    Posted via a free Usenet account from http://www.teranews.com


  2. Re: SQL Question

    The answer is of course, to select the specific key.data you want;
    i.e. specify on the WHERE clause, the specific key value of interest.
    But of course such a simple answer probably means the question is not
    formed in a manner that expresses the true difficulty.
    By 'first', if meaning any record or perhaps by some specific order,
    to just get a match or the first ordered match, then something like the
    following which returns the /first/ matching row irrespective of order:
    select * from qsys.qadbxref as fil
    right outer join qsys.qadbifld as fld
    on fil.dbxfil = fld.dbifil
    and fil.dbxlib = fld.dbilib
    fetch first row only for fetch only

    Regards, Chuck
    -- All comments provided "as is" with no warranties of any kind whatsoever.

    Thomas Hauber wrote:
    > I have a query where I am joining 3 files. One of which I only need to
    > access the first record which matches the join by key from file 1.
    >
    > Does anyone know how I can accomplish this? If I do inner join or right
    > join, I get all the matching records, when I only want the first record
    > that matches file 1 from file 3.
    >


  3. Re: SQL Question

    CRPence wrote:
    > The answer is of course, to select the specific key.data you want;
    > i.e. specify on the WHERE clause, the specific key value of interest.
    > But of course such a simple answer probably means the question is not
    > formed in a manner that expresses the true difficulty.
    > By 'first', if meaning any record or perhaps by some specific order,
    > to just get a match or the first ordered match, then something like the
    > following which returns the /first/ matching row irrespective of order:
    > select * from qsys.qadbxref as fil
    > right outer join qsys.qadbifld as fld
    > on fil.dbxfil = fld.dbifil
    > and fil.dbxlib = fld.dbilib
    > fetch first row only for fetch only
    >
    > Regards, Chuck
    > -- All comments provided "as is" with no warranties of any kind whatsoever.
    >
    > Thomas Hauber wrote:
    >> I have a query where I am joining 3 files. One of which I only need
    >> to access the first record which matches the join by key from file 1.
    >>
    >> Does anyone know how I can accomplish this? If I do inner join or
    >> right join, I get all the matching records, when I only want the
    >> first record that matches file 1 from file 3.
    >>

    Wouldn't this return only one row, period? I had looked at the fetch
    statement in the manual and that is how I interpreted that.

    --
    Posted via a free Usenet account from http://www.teranews.com


  4. Re: SQL Question

    Yes, the "fetch first row only" clause will return only one row.
    Sorry, but 'first' usually implies only. The new question has me
    returning to the original inquiry and my comment that it seems not to
    have been "formed in a manner that expresses the true difficulty" in
    what is being asked.
    As the expression goes... "A picture is worth a thousand words."
    Seeing a simple 'picture' of the tables & data, and the desired output,
    will best assist others to understand what you want to accomplish. The
    data should include rows that problematic; i.e. those rows which make
    the query. Of course not a real 'picture', just some ascii text that is
    either a script of create and insert, or a visual of the data as it
    might be presented in a text report.
    For example: Assuming three tables of definition (K INT, C CHAR),
    with data as shown in the three side-by-side reports [below] showing the
    data in those files... What does the intended query need to show as a
    report?

    _K_ _C_ _K_ _C_ _K_ _C_
    1 x 0 y 0 z
    2 x 2 y 1 z
    5 x 5 y 3 z
    6 x 8 y 5 z
    7 x 9 y 7 z
    8 z

    Perhaps the definition & data above are not well suited for your
    concern, and an alternate should be given. Those are just an example of
    how the inquiry might better be articulated, limiting the requirement to
    infer from words, what is desired.

    Regards, Chuck
    -- All comments provided "as is" with no warranties of any kind whatsoever.

    Thomas Hauber wrote:
    > CRPence wrote:
    >> <> SQL join using FETCH FIRST ROW ONLY
    >>

    > Wouldn't this return only one row, period? I had looked at the fetch
    > statement in the manual and that is how I interpreted that.


  5. Re: SQL Question

    Hi Thomas,

    you may try the following solution, but be aware the performance might
    be awfull because in either way a table scan must be performed.

    With x as (Select min(rrn(MyTable)) as MinX, Key1, Key2 from MyTable
    where ... Group By Key1, Key2),
    y as (Select z.* from x join MyTable z on Minx = rrn(z))
    Select ...
    From y join ...
    Where ...

    (no guarantee for the statemente, I have currently no access to an
    iSeries)

    Birgitta


  6. Re: SQL Question

    CRPence wrote:
    > Yes, the "fetch first row only" clause will return only one row.
    > Sorry, but 'first' usually implies only. The new question has me
    > returning to the original inquiry and my comment that it seems not to
    > have been "formed in a manner that expresses the true difficulty" in
    > what is being asked.
    > As the expression goes... "A picture is worth a thousand words."
    > Seeing a simple 'picture' of the tables & data, and the desired output,
    > will best assist others to understand what you want to accomplish. The
    > data should include rows that problematic; i.e. those rows which make
    > the query. Of course not a real 'picture', just some ascii text that is
    > either a script of create and insert, or a visual of the data as it
    > might be presented in a text report.
    > For example: Assuming three tables of definition (K INT, C CHAR), with
    > data as shown in the three side-by-side reports [below] showing the data
    > in those files... What does the intended query need to show as a report?
    >
    > _K_ _C_ _K_ _C_ _K_ _C_
    > 1 x 0 y 0 z
    > 2 x 2 y 1 z
    > 5 x 5 y 3 z
    > 6 x 8 y 5 z
    > 7 x 9 y 7 z
    > 8 z
    >
    > Perhaps the definition & data above are not well suited for your
    > concern, and an alternate should be given. Those are just an example of
    > how the inquiry might better be articulated, limiting the requirement to
    > infer from words, what is desired.
    >
    > Regards, Chuck
    > -- All comments provided "as is" with no warranties of any kind whatsoever.
    >
    > Thomas Hauber wrote:
    >> CRPence wrote:
    >>> <> SQL join using FETCH FIRST ROW ONLY
    >>>

    >> Wouldn't this return only one row, period? I had looked at the fetch
    >> statement in the manual and that is how I interpreted that.

    File 2 really doesn't come into my question, since I am doing an inner
    join on that with file 1. Just to get item descriptions.

    The real crux of the matter is the join between file 1 (PO detail) and
    file 3(Updated PO line delivery date). When this particular system has
    changes made to PO detail line delivery dates. It basically tracks with
    multiple records what those dates are. So for instance you have
    modified a PO line, three times. Only the most current record (via
    modified time/date fields) is the one with the date you need.

    To lay it out visually:


    PO Detail (file 1)

    PO # Line # DATA
    12345 1 xxxxxxxxxx
    12346 1 xxxxxxxxxx
    12346 2 xxxxxxxxxx



    PO Current Date Due (file 3)

    PO # Line # Date Mod Time Mod Data(current due date)
    12345 1 20070601 120000 yyyyyyyyyyyyy
    12346 2 20070611 120000 yyyyyyyyyyyyy
    12346 2 20070601 120000 yyyyyyyyyyyyy
    12346 2 20070601 110000 yyyyyyyyyyyyy


    As you can see there might be no records matching, 1 record matching or
    multiple records matching. I can solve scenario 1 and 2 by using the
    right outer join. The difficulty arises in scenario 3 where you have
    multiple records in file 3 (PO 12346, line 2), but only the most current
    by date/time is the one you want returned.

    Doing this with RPG is trivial, doing it with SQL (for a client access
    file transfer) is proving very difficult.

  7. Re: SQL Question

    The following query returns a row for every database [and DDM] file
    on the system, presenting the 'last' field in the format of each file.
    For some non-relational files there is a one to none relationship, so
    the 'last' field information is the database NULL value.

    select dbxlib,dbxfil,dbxrel,dbxnfl,dbifld,dbipos
    from qsys.qadbxref fil
    left outer join qsys.qadbifld fld
    ON fil.dbxlib=fld.dbilib
    and fil.dbxfil=fld.dbifil
    where fld.dbipos=
    ( select max(dbipos) from qsys.qadbifld pos
    where fld.dbilib=pos.dbilib
    and fld.dbifil=pos.dbifil
    and fld.dbifmp=pos.dbifmp
    and fld.dbifmt=pos.dbifmt )
    or fld.dbipos is null
    -- QADBIFLD key: DBILIB,DBIFIL,DBIFMP,DBIFMT,DBIPOS
    -- QADBXREF key: DBXLIB,DBXFIL

    In the above query, both the files and data are chosen because those
    files are on every system and the data being file to field relationships
    should be somewhat intuitive for anyone doing SQL. The dbxrel is
    included only to help clarify non-relational files, for which that row
    will include the NULL value [when not a multi-format logical], and
    dbxnfl is included only because it will match dbipos for valid results.
    Note: That query will generate multiple rows for multi-format logical
    files.
    Caveat: The files named in the query are restricted to users with
    *ALLOBJ special authority -- QADBIFLD can refer instead to QADBILLB and
    QADBXREF to QADBXATR, but then the new/SQL query engine [SQE] will not
    be utilized.

    For the maximum date query, given the date+time are numerics, the
    best selection is probably by
    WHERE file3.DateMod*1000000 + file3.TimeMod =
    ( SELECT MAX( file3ss.DateMod*1000000 + file3ss.TimeMod )
    FROM FILE3 file3ss
    WHERE file3.PO#=file3ss.PO#
    and file3.LINE#=file3ss.LINE# )
    -- FILE3 and FILE1 should both have primary keys PO#,LINE#
    -- If the dates are character, then CONCAT expression

    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

    Thomas Hauber wrote:
    <>
    > File 2 really doesn't come into my question, since I am doing an inner
    > join on that with file 1. Just to get item descriptions.
    >
    > The real crux of the matter is the join between file 1 (PO detail) and
    > file 3(Updated PO line delivery date). When this particular system has
    > changes made to PO detail line delivery dates. It basically tracks with
    > multiple records what those dates are. So for instance you have
    > modified a PO line, three times. Only the most current record (via
    > modified time/date fields) is the one with the date you need.
    >
    > To lay it out visually:
    >
    > PO Detail (file 1)
    >
    > PO # Line # DATA
    > 12345 1 xxxxxxxxxx
    > 12346 1 xxxxxxxxxx
    > 12346 2 xxxxxxxxxx
    >
    > PO Current Date Due (file 3)
    >
    > PO # Line # Date Mod Time Mod Data(current due date)
    > 12345 1 20070601 120000 yyyyyyyyyyyyy
    > 12346 2 20070611 120000 yyyyyyyyyyyyy
    > 12346 2 20070601 120000 yyyyyyyyyyyyy
    > 12346 2 20070601 110000 yyyyyyyyyyyyy
    >
    > As you can see there might be no records matching, 1 record matching or
    > multiple records matching. I can solve scenario 1 and 2 by using the
    > right outer join. The difficulty arises in scenario 3 where you have
    > multiple records in file 3 (PO 12346, line 2), but only the most current
    > by date/time is the one you want returned.
    >
    > Doing this with RPG is trivial, doing it with SQL (for a client access
    > file transfer) is proving very difficult.


  8. Re: SQL Question

    On Jun 14, 9:42 am, Thomas Hauber wrote:
    > I have a query where I am joining 3 files. One of which I only need to
    > access the first record which matches the join by key from file 1.
    >
    > Does anyone know how I can accomplish this? If I do inner join or right
    > join, I get all the matching records, when I only want the first
    > record that matches file 1 from file 3.


    I thought I sent a reply to this, but don't see it (in Google
    Groups). This will work as long as you don't need any columns from
    file3 in your selected column list.

    select [column list]
    from file1 f1
    join file2 f2
    on f1.col1 = f2.col1 and [other join columns]
    where [any 'where' predicates]
    and exists
    (select *
    from file3 f3
    where f3.col1 = f1.col1 and [other join columns] )


+ Reply to Thread