midrange.com code scratchpad
Name:
getreldate table definition and population script
Scriptlanguage:
Plain Text
Tabwidth:
4
Date:
05/15/2008 02:50:16 pm
IP:
Logged
Description:
Use RUNSQLSTM or Inav Run SQl Scripts to create and populate DATRELPF. Rename file/fields as you need
Code:
  1. --  Generate SQL 
  2. --  Version:                       V5R4M0 060210 
  3. --  Generated on:                  05/15/08 08:21:42 
  4. --  Relational Database:           BOISE03 
  5. --  Standards Option:              DB2 UDB iSeries 
  6.  
  7. DROP TABLE DATRELPF ; 
  8.  
  9. CREATE TABLE DATRELPF ( 
  10. --  SQL1506   30   Key or attribute for DATRELPF ignored. 
  11.     DRDATE CHAR(15) CCSID 37 NOT NULL DEFAULT '' , 
  12.     DRWEEK CHAR(1) CCSID 37 NOT NULL DEFAULT '' , 
  13.     DRDOW NUMERIC(3, 0) NOT NULL DEFAULT 0 )   
  14.  
  15.     RCDFMT DATRELRF   ; 
  16.  
  17. LABEL ON TABLE DATRELPF 
  18.     IS 'Relative Dates File' ;
  19. Insert Into DATRELPF (DRDATE, DRWEEK, DRDOW)
  20.  
  21. Values 
  22. ('1'   ,'S',1),
  23. ('+1D' ,'S',1),
  24. ('+1W' ,'S',7),
  25. ('10'  ,'S',10),
  26. ('+10D','S',10),
  27. ('+10W','S',70),
  28. ('11'  ,'S',11),
  29. ('+11D','S',11),
  30. ('+11W','S',77),
  31. ('12'  ,'S',12),
  32. ('+12D','S',12),
  33. ('+12W','S',84),
  34. ('13'  ,'S',13),
  35. ('+13D','S',13),
  36. ('+13W','S',91),
  37. ('14'  ,'S',14),
  38. ('+14D','S',14),
  39. ('+14W','S',98),
  40. ('15'  ,'S',15),
  41. ('+15D','S',15),
  42. ('16'  ,'S',16),
  43. ('16'  ,'S',16),
  44. ('+16D','S',16),
  45. ('17'  ,'S',17),
  46. ('+17D','S',17),
  47. ('18'  ,'S',18),
  48. ('+18D','S',18),
  49. ('19'  ,'S',19),
  50. ('+19D','S',19),
  51. ('2'   ,'S',2),
  52. ('+2D' ,'S',2),
  53. ('+2W' ,'S',14),
  54. ('20'  ,'S',20),
  55. ('+20D','S',20),
  56. ('3'   ,'S',3),
  57. ('+3D' ,'S',3),
  58. ('+3W' ,'S',21),
  59. ('4'   ,'S',4),
  60. ('+4D' ,'S',4),
  61. ('+4W' ,'S',28),
  62. ('5'   ,'S',5),
  63. ('+5D' ,'S',5),
  64. ('+5W' ,'S',35),
  65. ('6'   ,'S',6),
  66. ('+6D' ,'S',6),
  67. ('+6W' ,'S',42),
  68. ('7'   ,'S',7),
  69. ('+7D' ,'S',7),
  70. ('+7W' ,'S',49),
  71. ('8'   ,'S',8),
  72. ('+8D' ,'S',8),
  73. ('+8W' ,'S',56),
  74. ('9  ' ,'S',9),
  75. ('+9D' ,'S',8),
  76. ('+9W' ,'S',63),
  77. ('-1'  ,'S',-1),
  78. ('-1D' ,'S',-1),
  79. ('-1W' ,'S',-7),
  80. ('-10' ,'S',-10),
  81. ('-11' ,'S',-11),
  82. ('-12' ,'S',-12),
  83. ('-13' ,'S',-13),
  84. ('-14' ,'S',-14),
  85. ('-14' ,'S',-14),
  86. ('-14D','S',-14),
  87. ('-15' ,'S',-15),
  88. ('-16' ,'S',-16),
  89. ('-17' ,'S',-17),
  90. ('-18' ,'S',-18),
  91. ('-19' ,'S',-19),
  92. ('-2 ' ,'S',-2),
  93. ('-2D' ,'S',-2),
  94. ('-2W' ,'S',-14),
  95. ('-20' ,'S',-20),
  96. ('-3 ' ,'S',-3),
  97. ('-3D' ,'S',-3),
  98. ('-3W' ,'S',-21),
  99. ('-4 ' ,'S',-4),
  100. ('-4D' ,'S',-4),
  101. ('-4W' ,'S',-28),
  102. ('-5 ' ,'S',-5),
  103. ('-5D' ,'S',-5),
  104. ('-5W' ,'S',-35),
  105. ('-6 ' ,'S',-6),
  106. ('-6D' ,'S',-6),
  107. ('-6W' ,'S',-42),
  108. ('-7 ' ,'S',-7),
  109. ('-7D' ,'S',-7),
  110. ('-7W' ,'S',-49),
  111. ('-8 ' ,'S',-8),
  112. ('-8W' ,'S',-56),
  113. ('-9 ' ,'S',-9),
  114. ('-9W' ,'S',-63),
  115. ('C'   ,'D',0),
  116. ('FRI  ','C',6),
  117. ('FRIDAY','C',6),
  118. ('LAST FRI','L',6),
  119. ('LAST FRIDAY','L',6),
  120. ('LAST MON','L',2),
  121. ('LAST MONDAY','L',2),
  122. ('LAST SAT','L',7),
  123. ('LAST SATURDAY','L',7),
  124. ('LAST SUN','L',1),
  125. ('LAST SUNDAY','L',1),
  126. ('LAST THU','L',5),
  127. ('LAST THURSDAY','L',5),
  128. ('LAST TUE','L',3),
  129. ('LAST TUESDAY','L',3),
  130. ('LAST WED','L',4),
  131. ('LAST WEDNESDAY','L',4),
  132. ('MON','C',2),
  133. ('MONDAY','C',2),
  134. ('NEXT FRI','N',6),
  135. ('NEXT FRIDAY','N',6),
  136. ('NEXT MON','N',2),
  137. ('NEXT MONDAY','N',2),
  138. ('NEXT SAT','N',7),
  139. ('NEXT SATURDAY','N',7),
  140. ('NEXT SUN','N',1),
  141. ('NEXT SUNDAY','N',1),
  142. ('NEXT THU','N',5),
  143. ('NEXT THURSDAY','N',5),
  144. ('NEXT TUE','N',3),
  145. ('NEXT TUESDAY','N',3),
  146. ('NEXT WED','N',4),
  147. ('NEXT WEDNESDAY','N',4),
  148. ('SAT','C',7),
  149. ('SATURDAY','C',7),
  150. ('SUN','C',1),
  151. ('SUNDAY','C',1),
  152. ('THIS FRI','C',6),
  153. ('THIS FRIDAY','C',6),
  154. ('THIS MON','C',2),
  155. ('THIS MONDAY','C',2),
  156. ('THIS SAT','C',7),
  157. ('THIS SATURDAY','C',7),
  158. ('THIS SUN','C',1),
  159. ('THIS SUNDAY','C',1),
  160. ('THIS THU','C',5),
  161. ('THIS THURSDAY','C',5),
  162. ('THIS TUE','C',3),
  163. ('THIS TUESDAY','C',3),
  164. ('THIS WED','C',4),
  165. ('THIS WEDNESDAY','C',4),
  166. ('THU','C',5),
  167. ('THURSDAY','C',5),
  168. ('TM','D',1),
  169. ('TODAY','D',0),
  170. ('TOMORROW','D',1),
  171. ('TUE','C',3),
  172. ('TUESDAY','C',3),
  173. ('WED','C',4),
  174. ('WEDNESDAY','C',4),
  175. ('Y','D',-1),
  176. ('YESTERDAY','D',-1)
© 2004-2019 by midrange.com generated in 0.007s valid xhtml & css