+ Reply to Thread
Page 1 of 2 1 2 LastLast
Results 1 to 10 of 11

Thread: SQL: Made to Order

  1. Default SQL: Made to Order

    This is the Article published in the July 2009 edition iSeries Nation Network newsletter. The original article can be found in the Tekkie-Corner (Chapter 3) at http://www.sss-software.de/inn/power...erInfo0907.pdf

    SQL: Made to Order
    Kit von Blerk

    Have you ever wondered, “There must be a better way” when creating a program or SQL statement? I was wondering the same thing about an approach I had taken to a challenge, and a year later discussed it with a friend. The friend suggested another approach which, while excellent and would not require dynamic SQL, did not meet the particular needs of my requirements. In the end, we agreed that each approach had pro’s and con’s.

    In this TechTip, both approaches are outlined and discussed, but it should be noted that in both approaches, all other statements are ignored as it is only the ORDER BY statement which causes the second approach to be dynamic SQL.

    First approach (Static SQL): Using ORDER BY with multiple CASE WHEN statements. This would generally be used in the case where the user is offered a choice of 3 to 4 pre-defined sort sequences via one input field.

    The Case Code:
    Code:
     
    Order By Case When :Sort = 1 Then EmployeeSName else NULL End, 
    Case When :Sort = 1 Then EmployeeFirstName else NULL End, 
    Case When :Sort = 2 then EmployeeNo else NULL End, 
    Case When :Sort = 3 then Birthday Else NULL End Desc,
    Case When :Sort = 3 then EmployeeSurname Else NULL End Desc, 
    Case When :Sort = 3 then EmployeeFirstName Else NULL End Desc,
    ....
    The following is also possible if you want to use a single case clause, the order fields must have compatible data types, which means if EmployeeSName and EmployeeNo are both character, you may also type:
    Code:
     
    Order By Case :Sort When 1 Then EmployeeNo 
    When 2 Then EmployeeSName 
    Else City 
    End
    The (huge) advantage with this approach is that the SQL statement need not be dynamic and therefore executes faster. Another advantage is that the code is also pretty much self-documenting, in that there is no need to scroll-up or down to look for other fields.

    However, the user is limited to a pre-defined number of sort sequences. If too many sequencing options are available to the user, this might lead to confusion. The HELP documentation must be clear and would most likely require another screen or two of HELP text. Also, maintenance may become a problem. Let’s take for example, 8 fields with all combinations would give us 64 possible combinations. That’s 64 x 8 (nr. of fields) lines of code to cater for all combinations.

    Second approach (Dynamic SQL): Using Arrays to build an ORDER BY statement. This would generally be used when the requirement is to allow the user to choose the sort sequence from a pre-set list of fields and then allow them to rank that the priority of each field.

    Consider the following user requirements (the field names have been shortened for display purposes):
    Code:
     
    Field: EmpNo  EmpSnme   EmpFnme   EmpBday   EmpCoy   EmpDiv   EmpTyp   DateJoined
    Seq:     3      4                                                         1
    
    or even
    Code:
     
    Field: EmpNo  EmpSnme   EmpFnme   EmpBday   EmpCoy   EmpDiv   EmpTyp   DateJoined
    Seq:     8      5         6         4         1        2        3             
    
    The Arrays Code:
    Code:
     
    // SQL order by fields - fields names                                                       
    d DsqlOrd         ds
    d                               10a   inz('EmpNo')
    d                               10a   inz('EmpSnme') 
    d                               10a   inz('EmpFnme')
    d                               10a   inz('EmpBday') 
    d                               10a   inz('EmpCoy')
    d                               10a   inz('EmpDiv')
    d                               10a   inz('EmpTyp')
    d                               10a   inz('DateJoin')
    d  FldName                      10a   dim(8) overlay(DsqlOrd)              must =FldSeq dim
     
    // SQL order by fields - field rank selection
    d FldSeql         ds    
    d   dSQEmpNoFT                   1  0 inz
    d   dSQEmpSnmeFT                 1  0 inz
    d   dSQEmpFnmeFT                 1  0 inz  
    d   dSQEmpBdayFT                 1  0 inz
    d   dSQEmpCoyFT                  1  0 inz
    d   dSQEmpDivFT                  1  0 inz
    d   dSQEmpTypFT                  1  0 inz 
    d   dSQDteJoinFT                 1  0 inz
    d  FldSeq                        1  0 dim(8) overlay(FldSeql)              Fields from DSPF 
    
    The ORDER BY Code:
    Code:
     
    // setup group by & sort sequence                   
    if   FldSeql = *zero; 
     FldSeq(5) = 1;   // Set default seq if user hasn't entered anything
     FldSeq(2) = 2;   
    endif;    
     
    ordersql = ' ORDER by ';
    y = 0; 
     
    // now lookup the sequence                          
    for z = 1 to 8; 
     x = %lookup(z: FldSeq);                         
     
     // field is selected for sorting
     if x <> 0;   
       y += 1; 
     
       // we must not add a comma for the first one 
        if y > 1;          
          OrderSql = OrderSql + ', '; 
        endif;  
     
        ordersql = Ordersql + FldName(x);
     endif; 
    endfor; 
     
    EmpEnqySql = EmpEnqySql + %trimr(OrderSql);
    The (huge) advantage with this approach is the flexibility of the solution and the power offered to the user. Incidentally, should there be a need for a GROUP BY clause; this could also be easily added after the last statement.

    The disadvantage of this approach is that it uses dynamic SQL and is therefore slower. While not quite as self-documenting as the first approach, the code is easily read and maintenance is extremely simple.

    Food for thought:
    · Giving the user more set choices (as in the first approach), may result in the users entering a wrong option (or the developer making mistakes). Might they not be a little overwhelmed and just choose whichever looks most likely within the first couple of options?
    · What about the maintainability of the program afterwards? The dynamic approach in the above example has 72 possible combinations (0=Ignore is also an option).
    Once again, it comes down to the requirement and care should be taken as to which approach should be used. To use the trendy catchphrase: “Use the right tool for the job”.

    About the Author:
    Kit von Blerk in the owner of EcofIT Limited, an AS/400 (iSeries, System I, …) Software Solutions provider based in Germany. He started programming in COBOL (on an ICL ME29) in 1983 and changed to RPGIII on an IBM S/38 in 1986.
    Last edited by kitvb1; 22nd February 2010 at 08:47. Reason: Updated the URL for the original article.
    Regards

    Kit

  2. Default

    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
    Regards

    Kit

  3. Default

    Cross-post of a question from Jamie at Code400.com http://www.code400.com/forum/showthr...5050#post45050
    __________________________________________________ ___________________________________________
    Quote Originally Posted by jamief

    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
    Last edited by kitvb1; 29th August 2009 at 19:09.
    Regards

    Kit

  4. Default

    Cross-post of my reply at Code400.com http://www.code400.com/forum/showthr...5050#post45050
    __________________________________________________ __________________________________

    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:
    Code:
    ...
    order by DateJoined, EmpNo, EmpSnme
    in the second dynamic request example, the result would be:
    Code:
    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.
    Regards

    Kit

  5. Default

    Cross-post of my reply at Code400.com http://www.code400.com/forum/showthr...5050#post45050
    __________________________________________________ __________________________________

    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:
    Code:
    Pgm        Sql Cursor   Seq    Sort Fields
    ABC        CusCsr        1     CustNr, InvNr
    ABC        CusCsr        2     CustNr, InvDte 
    ABC        CusCsr        3     CustNr, InvAmt
    ABC        CusCsr        4     InvAmt, CustNr
    DEF        StockCsr      1     PartNr, Stockroom
    DEF        StockCsr      2     Stockroom 
    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 )
    Regards

    Kit

  6. Default

    Cross-post of some queries at Code400.com http://www.code400.com/forum/showthread.php?p=45050#post45050
    __________________________________________________ __________________________________
    Quote Originally Posted by jamief
    Can you put together a tiny little example of how to use a parameter file?

    I would love to see it in action!
    Quote Originally Posted by Pramendra
    me also waiting for example Kit...
    Quote Originally Posted by FaStOnE
    *Tap* *Tap* *Tap* *Tap* *Tap* *Tap*

    Sheesh ... what's taking so long?!?
    Regards

    Kit

  7. Default

    Cross-post of my reply at Code400.com http://www.code400.com/forum/showthread.php?p=45050#post45050
    __________________________________________________ __________________________________
    Ok.. so I took a little time out to do some testing.

    1. Try as I might, I cannot get it to find a record using static sql, i.e. just adding - " ORDER BY :dSrtflds " at the end of the declare. SQLCOD = 100 always. (I am sure Biggie can come up with some of her magic.) I tried it for about 3 hours and no go. I must admit 'cos it's not that important to me as I don't ever use this sort selection technique. I use the field ranking method as described in the article.

    2. Dynamic SQL is just boring - that took 5 mins to change the declare, define a field or two, do a prepare and Bob's-your-uncle.

    IMO, if you have no obstacle against using a dynamic SQL statement, this almost makes the "Order by case when..." redundant. The reason for stating this is that I believe in soft-coding where possible. With this technique, one can add/change many different order by's in many programs without having to change the source code.

    Here's some screenshots.

    If you want to see some code... hurry and ask 'cos I will deleting it soon.
    Regards

    Kit

  8. #8

    Default

    To make this even better... Leave the program name out of the file. This will allow the same order by fields to be used by many programs.

    A good approach to use this in an application may be to create a service program, read this table (using a SQL multiple row fetch) into a globally defined array DS. This means that with just 1 fetch, the array is now availbale to every program that uses this service program. Pretty cool.
    Last edited by kitvb1; 8th September 2009 at 14:39.
    Regards

    Kit

  9. Default

    Kit the example is really nice..

    Regards

  10. #10

    Default

    This Article has now also been published in the 13 January 2011 edition of System iNetwork Programming Tips edited by Scott Klement. In the new edition I added two clarifying statements This article can be found here http://systeminetwork.com/article/sql-made-order
    Regards

    Kit

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may post replies
  • You may post attachments
  • You may not edit your posts
  •