kitvb1
9th August 2009, 09:44
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/powerinfo/iNN-PowerInfo0907.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:
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:
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):
Field: EmpNo EmpSnme EmpFnme EmpBday EmpCoy EmpDiv EmpTyp DateJoined
Seq: 3 4 1
or even
Field: EmpNo EmpSnme EmpFnme EmpBday EmpCoy EmpDiv EmpTyp DateJoined
Seq: 8 5 6 4 1 2 3
The Arrays 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:
// 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.
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:
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:
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):
Field: EmpNo EmpSnme EmpFnme EmpBday EmpCoy EmpDiv EmpTyp DateJoined
Seq: 3 4 1
or even
Field: EmpNo EmpSnme EmpFnme EmpBday EmpCoy EmpDiv EmpTyp DateJoined
Seq: 8 5 6 4 1 2 3
The Arrays 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:
// 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.