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:
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 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 (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.Code:Order By Case :Sort When 1 Then EmployeeNo When 2 Then EmployeeSName Else City End
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):
or evenCode:Field: EmpNo EmpSnme EmpFnme EmpBday EmpCoy EmpDiv EmpTyp DateJoined Seq: 3 4 1
The Arrays Code:Code:Field: EmpNo EmpSnme EmpFnme EmpBday EmpCoy EmpDiv EmpTyp DateJoined Seq: 8 5 6 4 1 2 3
The ORDER BY 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 (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.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 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.
Reply With Quote
Originally Posted by jamief
)
