在SQR中使用Break逻辑

目录(?) [+]

1. 不使用Break逻辑
2. 使用Print命令的On-Break选项
3. 使SQR程序API Ware

在报表中，当一列值改变时，也就是出现break时，通常会做一些特殊处理，比如跳过一行、在break之前或之后执行某个procedure、打印小计等等。

不使用Break逻辑

如下例，针对选择的每一个员工，打印该员工的姓名、公司和员工薪水。该例不使用任何break逻辑：

[plain] view plain copy print ?

1. !****************************
2. !An employee list program that uses no break logic
3. !****************************
4. Begin-Program
5. !****************************
6. Do List_Employees
7. End-Program
8. !****************************
9. Begin-Heading 2
10. !****************************
11. Print 'Company' (1,1)
12. Print 'Paygroup' (,+2)
13. Print 'Emplid' (,+2)
14. Print 'Name ' (,+4)
15. Print 'Annual Salary' (,+15)
16. Print ' ' (2,1)
17. End-Heading
18. !*****************************************
19. Begin-Procedure List_Employees
20. !*****************************************
21. Begin-Select
22. B.Company (,1,7)
23. B.Paygroup (,+2,8)
24. A.Emplid (,+2,8)
25. A.Name (,+2,20)
26. B.Annual_Rt (,+2,12) Edit $,$$$,$$$.00
27. Position (+1)
28. From PS_Personal_Data A, PS_Job B
29. Where A.Emplid=B.Emplid
30. And B.Effdt=(Select Max(D.Effdt) From PS_Job D
31. Where D.Effdt<=Sysdate
32. And D.Emplid=B.Emplid
33. And D.Empl_Rcd=B.Empl_Rcd)
34. And B.Effseq = (Select Max(C.Effseq) from PS_Job C
35. Where C.Emplid=B.Emplid
36. And C.Empl_Rcd = B.Empl_Rcd
37. And C.Effdt=B.Effdt)
38. Order By B.Company, B.Paygroup, A.Emplid
40. End-Select
41. End-Procedure
42. !****************************

!****************************
!An employee list program that uses no break logic
!****************************
Begin-Program
!****************************
Do List_Employees
End-Program
!****************************
Begin-Heading 2
!****************************
Print 'Company' (1,1)
Print 'Paygroup' (,+2)
Print 'Emplid' (,+2)
Print 'Name ' (,+4)
Print 'Annual Salary' (,+15)
Print ' ' (2,1)
End-Heading
!*****************************************
Begin-Procedure List_Employees
!*****************************************
Begin-Select
B.Company (,1,7)
B.Paygroup (,+2,8)
A.Emplid (,+2,8)
A.Name (,+2,20)
B.Annual_Rt (,+2,12) Edit $,$$$,$$$.00
Position (+1)
From PS_Personal_Data A, PS_Job B
Where A.Emplid=B.Emplid
And B.Effdt=(Select Max(D.Effdt) From PS_Job D
Where D.Effdt<=Sysdate
And D.Emplid=B.Emplid
And D.Empl_Rcd=B.Empl_Rcd)
And B.Effseq = (Select Max(C.Effseq) from PS_Job C
Where C.Emplid=B.Emplid
And C.Empl_Rcd = B.Empl_Rcd
And C.Effdt=B.Effdt)
Order By B.Company, B.Paygroup, A.Emplid

End-Select
End-Procedure
!****************************

该SQR报表输出如下：

使用Print命令的On-Break选项

当某个输出字段的值改变时，On-Break选项会触发指定的操作。可以指定 On-Break 选项的一些限定词（qualifier），这些限定词定义了当break出现时要采取的操作。使用最频繁的限定词是Print（不要与Print命令搞混了）。Print 限定词是On-Break选项的默认限定词。当制定On-Break选项时不必指定该限定词。当使用On-break 选项的 Print 限定词时，可以指定 break 字段何时被打印或不被打印：

• Always 总是打印Break 字段。
• Change 只有当break字段的值改变时才打印。默认为Change。
• Change/Top-Page 1.当break字段的值改变时打印 2.在每一页的顶部时打印
• Never break字段永远不打印

如下例：

[plain] view plain copy print ?

1. !****************************
2. !Using break logic in the employee list program
3. !****************************
4. Begin-Program
5. !****************************
6. Do List_Employees
7. End-Program
8. !****************************
9. Begin-Heading 2
10. !****************************
11. Print 'Company' (1,1)
12. Print 'Paygroup' (,+2)
13. Print 'Emplid' (,+2)
14. Print 'Name ' (,+4)
15. Print 'Annual Salary' (,+15)
16. Print ' ' (2,1)
17. End-Heading
18. !*****************************************
19. Begin-Procedure List_Employees
20. !*****************************************
21. Begin-Select
22. ! We use the On-Break option below:
23. B.Company (,1,7) On-Break Print=Change/Top-Page Skiplines=1
24. B.Paygroup (,+2,8)
25. A.Emplid (,+2,8)
26. A.Name (,+2,20)
27. B.Annual_Rt (,+2,12) edit $,$$$,$$$.00
28. Position (+1)
29. From PS_Personal_Data A, PS_Job B
30. Where A.Emplid=B.Emplid
31. And B.Effdt=(Select Max(D.Effdt) From PS_Job D
32. Where D.Effdt<=Sysdate
33. And D.Emplid=B.Emplid
34. And D.Empl_Rcd=B.Empl_Rcd)
35. And B.Effseq = (Select Max(C.Effseq) from PS_Job C
36. Where C.Emplid=B.Emplid
37. And C.Empl_Rcd = B.Empl_Rcd
38. And C.Effdt=B.Effdt)
39. Order By B.Company, B.Paygroup, A.Emplid
40. End-Select
41. End-Procedure
42. !****************************

!****************************
!Using break logic in the employee list program
!****************************
Begin-Program
!****************************
Do List_Employees
End-Program
!****************************
Begin-Heading 2
!****************************
Print 'Company' (1,1)
Print 'Paygroup' (,+2)
Print 'Emplid' (,+2)
Print 'Name ' (,+4)
Print 'Annual Salary' (,+15)
Print ' ' (2,1)
End-Heading
!*****************************************
Begin-Procedure List_Employees
!*****************************************
Begin-Select
! We use the On-Break option below:
B.Company (,1,7) On-Break Print=Change/Top-Page Skiplines=1
B.Paygroup (,+2,8)
A.Emplid (,+2,8)
A.Name (,+2,20)
B.Annual_Rt (,+2,12) edit $,$$$,$$$.00
          Position (+1)
From PS_Personal_Data A, PS_Job B
Where A.Emplid=B.Emplid
And B.Effdt=(Select Max(D.Effdt) From PS_Job D
Where D.Effdt<=Sysdate
And D.Emplid=B.Emplid
And D.Empl_Rcd=B.Empl_Rcd)
And B.Effseq = (Select Max(C.Effseq) from PS_Job C
Where C.Emplid=B.Emplid
And C.Empl_Rcd = B.Empl_Rcd
And C.Effdt=B.Effdt)
Order By B.Company, B.Paygroup, A.Emplid
End-Select
End-Procedure
!****************************

输出结果如下：

1.当break字段的值改变时打印：

2.在每一页的顶部时打印

在上面的例子中，我将Company字段作为一个break字段，对其使用On-Break选项。除了Pritn限定词外，还是用了另外一个限定词Skiplines。该限定词指定当出现break时应该跳过多少行。

使SQR程序API Ware

要想在进程调度器中执行SQR程序，且又要使进程调度器能够更新SQR程序的运行状态，就必须要使SQR程序API Ware。也就是说必须在SQR成添加一些能够将SQR程序的状态返回给进程调度器的代码，从而让Process Monitor能够反应程序的当前状态。可以通过#Include命令包含PeopleSoft 自带的一些SQC文件。如上例，因为没有#include任何SQC文件，所以在进程监控器中出现如下结果：

可见程序已经成功Posted，但是状态却为Error。

修改TEST10B.SQR程序：

[plain] view plain copy print ?

1. !****************************
2. !TEST10B.SQR
3. !Using break logic in the employee list program
4. !****************************
6. <span style="color: rgb(255, 0, 0);">#Include 'setenv.sqc' !Set environment</span>
7. <span style="color: rgb(255, 0, 0);">begin-setup
8. #Include 'setupdb.sqc'
9. end-setup</span>
11. Begin-Program
12. <span style="color: rgb(255, 0, 0);">Do Stdapi-Init</span>
14. Do List_Employees
16. <span style="color: rgb(255, 0, 0);">Do Stdapi-Term</span>
17. End-Program
18. !****************************
19. Begin-Heading 2
20. !****************************
21. Print 'Company' (1,1)
22. Print 'Paygroup' (,+2)
23. Print 'Emplid' (,+2)
24. Print 'Name ' (,+4)
25. Print 'Annual Salary' (,+15)
26. Print ' ' (2,1)
27. End-Heading
28. !*****************************************
29. Begin-Procedure List_Employees
30. !*****************************************
31. Begin-Select
32. ! We use the On-Break option below:
33. B.Company (,1,7) On-Break Print=Change/Top-Page Skiplines=1
34. B.Paygroup (,+2,8)
35. A.Emplid (,+2,8)
36. A.Name (,+2,20)
37. B.Annual_Rt (,+2,12) edit $,$$$,$$$.00
38. Position (+1)
39. From PS_Personal_Data A, PS_Job B
40. Where A.Emplid=B.Emplid
41. And B.Effdt=(Select Max(D.Effdt) From PS_Job D
42. Where D.Effdt<=Sysdate
43. And D.Emplid=B.Emplid
44. And D.Empl_Rcd=B.Empl_Rcd)
45. And B.Effseq = (Select Max(C.Effseq) from PS_Job C
46. Where C.Emplid=B.Emplid
47. And C.Empl_Rcd = B.Empl_Rcd
48. And C.Effdt=B.Effdt)
49. Order By B.Company, B.Paygroup, A.Emplid
50. End-Select
51. End-Procedure
52. !****************************
53. <span style="color: rgb(255, 0, 0);">#Include 'datetime.sqc' !Routines for date and time formatting
54. #Include 'stdapi.sqc'</span>

!****************************
!TEST10B.SQR
!Using break logic in the employee list program
!****************************

#Include 'setenv.sqc' !Set environment
begin-setup
#Include 'setupdb.sqc'
end-setup

Begin-Program
   Do Stdapi-Init
   
   Do List_Employees

   Do Stdapi-Term
End-Program
!****************************
Begin-Heading 2
!****************************
Print 'Company' (1,1)
Print 'Paygroup' (,+2)
Print 'Emplid' (,+2)
Print 'Name ' (,+4)
Print 'Annual Salary' (,+15)
Print ' ' (2,1)
End-Heading
!*****************************************
Begin-Procedure List_Employees
!*****************************************
Begin-Select
! We use the On-Break option below:
B.Company (,1,7) On-Break Print=Change/Top-Page Skiplines=1
B.Paygroup (,+2,8)
A.Emplid (,+2,8)
A.Name (,+2,20)
B.Annual_Rt (,+2,12) edit $,$$$,$$$.00
          Position (+1)
From PS_Personal_Data A, PS_Job B
Where A.Emplid=B.Emplid
And B.Effdt=(Select Max(D.Effdt) From PS_Job D
Where D.Effdt<=Sysdate
And D.Emplid=B.Emplid
And D.Empl_Rcd=B.Empl_Rcd)
And B.Effseq = (Select Max(C.Effseq) from PS_Job C
Where C.Emplid=B.Emplid
And C.Empl_Rcd = B.Empl_Rcd
And C.Effdt=B.Effdt)
Order By B.Company, B.Paygroup, A.Emplid
End-Select
End-Procedure
!****************************
#Include 'datetime.sqc' !Routines for date and time formatting
#Include 'stdapi.sqc'

运行结果如下：