ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

Using order by with embedded SQL RPGLE.

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Using order by with embedded SQL RPGLE.

    I am trying to populate a subfile with data read from a master file in a particular order. The user can switch order by pressing the appropriate Function Key. The problem is the data populates the subfile based on the first statement processed all the time. I have cleared the array before launching the SQL statement, but it still populates the same way. The SQLRPGLE code is below and any feedback is appreciated.

    // Sort records by CAR#
    //
    If (IndDs.F5_Scar);
    IndDs.Car_HIRI = *on;
    IndDs.Stat_HIRI = *off;
    IndDs.Reas_HIRI = *off;
    exec sql DECLARE C0 CURSOR FOR
    SELECT cmcar#, cmstat, substr(cmnconf,1,50) FROM carmst
    ORDER BY cmcar#
    FOR READ ONLY ;

    exec sql OPEN C0;

    exec sql FETCH C0 FOR :Elements ROWS INTO Data ;
    exec sql GET DIAGNOSTICS :LastRecord = ROW_COUNT ;

    exec sql CLOSE C0 ;
    endif;

    //
    // Sort records by CAR Status
    //
    If (IndDs.F6_Sstat);
    IndDs.Car_HIRI = *off;
    IndDs.Stat_HIRI = *on;
    IndDs.Reas_HIRI = *off;
    exec sql DECLARE C1 CURSOR FOR
    SELECT cmcar#, cmstat, substr(cmnconf,1,50) FROM carmst
    ORDER BY cmstat
    FOR READ ONLY ;

    exec sql OPEN C1;

    exec sql FETCH C1 FOR :Elements ROWS INTO Data ;
    exec sql GET DIAGNOSTICS :LastRecord = ROW_COUNT ;

    exec sql CLOSE C1 ;
    endif;
    //
    // Sort records by Reason
    //
    If (IndDs.F7_Sreason);
    IndDs.Car_HIRI = *off;
    IndDs.Stat_HIRI = *off;
    IndDs.Reas_HIRI = *on;
    exec sql DECLARE C2 CURSOR FOR
    SELECT cmcar#, cmstat, substr(cmnconf,1,50) FROM carmst
    ORDER BY cmnconf
    FOR READ ONLY ;

    exec sql OPEN C2;

    exec sql FETCH C2 FOR :Elements ROWS INTO Data ;
    exec sql GET DIAGNOSTICS :LastRecord = ROW_COUNT ;

    exec sql CLOSE C2 ;
    endif;

  • #2
    Knee-jerk reaction - can you run it in debug? Is the 'IndDs.F5' being set off when other F-keys are pressed?

    Comment


    • mgarczynski
      mgarczynski commented
      Editing a comment
      Running in debug makes all of the proper selections. If F6 or F7 is pressed, the appropriate logic for the F-key is executed, The F5 logic is skipped as per the if statement. All indicators are in the proper status. I can see data in the array before the clear. The array is blank after the clear, after the SQL statement, the data in the array is in the order of F5 key.

  • #3
    Why 3 different SQL Statements at all?
    There is neither a need for different SQL statements nor for an array.
    The user select the sort sequence, so you'll put the information into a host variable, and then you can check the host variable in case clauses in the order by clause. If the data types of the sort columns are identical, you can put them in the same CASE clause otherwise you need different case clauses:

    Code:
    Declare Csr01 Cursor For
    Select ...
       From ...
       Where ...
       Order By Case when :IndDs.Car_HIRI = '1' Then cmcar#
                     When :IndDs.Stat_HIRI = '1' Then cmstat
                     End,
                     Case When :IndDs.Reas_HIRI = '1' Then cmnconf
                     End;
    Birgitta

    Comment


    • #4
      I think
      Code:
      ROWS INTO Data ;
      should be
      Code:
      ROWS INTO :Data ;
      Nicolas

      Comment


      • mgarczynski
        mgarczynski commented
        Editing a comment
        The program code actually is how you described. I guess the copy/paste dropped the : and I didn't notice.

    • #5
      Are you getting a success SQL state after every operation? I assume you're code checks and you just removed them from your post for clarity.

      Comment


      • #6
        Thanks for all of the great input. I restructured the code based on some feedback using the Case clause and the program works as expected.

        Comment

        Working...
        X