SQL query design questions - IBM AS400

This is a discussion on SQL query design questions - IBM AS400 ; I have a requirement to produce a report on demand. This report will contain data from 3 or 4 different (but similar) files, and users will need to be able to supply a couple of selection criteria (such as a ...

+ Reply to Thread
Results 1 to 12 of 12

Thread: SQL query design questions

  1. SQL query design questions

    I have a requirement to produce a report on demand. This report will
    contain data from 3 or 4 different (but similar) files, and users will
    need to be able to supply a couple of selection criteria (such as a
    date) to determine which records should be returned from these files.
    (Different users could be generating this report simultaneously, with
    different criteria.)

    Currently the users want to see this report on-screen, but I think
    there is a reasonable chance that at some future point they will want
    the same report generated as a print; so whilst I could code this in a
    combined program (to capture the selection criteria, collect the data,
    and display the results), I'm thinking it might be best to separate out
    the data collection phase (which could then be reused if a print report
    is required).

    Although I have done some simple SQL before, neither of the two
    candidate solutions I have come up with are in areas I have dealt with
    before:

    Solution 1) Use DECLARE GLOBAL TEMPORARY TABLE in the data collection
    program, and INSERT the SELECTed records. The report program(s) would
    then SELECT from this temporary table (possibly using FETCH n ROWS for
    the on-screen report). The temporary table would be defined in a source
    member accessed via INCLUDEs in the data collection and the report
    programs.

    Solution 2) Create the data collection program as a Stored Procedure,
    and use SET RESULT SETS to pass the data to the report program(s).


    All comments and suggestions on which is the better method (or if there
    are other good methods) would be gratefully received. (Our current
    environment is OPM COBOL and v5r2, I'm not interested in an ILE
    solution, or one requiring a newer version of OS/400.)


    Thanks.


  2. Re: SQL query design questions


    "walker.l2" wrote in message
    news:1166030410.261631.155960@l12g2000cwl.googlegr oups.com...
    >I have a requirement to produce a report on demand. This report will
    > contain data from 3 or 4 different (but similar) files, and users will
    > need to be able to supply a couple of selection criteria (such as a
    > date) to determine which records should be returned from these files.
    > (Different users could be generating this report simultaneously, with
    > different criteria.)
    >
    > Currently the users want to see this report on-screen, but I think
    > there is a reasonable chance that at some future point they will want
    > the same report generated as a print; so whilst I could code this in a
    > combined program (to capture the selection criteria, collect the data,
    > and display the results), I'm thinking it might be best to separate out
    > the data collection phase (which could then be reused if a print report
    > is required).
    >
    > Although I have done some simple SQL before, neither of the two
    > candidate solutions I have come up with are in areas I have dealt with
    > before:
    >
    > Solution 1) Use DECLARE GLOBAL TEMPORARY TABLE in the data collection
    > program, and INSERT the SELECTed records. The report program(s) would
    > then SELECT from this temporary table (possibly using FETCH n ROWS for
    > the on-screen report). The temporary table would be defined in a source
    > member accessed via INCLUDEs in the data collection and the report
    > programs.
    >
    > Solution 2) Create the data collection program as a Stored Procedure,
    > and use SET RESULT SETS to pass the data to the report program(s).
    >
    >
    > All comments and suggestions on which is the better method (or if there
    > are other good methods) would be gratefully received. (Our current
    > environment is OPM COBOL and v5r2, I'm not interested in an ILE
    > solution, or one requiring a newer version of OS/400.)
    >
    >
    > Thanks.
    >


    Have you considered building and running dynamic SQL calls in your app
    layer. This would be the most logical process with minimal complexity and
    maximum flexibility.

    RJ



  3. Re: SQL query design questions

    Can you explain further? I'm not sure what you mean. What I was trying
    to avoid was writing a program to gather the selection criteria,
    collect the data, and display the data on-screen, and a second program
    to gather the selection criteria, collect the data, and print the data
    to a spool file.
    I suppose I could write a single program to do everything necessary for
    the on-screen report, and add a PF-key option for the user to generate
    a printed version; but that would work only if the print was to be
    produced during the day, and not if it were automated for production
    during the end of day run.

    My thought was to separate this into 3 stages:
    1) Gather the criteria (program A)
    2) Collect the data (program B)
    3) Produce the report (either on-screen (program C) or spool file
    (program D))

    but maybe I'm making a mountain out of a molehill?


  4. Re: SQL query design questions


    "walker.l2" wrote in message
    news:1166094044.759271.62020@l12g2000cwl.googlegro ups.com...
    > Can you explain further? I'm not sure what you mean. What I was trying
    > to avoid was writing a program to gather the selection criteria,
    > collect the data, and display the data on-screen, and a second program
    > to gather the selection criteria, collect the data, and print the data
    > to a spool file.
    > I suppose I could write a single program to do everything necessary for
    > the on-screen report, and add a PF-key option for the user to generate
    > a printed version; but that would work only if the print was to be
    > produced during the day, and not if it were automated for production
    > during the end of day run.
    >
    > My thought was to separate this into 3 stages:
    > 1) Gather the criteria (program A)
    > 2) Collect the data (program B)
    > 3) Produce the report (either on-screen (program C) or spool file
    > (program D))
    >
    > but maybe I'm making a mountain out of a molehill?
    >


    Form your original post the source of the data seems fairly fixed so it
    would appear that only the columns required to be show and the row filter is
    all that is varying. This degree of variability is handled simply by SQL.

    You are looking at:

    SELECT {some potentially user defined column list,,,}
    FROM {known files}
    LEFT JOIN {if required - but also pre-known}
    WHERE {Some variable condition}

    You would just need to construct a string with the required Column list and
    Where clause on the fly just before the script is called from the calling
    RPG program.

    So the data retrieval component should be doable with a straight embedded
    SQL call. The presentation layer is a different kettle of fish. You'd want
    to be careful with DDS or RLE as these aren't too flexible for dynamic
    rendering. I don't know your preference but you could assemble a single
    string that is rendered as the entire row/line of the screen/report (column
    one of the SQL results starting at character position 1, column 2 at
    character position 10, etc).

    One thing we do occasionally now in similar circumstances (admittedly
    normally in SQL Server) is let the SQL environment do the concatenation type
    work. The advantage for you is the SQL environment knows the length of each
    column so the padding works quite well. In return the presentation layer
    merely gets a recordset consisting of 1 column (the derived string of
    fields). You then just dump each line on to the window subfile or write it
    to the report detail line.

    If you follow this logic then you would have:

    1 RPG program to prompt for variables, which then goes on to create the
    appropriate SQL string, execute it and load the DDS or Report Defn as
    required by simply dumping each resultset out one line at a time. The report
    MUST render with a non-proportional font (eg. Courier New) for this to work
    though.

    Hope you get some ideas from this,

    Rj.




  5. Re: SQL query design questions

    Thanks.

    I did wonder about a JOIN, but I don't think it will grab the data in
    the format I need. The same columns will be returned each time, so that
    simplifies things, but records from different files need to be shown as
    different lines on the report, not as a merged / combined line.
    (Logically I want to merge the files into a single file with a single
    format, then select out the records I need; but I only need a very few
    rows from each file, so it would seem to make sense to do the SELECT
    before the merge, rather than after it.)

    I've asked the users if they mind accessing the report through our web
    front-end, rather than through a 5250 session. If they are okay with
    that, then I don't need the subfile (phew!) as I can use a JSP instead,
    and the printing could be handled (during the day at least) simply by
    using the Print function of the browser. Of course that still leaves me
    with the dilemma as to whether to code the SQL as a series of JDBC
    SELECTs, or a Stored Procedure (which could be reused during the end of
    day)...


  6. Re: SQL query design questions


    "walker.l2" wrote in message
    news:1166108512.830822.207130@n67g2000cwd.googlegr oups.com...
    > Thanks.
    >
    > I did wonder about a JOIN, but I don't think it will grab the data in
    > the format I need. The same columns will be returned each time, so that
    > simplifies things, but records from different files need to be shown as
    > different lines on the report, not as a merged / combined line.
    > (Logically I want to merge the files into a single file with a single
    > format,


    This is exactly what a SQL resultset would net you. In effect you build a
    non relational resultset.

    Say we have a HEADER table and a DETAIL table. The result of a Join comes
    back as ...

    SELECT *
    FROM DETAIL
    INNER JOIN HEADER ON (hdField = dtlField) AND (....)
    WHERE {some variable condition)
    ORDER BY hdFld3, dtlFld2


    You'll end up with...

    dtlFld1, dtlFld2, dtlFld3, dtlFld4, .., hdFld1, hdFld2, hdFld3, ...

    You can level break on the appropriate fields as required. The fact that
    there is redundancy in the resultset is irrelevant as it is just a
    presentation transfer dataset.

    The trick is to make sure the Join is optimal if there are large tables
    involved. But surely you know pre-emptively how these tables go together.
    For instance, if the user nominates selection by date and key code 1 but not
    key code 2 then you may run the 2nd variant of the SQL query where , say,
    the join includes a third file or a subquery. Otherwise use query structure
    1.

    eg. I want all records where the header table field hdFldx must be between
    'A' and 'M' alphabetically. No other conditions.


    SELECT ...
    FROM HEADER
    INNER JOIN DETAIL ON ()
    WHERE hdFldx BETWEEN 'A ' AND 'MZZZZZ'

    The subtle change of join might be a more optimal approach.


    >then select out the records I need; but I only need a very few
    > rows from each file, so it would seem to make sense to do the SELECT
    > before the merge, rather than after it.)
    >
    > I've asked the users if they mind accessing the report through our web
    > front-end, rather than through a 5250 session. If they are okay with
    > that, then I don't need the subfile (phew!) as I can use a JSP instead,
    > and the printing could be handled (during the day at least) simply by
    > using the Print function of the browser. Of course that still leaves me
    > with the dilemma as to whether to code the SQL as a series of JDBC
    > SELECTs, or a Stored Procedure (which could be reused during the end of
    > day)...
    >


    We typically code so that the front end app layer is just piping between the
    database and the rendered result. Let the SQL Server do the hard work - it's
    better at it and has such a simple yet powerful syntax.


    Rj.



  7. Re: SQL query design questions

    > This is exactly what a SQL resultset would net you. In effect you build a
    > non relational resultset.
    >
    > Say we have a HEADER table and a DETAIL table. The result of a Join comes
    > back as ...
    >
    > SELECT *
    > FROM DETAIL
    > INNER JOIN HEADER ON (hdField = dtlField) AND (....)
    > WHERE {some variable condition)
    > ORDER BY hdFld3, dtlFld2
    >
    >
    > You'll end up with...
    >
    > dtlFld1, dtlFld2, dtlFld3, dtlFld4, .., hdFld1, hdFld2, hdFld3, ...
    >

    Again, thanks. But I think that's exactly what I don't want. I have 3
    or 4 detail files (with similar but different formats) that contain
    unrelated records. Each row of the report will contain data from only 1
    of the detail files, so the report would look something like this:

    file1fieldA, file1fieldB, file1fieldC, file1fieldD
    file1fieldA, file1fieldB, file1fieldC, file1fieldD
    file2fieldA, file2fieldB, file2fieldD, file2fieldE
    file2fieldA, file2fieldB, file2fieldD, file2fieldE
    file2fieldA, file2fieldB, file2fieldD, file2fieldE
    file3fieldA, file3fieldB, file3fieldC, file3fieldD
    file3fieldA, file3fieldB, file3fieldC, file3fieldD
    file4fieldA, file4fieldB, file4fieldC, file4fieldG

    The layouts of the details files are sufficiently similar that I should
    be able to co-erce the values into combined table from which I can
    build the report, so I thought something like this would do the trick:

    CREATE TABLE newtable...

    INSERT INTO newtable VALUES (SELECT fieldA, fieldB, fieldC, fieldD FROM
    file1 WHERE fieldX=somevalue AND fieldY=anothervalue)
    INSERT INTO newtable VALUES (SELECT fieldA, fieldB, fieldD, fieldE FROM
    file2 WHERE fieldV=somevalue AND fieldW=anothervalue)
    INSERT INTO newtable VALUES (SELECT fieldA, fieldB, fieldC, fieldD FROM
    file3 WHERE fieldV=somevalue AND fieldY=anothervalue)
    INSERT INTO newtable VALUES (SELECT fieldA, fieldB, fieldC, fieldG FROM
    file4 WHERE fieldX=somevalue AND fieldY=anothervalue)

    with casts / conversions where necessary

    then

    SELECT * FROM newtable

    as the input to the on-screen / print reports. That's certainly the
    approach I'd take in Java (except that the rows in newtable would be
    replaced by elements in a Java Collection, so there would be no need
    for a temporary table).

    But since my SQL knowledge is basic (or intermediate at best), and the
    report might still need to be an OPM COBOL generated 5250 screen or
    spool file, I wondered if there were better ways of going about this.
    If there is a way to SELECT all the data in one go (maybe using a more
    complex JOIN than I would normally use), then that might be a
    possibility - I could either code the data collection as a separate
    program / stored procedure, or extract the SQL statement that collects
    the data into a separate member, and copy that into the source of the
    on-screen and print reports.


  8. Re: SQL query design questions


    "walker.l2" wrote in message
    news:1166180075.745182.129310@16g2000cwy.googlegro ups.com...
    >
    > CREATE TABLE newtable...
    >
    > INSERT INTO newtable VALUES (SELECT fieldA, fieldB, fieldC, fieldD FROM
    > file1 WHERE fieldX=somevalue AND fieldY=anothervalue)
    > INSERT INTO newtable VALUES (SELECT fieldA, fieldB, fieldD, fieldE FROM
    > file2 WHERE fieldV=somevalue AND fieldW=anothervalue)
    > INSERT INTO newtable VALUES (SELECT fieldA, fieldB, fieldC, fieldD FROM
    > file3 WHERE fieldV=somevalue AND fieldY=anothervalue)
    > INSERT INTO newtable VALUES (SELECT fieldA, fieldB, fieldC, fieldG FROM
    > file4 WHERE fieldX=somevalue AND fieldY=anothervalue)
    >
    > with casts / conversions where necessary
    >
    > then
    >
    > SELECT * FROM newtable
    >



    Maybe I could help with this one then. Use the UNION command between your
    individual select statements and there is no need for the Temporary Table.

    For example,

    SELECT fieldA, fieldB, fieldC, fieldD
    FROM file1
    WHERE fieldX=somevalue AND fieldY=anothervalue

    UNION

    SELECT fieldA, fieldB, fieldD, fieldE
    FROM file2
    WHERE fieldV=somevalue AND fieldW=anothervalue

    UNION

    SELECT fieldA, fieldB, fieldC, fieldD
    FROM file3
    WHERE fieldV=somevalue AND fieldY=anothervalue

    UNION

    SELECT fieldA, fieldB, fieldC, fieldG
    FROM file4
    WHERE fieldX=somevalue AND fieldY=anothervalue



    The field names on each sub-select MUST be renamed identically. Also check
    the UNION ALL command for extended functionality.


    Does this help?



  9. Re: SQL query design questions

    Thanks a lot! I'd forgotten all about UNION (I haven't used it since I
    first learnt SQL - nearly 10 years ago). That might well do the trick.
    Knowing my luck, the fields will not have identical names, but maybe I
    can get round this by an ALIAS or logical files over the top...


  10. Re: SQL query design questions

    Yep, UNION did the trick. ((file1 U file2) U (file3 JOIN file4)) got me
    the records I needed.

    Now I just have to decide where to put the SQL (embedded in COBOL, as a
    Stored Procedure, or a JDBC call). The user has agreed that either a
    5250 screen or a webpage would be acceptable, so I think I'll use a JSP
    (with a Servlet for control, and a plain Java backend to do the JDBC
    stuff) - that'll be so much quicker and easier than messing around with
    a subfile.


  11. Re: SQL query design questions


    "walker.l2" wrote in message
    news:1166203459.451630.287280@79g2000cws.googlegro ups.com...
    > Yep, UNION did the trick. ((file1 U file2) U (file3 JOIN file4)) got me
    > the records I needed.
    >
    > Now I just have to decide where to put the SQL (embedded in COBOL, as a
    > Stored Procedure, or a JDBC call). The user has agreed that either a
    > 5250 screen or a webpage would be acceptable, so I think I'll use a JSP
    > (with a Servlet for control, and a plain Java backend to do the JDBC
    > stuff) - that'll be so much quicker and easier than messing around with
    > a subfile.
    >


    Straight through JDBC. Why complicate the issue with an interim layer.
    Anything manipulation you need to do in COBOL you could do in a JSP (do it
    through a Bean if you are into a more "correct" approach).

    Rj.



  12. Re: SQL query design questions

    The reason for considering the interim layer was that it would enable
    me to produce a printed version of the report as part of the normal End
    of Day run. (We don't run Java out of office hours due to support
    issues.) But printing the display of the browser will probably be
    sufficient for the users, so plain JDBC (in a plain Java class) feeding
    a servlet, with the servlet feeding a JSP, is still my current plan.

    Again, thanks for your help with this.


+ Reply to Thread