ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

Order By with Case

Collapse
This is a sticky topic.
X
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Order By with Case

    Is there a syntax where you can change the Order By clause based on Indicators?

    Need to add an F8 "Sort By" to a screen with a Declared SQL Statement. Something like...

    PHP Code:
    Select from ....
    Where ...
    Order By Case When (:inF8 = :cNoThen Fld1Fld2Fld3
                  When 
    (:inF8 = :cYesThen Fld2Fld3Fld1
       End

    Or. is it something like:

    PHP Code:
    Select from ....
    Where ...
    Order By Case When (:inF8 = :cNoThen Fld1
                  When 
    (:inF8 = :cYesThen Fld2
       End
    ,
     Case 
    When (:inF8 = :cNoThen Fld2
                  When 
    (:inF8 = :cYesThen Fld3
       End
    ,
     Case 
    When (:inF8 = :cNoThen Fld3
                  When 
    (:inF8 = :cYesThen Fld1
       End

    Help! I can't get either to work...

  • #2
    Re: Order By with Case

    Hi,

    you may try the following:
    PHP Code:
    D IndPtr          S               *   inz(%Addr(*In))              
    D                 DS                  BASED(IndPtr)                
    D   F08                  8      8N 

     
    /Free
        
    Declare .... 
        
    Order By Case When F08 '1' Then Fld1 Else NULL end,
                 Case 
    When F08 '1' Then Fld2 Else NULL end,
                 Case 
    When F08 '1' Then Fld3 Else NULL end,
                 Case 
    When F08 '0' Then Fld2 Else NULL end,
                 Case 
    When F08 '0' Then Fld3 else NULL end,
                 Case 
    When F08 '0' Then Fld1 Else NULL end 

    Comment


    • #3
      Re: Order By with Case

      Thanks B!! Again, you're the best!! Ended up doing this...

      PHP Code:
      Order By Case When :inF8='0' and :inF9='0'
                    
      Then DateArTm Else Null End,
               Case 
      When :inF8='0' and :inF9='0'
                    
      Then Priority Else Null End,
               Case 
      When :inF8='0' and :inF9='0'
                    
      Then ReqDate  Else Null End,
                                                
               Case 
      When :inF8='1' and :inF9='0'
                    
      Then Priority Else Null End,
               Case 
      When :inF8='1' and :inF9='0'
                    
      Then ReqDate  Else Null End,
               Case 
      When :inF8='1' and :inF9='0'
                    
      Then DateArTm Else Null End,
                                                
               Case 
      When :inF8='0' and :inF9='1'
                    
      Then DltdDate Else Null End,
               Case 
      When :inF8='0' and :inF9='1'
                    
      Then CompDate Else Null End 
      For Fetch Only
      Made for very easy view/sort functionality (F8 toggle for view) and not having to code a separate Declare or View.

      Thanks!

      Comment


      • #4
        Re: Order By with Case

        okay... I have to ask

        whatz this do?

        Else NULL end
        All my answers were extracted from the "Big Dummy's Guide to the As400"
        and I take no responsibility for any of them.

        www.code400.com

        Comment


        • #5
          Re: Order By with Case

          Originally posted by jamief View Post
          okay... I have to ask

          whatz this do?
          ... if the condition is not true, the column will be ignored!

          Birgitta

          Comment


          • #6
            Re: Order By with Case

            Thanks Birgitta .... so it doesnt function like a select statement and just
            test for the "current" conditions and run that statement?
            In your example you would just input the sort field name in place of
            fld1..fld2....

            Would it be much faster than building it dynamically ....
            for example I build the string dynamically in RPG ..
            Would it be better for me to switch to something
            like your example?

            the way I do it now is to just add the sort by field(s) I want...

            for example: #FLD comes from the DDS..this is where the cursor
            is when user pressed F8... so user can sort by any column on
            my subfile....
            DDS
            PHP Code:
            A                                      RTNCSRLOC(&#REC &#FLD) 
            RPG
            Code:
                 //                                                         
                 // F8 pressed set sort field                               
                 //                                                         
                     when  Choice = setsort;                                
                      select;                                               
                       when #FLD = 'S1CNAME';                               
                       sortby = '1';                                        
                       when #FLD = 'S1CITY' or #FLD = 'S1STATE';            
                       sortby = '2';                                        
                       when #FLD = 'S1ZIP';                                 
                       sortby = '3';           //desc - descending          
                       when #FLD = 'S1SCYTD';                               
                       sortby = '4';           //desc                       
                       when #FLD = 'S1SLYTD';                               
                       sortby = '5';           //desc                       
                       when #FLD = 'S1LASTYEAR';                            
                       sortby = '6';           //desc                       
                       when #FLD = 'S1LASTINV';                             
                       sortby = '7';           //desc                       
                       when #FLD = 'S1DAYS';                                
                       sortby = '8';           //desc     
                      other;                              
                       sortby = '4';                      
                      endsl;                              
                      exsr $clearsfl;                     
                      exsr $loadsfl;                      
            
            ...... blah blah blah 
            
              //--------------------------------------------------------      
              // $loadsfl- load up the entire subfile                         
              //--------------------------------------------------------      
                   begsr $loadsfl;                                            
                                                                              
                    if  SavRrn  > *zeros;                                     
                     RRN1  =  SavRrn;                                         
                     SCRRN =  SavRrn;                                         
                    endif;                                                    
                                                                              
                    sqlstmt = 'select * from arc16work ';                     
                                                                              
                    select;                                                   
                     when sortby = '1';                                       
                      sqlstmt = %trim(sqlstmt) +                              
                              '  order by  s2name ';                          
                     when sortby = '2';                                       
                      sqlstmt = %trim(sqlstmt) +                              
                              '  order by  state, city   ';                   
                     when sortby = '3';                                       
                     sqlstmt = %trim(sqlstmt) +                       
                             '  order by  state, zipcode  desc ';     
                    when sortby = '4';                                
                     sqlstmt = %trim(sqlstmt) +                       
                             '  order by  salesc desc ' ;             
                    when sortby = '5';                                
                     sqlstmt = %trim(sqlstmt) +                       
                             '  order by salesp desc ' ;              
                    when sortby = '6';                                
                     sqlstmt = %trim(sqlstmt) +                       
                             '  order by saleslr desc ' ;             
                    when sortby = '7';                                
                     sqlstmt = %trim(sqlstmt) +                       
                             '  order by lastinv desc ' ;             
                    when sortby = '8';                                
                     sqlstmt = %trim(sqlstmt) +                       
                             '  order by diffdays desc ' ;            
                   endsl;                                             
                                                                      
                   openList();                                        
                   dow fetchNext2();                                              
                                                                                  
                    H1sl# = SOLDTO;                                               
                    s1cname = S2NAME;                                             
                    s1city  = title(city);                                        
                    s1state = state;                                              
                    if zipcode <> *blanks and                                     
                            %check('0123456789' : %subst(zipcode:1:5)) = *zeros;  
                     s1zip  =  %dec(%subst(zipcode:1:5):5:0);                     
                    else;                                                         
                     s1zip  =  *zeros;                                            
                    endif;                                                        
                    s1scytd = SALESC;                                             
                    s1slytd = SALESP;                                             
                    s1lastyear = SALESLR;                                         
                    s1lastinv = LASTINV;                                          
                    s1days = DIFFDAYS;                                            
                    s1status = STATUS;                                            
                    h1days = numberdays;                                          
                                                                                  
                   clear s1diff;                                                      
                   if s1scytd < s1slytd;                                              
                    s1diff = '<';                                                     
                   endif;                                                             
                                                                                      
               // get last call date                                                  
                   exec sql                                                           
                    select coalesce(max(sddat),0 )                                    
                       into :CYMD                                                     
                       from  SLDCOMM                                                  
                       where SDSL# = : SOLDTO and SDEVCD = 'CL';  // CL = sales Call  
                   clear s1lastcall;                                                  
                   if cymd <> *zeros;                                                 
                    isodate = %date(cymd:*cymd);                                      
                    s1lastcall = %dec(isodate:*iso);                                  
                   endif;                                                             
                                                                                      
                   clear s1outstand;                                                  
                   // outstanding Invoices                                            
                   setll (company : h1sl#) AROOUTID;                                  
                   reade (company : h1sl#) AROOUTID;                    
                   dow not%eof(AROOUTID);                               
                    invoiceiso = %date(AOIDAT:*cymd);                   
                    if %diff(%date():invoiceISO:*days) > h1days;        
                     s1outstand = '*';                                  
                     leave;                                             
                    endif;                                              
                    reade (company : h1sl#) AROOUTID;                   
                   enddo;                                               
                                                                        
                   RRN1 += 1;                                           
                   SCRRN = RRN1;                                        
                   write SUB01;                                         
                   *in70 = *off;                                        
                  enddo;                                                
                  closeList();                                          
                                                                        
                  *in33 = *on;                                          
                  savrrn = SCRRN;                                       
                                                                        
              //                                                                   
              //  If no records in subfile then do not disply the subfile.         
              //                                                                   
                 if SavRrn  = *zeros;                                              
                  *in31 = *off;                                                    
                 else;                                                             
                  RRN1  = 1;                                                       
                  SCRRN  = 1;                                                      
                 endif;                                                            
                                                                                   
                endsr;
            Last edited by jamief; August 26, 2009, 10:19 AM. Reason: added explination of #FLD
            All my answers were extracted from the "Big Dummy's Guide to the As400"
            and I take no responsibility for any of them.

            www.code400.com

            Comment


            • #7
              Re: Order By with Case

              Discussed in this article:
              Tekkie-Corner (Chapter 3) at http://www.sss-software.de/inn/power...-PowerInfo.pdf
              or
              Regards

              Kit
              http://www.ecofitonline.com
              DeskfIT - ChangefIT - XrefIT
              ___________________________________
              There are only 3 kinds of people -
              Those that can count and those that can't.

              Comment


              • #8
                Re: Order By with Case

                @Kit,
                I was sure you'll answer
                This was your turn!

                Birgitta

                Comment


                • #9
                  Re: Order By with Case

                  LOL... and I knew you were waiting for me, otherwise you would have answered just before going to work
                  Regards

                  Kit
                  http://www.ecofitonline.com
                  DeskfIT - ChangefIT - XrefIT
                  ___________________________________
                  There are only 3 kinds of people -
                  Those that can count and those that can't.

                  Comment


                  • #10
                    Re: Order By with Case

                    Here's the final product for those who are interested...

                    PHP Code:
                    Exec SQL                                    
                    Declare Req Scroll Cursor For               
                    Select Request_ID,                          
                           
                    Customer,                            
                           
                    ReqDate,                             
                           
                    FlagArTm,                            
                           
                    DateArTm,                            
                           
                    Priority,                            
                           
                    AddInfo,                             
                           
                    CompDate,                            
                           
                    DltdDate                             
                      From CustReq                              
                     Where OrigUser 
                    = :wOrigUser                
                       
                    and Customer >= case when :S3_SCUST <> 
                                            then 
                    :S3_Scust      
                                            
                    else 0              
                                       end                      
                     Order By 
                    Case When :inF8='0' and :inF9='0' 
                                   
                    Then DateArTm Else Null End
                              Case 
                    When :inF8='0' and :inF9='0' 
                                   
                    Then Priority Else Null End,
                              Case 
                    When :inF8='0' and :inF9='0'
                                   
                    Then ReqDate  Else Null End,

                              Case 
                    When :inF8='1' and :inF9='0'
                                   
                    Then Priority Else Null End,
                              Case 
                    When :inF8='1' and :inF9='0'
                                   
                    Then ReqDate  Else Null End,
                              Case 
                    When :inF8='1' and :inF9='0'
                                   
                    Then DateArTm Else Null End,
                                                               
                              Case 
                    When :inF8='0' and :inF9='1'
                                   
                    Then DltdDate Else Null End,
                              Case 
                    When :inF8='0' and :inF9='1'
                                   
                    Then CompDate Else Null End 
                     
                    For Read Only
                    From the User Screen... there is an F8/Sort option and an F9/Deleted Records only option.

                    Pressing F8 toggles the sort based on Date & Priority. We added an additional filter inside the Sub-file load to weed out Non-deleted records when the F9 was turned on, but still wanted the sort associated with the display of the Deleted records. When F8 or F9 is pressed, we just close/open the Cursor and re-load. Quite simple actually... even JAMIE could do it!

                    Oh, and it also allows for a "position to" for the Customer Number in the Where Clause
                    Last edited by FaStOnE; August 27, 2009, 08:21 AM.

                    Comment


                    • #11
                      Re: Order By with Case

                      Kit your answer seems to ride the fence...

                      You gave both a (huge) advantage.. When we are talking speeds
                      what kind of speed differences are we talking.

                      I actually generate a workfile -- (global Temp file using SQL)

                      then I use dynamic sql to sort it about 100 ways via the
                      cursor position on the loaded subfile...
                      I have multiple users locally using this method with no noticable
                      loss of speed.. (now records are in the range of 100 - 200)

                      I also have a large group using both verizon and UsCellular networks
                      remotely again with no noticeable speed loss.
                      The largest time loss is the building of the temp file.
                      The sorts are magically delicious....

                      Do you think making the sorts static would made a large speed
                      difference?

                      jamie
                      All my answers were extracted from the "Big Dummy's Guide to the As400"
                      and I take no responsibility for any of them.

                      www.code400.com

                      Comment


                      • #12
                        Re: Order By with Case

                        I find that on slower machines, it does. For example on our test machine some dynamic SQL is slower, whereas on our live machine, it's negligible.

                        To quote Birgitta http://forums.systeminetwork.com/isn...71&postcount=9

                        The snippet you provided is pretty much static in that the user has 8 set choices for the sequence required - either or scenario.

                        In the dynamic SQL approach in the example (in the article), the user can choose any or all of the fields, and thereby give each selected field an order by "ranking".

                        I reckon I should have put it the article (but me is old and I forgot)...
                        in the first dynamic request example, the result would be:
                        Code:
                        ...
                        order by DateJoined, EmpNo, EmpSnme
                        in the second dynamic request example, the result would be:
                        Code:
                        ...
                        order by EmpCoy, EmpDiv, EmpTyp, EmpBday, EmpSnme, EmpBday, EmpNo

                        The article was more of a discussion of the 2 different techniques rather than recommending 1 over the other.

                        Just btw... you may have it and not have pasted it... using the "optimize for" when only few records are expected to be returned really provides much better performance differences than choosing between static & dynamic SQL (imo). In my tests yesterday, using "optimize for 1 row" took 1/12 the time than when not using it.

                        Hope this helps.
                        Last edited by kitvb1; August 27, 2009, 09:46 AM.
                        Regards

                        Kit
                        http://www.ecofitonline.com
                        DeskfIT - ChangefIT - XrefIT
                        ___________________________________
                        There are only 3 kinds of people -
                        Those that can count and those that can't.

                        Comment


                        • #13
                          Re: Order By with Case

                          Thanks Kit.. no I havent ever tried "optimize for" but I will add and let you know what I see...

                          As everyone knows I really dont have a clue...

                          I like SQL and have been moving "us/we" more towards that as
                          I write new apps.... So any info is GREAT info.

                          Thanks for staying up late just to help me

                          jamie
                          All my answers were extracted from the "Big Dummy's Guide to the As400"
                          and I take no responsibility for any of them.

                          www.code400.com

                          Comment


                          • #14
                            Re: Order By with Case

                            Originally posted by jamief View Post
                            I like SQL and have been moving "us/we" more towards that as
                            I write new apps.... So any info is GREAT info.
                            You get to write new apps? wow!! @#%@#%@#%@#%, you're so lucky. You got a job for me? All I do is change fields on displays to be blinking when there's an internal text for the customer or blinking & red if he also hasn't paid his acct.
                            Regards

                            Kit
                            http://www.ecofitonline.com
                            DeskfIT - ChangefIT - XrefIT
                            ___________________________________
                            There are only 3 kinds of people -
                            Those that can count and those that can't.

                            Comment


                            • #15
                              Re: Order By with Case

                              Jamie, just by the way, 2 weeks after that article, I think I've come up with a better way (for maintainability) than using the Case When or dynamic statement- if the fields are static. I'm just waiting for an opportunity to test it out.

                              Use a parameter file - just as an example:
                              Code:
                              [FONT=Courier New]Pgm        Sql Cursor   Seq    Sort Fields[/FONT]
                              [FONT=Courier New]ABC        CusCsr        1     CustNr, InvNr[/FONT]
                              [FONT=Courier New]ABC        CusCsr        2     CustNr, InvDte [/FONT]
                              [FONT=Courier New]ABC        CusCsr        3     CustNr, InvAmt[/FONT]
                              [FONT=Courier New]ABC        CusCsr        4     InvAmt, CustNr[/FONT]
                              [FONT=Courier New]DEF        StockCsr      1     PartNr, Stockroom[/FONT]
                              [FONT=Courier New]DEF        StockCsr      2     Stockroom   [/FONT]
                              This means that you just have to get the sort sequence from a file dependant on the user's choice.

                              The advantage with this way is that if you only add in a new sequence as an option, you change the file and there is no programming changes to be made. I think though, that this means this will then always have to be dynamic SQL. (maybe Biggie can enlighten us here on the last sentence )
                              Last edited by kitvb1; August 28, 2009, 04:27 AM.
                              Regards

                              Kit
                              http://www.ecofitonline.com
                              DeskfIT - ChangefIT - XrefIT
                              ___________________________________
                              There are only 3 kinds of people -
                              Those that can count and those that can't.

                              Comment

                              Working...
                              X