SQL "ORDER BY" usage - IBM AS400

This is a discussion on SQL "ORDER BY" usage - IBM AS400 ; Well, here I am again. Is the use of Order By and Group By in the same SELECT Statement not a common practice? We seem to be shooting ourselves in the foot trying to use these together. Here are two ...

+ Reply to Thread
Results 1 to 15 of 15

Thread: SQL "ORDER BY" usage

  1. SQL "ORDER BY" usage

    Well, here I am again. Is the use of Order By and Group By in the
    same SELECT Statement not a common practice? We seem to be shooting
    ourselves in the foot trying to use these together. Here are two
    examples that will explain our problem:

    select sum(hewag), char(hess) from LIUNAD01/DUHSTE where hess = 9999
    99999 group by hess order by hess
    ORDER BY column HESS or expression not in result table.

    Above Statement fails - SQL Error is included

    But this statement below works:

    select sum(hewag), char(hess) from LIUNAD01/DUHSTE where hess = 9999
    99999 group by hess
    SELECT statement run complete.

    How come the Group By works yet the Order By does not? Are we mis-
    guided in trying to use Group & Order in the same statement? Should
    we try to get all our fields to CHAR before we start processing SQL?
    Should another SQL statement be run before we try to execute the above
    that gets our data in order? Is this just AS/400 SQL that is
    complicating what we are trying to do?

    TIA
    that 1 guy


  2. Re: SQL "ORDER BY" usage

    Hi,

    look at your statement, you converted hess into a character field by
    using the scalar function CHAR. In this way you have to group and sort
    on either CHAR(hess) or on 2 (which represents the column where
    CHAR(hess) is located.

    Select Sum(xyz), Char(Hess) From MyTable
    Group By Char(Hess)
    Order By Char(Hess)

    or

    Select Sum(xyz), Char(Hess) From MyTable
    Group By 2
    Order By 2

    Group By and Order By normally cause the optimizer to generate
    temprary objects. These results may be ordered in the last step. But
    in your temporary object there will not be any column with the name
    hess.

    Birgitta



  3. Re: SQL "ORDER BY" usage

    What is the point of "ORDER BY hess" when you are only SELECTing a
    single hess value in your WHERE clause?


  4. Re: SQL "ORDER BY" usage

    I thought that might be the problem (in which case perhaps CHAR(hess)
    as aNewName ... ORDER BY aNewName might fix things?), but

    SELECT SUM(numericField1), CHAR(numericField2) FROM aFile
    GROUP BY numericField2
    ORDER BY numericField2

    works for me on v5r2.

    And it's still not clear to me why the OP would want to ORDER BY when
    he has only a single value because of the WHERE clause.


  5. Re: SQL "ORDER BY" usage

    On Feb 22, 9:36 am, "walker.l2" wrote:
    > What is the point of "ORDER BY hess" when you are only SELECTing a
    > single hess value in your WHERE clause?


    Walker I'm very sorry it was just a test example. I was trying to
    keep my result small for eaiser debugging.

    peace
    that 1 guy


  6. Re: SQL "ORDER BY" usage


    > And it's still not clear to me why the OP would want to ORDER BY when
    > he has only a single value because of the WHERE clause.


    My bad walker horrible example on my behalf. It was too mis-leading.

    that 1 guy


  7. Re: SQL "ORDER BY" usage

    Ok, I have tried your suggestions. Below are my statements and
    results:

    select sum(hewag), char(hess), char(heloc) from LIUNAD01/DUHSTE wher
    e ((hess > 316000000) and (hedatf > 20060000)) group by 2, 3 order b
    y 3
    Column HESS or expression specified in SELECT list not valid.

    The above errored out.

    Then I tried:

    select sum(hewag), char(hess), char(heloc) from LIUNAD01/DUHSTE wher
    e ((hess > 316000000) and (hedatf > 20060000)) group by hess, heloc
    order by char(heloc)

    But my heloc column started at 1047, followed by 1112, 213,
    204.....41, 81 which leads me to believe the Order by did not work.

    If I execute:

    select sum(hewag), hess, heloc from LIUNAD01/DUHSTE where ((hess > 3
    16000000) and (hedatf > 20060000)) group by hess, heloc order by heloc

    Then my heloc column's ordering is: 41, 81, 204, 213.....1047, 1112,
    which is what I desire but now my fields are not CHAR and will not
    work with the Data Structure (DS) in the ILE program.

    My data structure to be used on the FETCH looks like:

    D AllDat DS
    D Alldue 1 9 2
    D Allwag 10 18 2
    D Allduw 19 27 2
    D All01 28 77
    D All02 78 127
    D All03 128 177
    D All04 178 227
    D All05 228 277
    D All06 278 327
    D All07 328 377
    D All08 378 427
    D All09 428 477

    The numbered fields All01, All02, etc. are alpha because the incoming
    data could be numeric or alpha for these fields and is why I'm trying
    to use the CHAR function in my SELECT. Based upon the Users Dynamic
    creation of the SELECT, GROUP BY & ORDER BY, All01 could have a
    numeric or alpha placed in it.

    Thank you for replying to this obvious SQL noob's questions. Every
    time I try something that seems to be what I need another road block
    is placed in front of me and my lack of SQL knowledge just compunds
    the issue. Also, I am developing on a V5R1M0 box.


  8. Re: SQL "ORDER BY" usage

    When using CHAR to convert a numeric column into a character field,
    the result will be left adjusted. Thats why your sequence looks as
    1000, 231, 41 ...

    If you really need to convert your field into character and order by
    your original field, just add your original field into your select
    list.

    Select Sum(WhatEver), Char(Hess), Hess
    From MyTable
    Group by 2, 3
    Order By 3

    In either way converting a numeric field in the select list and group
    and order by this new field will result in a very bad performance,
    because no index can be used to access the data, i.e. a table scan
    will performed, the result copied, grouped and sorted.

    I assume you try it to archiefe the requirements you had yesterday.
    But this is the wrong way.

    Birgitta


  9. Re: SQL "ORDER BY" usage

    > In either way converting a numeric field in the select list and group
    > and order by this new field will result in a very bad performance,


    Client is expecting a slower response for this type of request.
    >
    > I assume you try it to archiefe the requirements you had yesterday.
    > But this is the wrong way.


    Any thoughts on what the right way to acheive my goal is?

    And I will try adding the same column twice, ie. ...char(hess),
    hess....

    Thanks again for your thoughts
    that 1 guy



  10. Re: SQL "ORDER BY" usage

    Birgitta,
    I tried:

    select sum(hewag), char(hess), char(heloc), heloc from LIUNAD01/DUHS
    TE where ((hess > 316903159) and (hedatf > 20060000)) group by hess,
    heloc order by heloc

    and it displayed the records as I was hoping for...... :-)

    Many, many thanks,
    that 1 guy


  11. Re: SQL "ORDER BY" usage

    Have you tried this:

    select sum(hewag), char(hess), digits(heloc) from LIUNAD01/DUHS
    TE where ((hess > 316903159) and (hedatf > 20060000)) group by hess,
    heloc order by digits(heloc)

    if heloc is always unsigned integer that should work.


  12. Re: SQL "ORDER BY" usage

    On Feb 22, 11:59 am, "walker.l2" wrote:
    > Have you tried this:
    > select sum(hewag), char(hess), digits(heloc) from LIUNAD01/DUHS
    > TE where ((hess > 316903159) and (hedatf > 20060000)) group by hess,
    > heloc order by digits(heloc)
    >
    > if heloc is always unsigned integer that should work.


    No I have not. I had another discussion with my Boss and he thinks
    that this has become too complex for what we are trying to accomplish
    for the User. We were trying to keep from having to teach our client
    SQL. He has asked me to re-state what we are trying to accomplish to
    see if you might have some suggestions on how to approach and
    accomplish this.

    1) We are trying to acheive a simple interface so our client can
    create SQL statements without the need to know SQL. So we have a DDS
    interface they can use to create their SQL Query. We're not able to
    use a Parameter Query because we need not only the SELECT, but GRoup
    By and Order By usage. Currently we have an interface over dynamic
    SQL. There are 9 fields they can Order By and 9 Fields they can Group
    By. We are using embedded SQL in ILE RPG with a DDS subfile to handle
    Screen I/O. Does IBM have a Form or Display API to display the result
    using our DDS selection interface? He was thinking that if there is
    an API we could use our selection interface and with the API just Fill
    it to display the results. If there is an API it must be a scrollable
    display of the fields selected by the user.

    2) Is there a way to use a Form in ILE Embedded SQL?

    3) Better ways to accomlish? Maybe 1) is the incorrect way to
    approach this?

    If more clarification is needed please let me know. I will accommodate
    as best I can. Feel free to e-mail me as well.

    Sincerely,
    that 1 guy
    Douglas P. Champion


  13. Re: SQL "ORDER BY" usage

    > No I have not. I had another discussion with my Boss and he thinks
    > that this has become too complex for what we are trying to accomplish
    > for the User. We were trying to keep from having to teach our client
    > SQL.
    >

    That's always going to be complex! But it is a good idea - not only
    might user SQL training be difficult, but it allows the possibility
    that a user could submit a query that will impact machine performance
    or access data unrelated to the intended application.

    > Does IBM have a Form or Display API to display the result
    > using our DDS selection interface?
    >

    There are some display APIs, but in a 5250 environment you're really
    better off sticking with the most common approaches such as subfiles.

    > 3) Better ways to accomlish? Maybe 1) is the incorrect way to
    > approach this?
    >

    I don't see a problem with your current approach. We do something
    similar in COBOL (albeit as a batch program receiving parameters and
    generating a print, rather than an interactive program doing screen
    handling as well) where we SELECT from 1 of 3 different files and sort
    on up to 6 different fields. If things are getting too big / messy for
    a single program, you might consider splitting it up like this:

    Program1:
    Display the screen to get selection criteria from the user
    Call program2, passing the selection criteria as parameters

    Program2:
    Build a new embedded SQL query based on the parameters on the CALL
    command
    Perform the query
    Store the results in a UserSpace (probably best in QTEMP)

    Program1 (when control returns from Program2):
    Read the results from the UserSpace
    Display the results on screen using subfiles to handle the scrolling

    You may find it easier to test the system like this since you can run
    the two parts independently.


    Walker.


  14. Re: SQL "ORDER BY" usage

    On Feb 23, 5:18 am, "walker.l2" wrote:
    > > No I have not. I had another discussion with my Boss and he thinks
    > > that this has become too complex for what we are trying to accomplish
    > > for the User. We were trying to keep from having to teach our client
    > > SQL.

    >
    > That's always going to be complex! But it is a good idea - not only
    > might user SQL training be difficult, but it allows the possibility
    > that a user could submit a query that will impact machine performance
    > or access data unrelated to the intended application.
    >
    > > Does IBM have a Form or Display API to display the result
    > > using our DDS selection interface?

    >
    > There are some display APIs, but in a 5250 environment you're really
    > better off sticking with the most common approaches such as subfiles.
    >
    > > 3) Better ways to accomlish? Maybe 1) is the incorrect way to
    > > approach this?

    >
    > I don't see a problem with your current approach. We do something
    > similar in COBOL (albeit as a batch program receiving parameters and
    > generating a print, rather than an interactive program doing screen
    > handling as well) where we SELECT from 1 of 3 different files and sort
    > on up to 6 different fields. If things are getting too big / messy for
    > a single program, you might consider splitting it up like this:
    >
    > Program1:
    > Display the screen to get selection criteria from the user
    > Call program2, passing the selection criteria as parameters
    >
    > Program2:
    > Build a new embedded SQL query based on the parameters on the CALL
    > command
    > Perform the query
    > Store the results in a UserSpace (probably best in QTEMP)
    >
    > Program1 (when control returns from Program2):
    > Read the results from the UserSpace
    > Display the results on screen using subfiles to handle the scrolling
    >
    > You may find it easier to test the system like this since you can run
    > the two parts independently.
    >
    > Walker.


    Walker,
    Thanks for the additional input. When the Boss arrives this morning
    I will show him your reply so we can continue our discussion.

    Anyone else have any thoughts????????

    thanks
    that 1 guy


  15. Re: SQL "ORDER BY" usage

    Glad to be of help.
    Another potential advantage of the 2 program approach is that if the
    input interface changes (e.g. you add a web front-end), or the output
    interface changes (e.g. you add a print option), you can re-use
    program 2 (that actually does the SQL stuff) without making any
    changes.


+ Reply to Thread