If I can assume Month_1..Month_120 as fields of your table rather than variables, then I suppose we can do things like this:
I= 1; while (I <= NoOfCol) do begin s= 'update YOUR_TABLE set Month_'||I||' = Month_'||I||' + ? where SOME_CONDITION'; execute statement (s) (nTotalMonth); I= I+1; end "YOUR_TABLE" & "SOME_CONDITION" are just symbolic, sorry to be wordy This approach is only good in syntax, in real performance I think it will be a bit slower than your long syntax, because of the run time compilation of EXECUTE STATEMENT. --- In [email protected], "Softtech Support" <stwizard@...> wrote: > > Thanks for your reply. > > I should have mentioned that this stored procedure is used to generate a > Stair-Step Financial Report (My first attempt at such a report I might add) > and therefore option 1 below would not work as I do not need to store the > results. > > I have used EXECUTE STATEMENT in the past for SELECT statements and even > after looking again at the LangRef-Update.pdf (For v1.5) I do not see how it > could be used for this instance. Am I missing something? > > A little more info: > I basically created a stored procedure that would generate a Stair-Step > Financial report that can list the number of accounts/cases turned, amount > turned, average amount turned for collections for a given period, followed > by the amount received for every month since the given period. > > The report works as follows: > If Client X has been a client for two years, then it would be a 24 x 24 > report. Shown below is an example of a client signed on Oct 2012 and we > started receiving accounts in Dec 2012. So the report is ran for the first > year 10/01/12 thru 06/30/13 and is a 9 x 9 report (9 rows - Periods x 9 > columns - Monthly collections). > > The start date for this report is 10/01/12 and every month following the > first has to be listed whether they had acounts turned to them for > collections or not. Then the Recovery would show how much was received in > each month following the turn. > > Obviously, each row has one less column filled out..so when you get to the > last row, there is only Month 1 on the report. > > PERIOD CASES PRINC_AMT AVG_CASE MONTH_1 MONTH_2 MONTH_3 MONTH_4 > MONTH_5 MONTH_6 MONTH_7 MONTH_8 MONTH_9 > Oct-12 0 0 0 0 0 0 0 > 0 0 0 0 0 > Nov-12 0 0 0 0 0 0 0 > 0 0 0 0 0 > Dec-12 292 162894.14 557.85 1101.64 2721.09 3510.35 2571.71 > 381.21 256.64 25.00 0 0 > Jan-13 229 161576.07 705.57 3802.94 762.90 3995.06 1062.37 > 1433.10 3206.25 0 0 0 > Feb-13 182 150174.56 825.13 1315.53 1502.86 1920.46 60.00 > 87.74 0 0 0 0 > Mar-13 158 122131.82 772.98 187.00 1867.37 1805.65 4251.79 > 0 0 0 0 0 > Apr-13 207 157906.67 762.83 1629.24 2649.20 500.83 0 > 0 0 0 0 0 > May-13 208 166616.77 801.04 167.23 1800.69 0 0 > 0 0 0 0 0 > Jun-13 294 236136.50 803.18 200.00 0 0 0 > 0 0 0 0 0 > > Is there somewhere I can post attachments? If so then I could post the full > stored procedure and an excel file that was generated by it. > > So any other ideas how to modify this stored procedure? > > Mike > > > ----- Original Message ----- > From: thp_pkmi > To: [email protected] > Sent: Tuesday, June 18, 2013 5:14 AM > Subject: [firebird-support] Re: Does Firebird have a way to reference > similar named fields? (Like MONTH_1 thru MONTH_120) > > > > I think you have 2 options: > > 1. normalize the table to have 2 fields "Month Number" & "Month Value", > then you can search record by "Month Number" for updating "Month Value" > > 2. use PSQL statement: EXECUTE STATEMENT, you can read about it in > Firebird-2.5-LangRef-Update.pdf > > --- In [email protected], "Softtech Support" <stwizard@> > wrote: > > > > In Delphi I have the ability to reference a field using FieldByName() > inside a For loop like this: > > > > For I := 1 to 120 do > > begin > > with cdsReport do > > begin > > FieldByName('MONTH_' + IntToStr(I)).Visible := iNoOfCols >= I; > > end; > > end; > > > > > > Is there any way to do this in a Firebird Stored Procedure? I have 120 > fields named MONTH_1 thru MONTH_120 and currently I have to reference them > like this. Just wanting to know if there is a better way.. > > ... > > I = 1; > > > > WHILE (I <= iNoOfColumns ) DO > > BEGIN > > IF (I = 1) THEN > > MONTH_1 = MONTH_1 + nTotalMonth; > > ELSE IF (I = 2) THEN > > MONTH_2 = MONTH_2 + nTotalMonth; > > ELSE IF (I = 3) THEN > > MONTH_3 = MONTH_3 + nTotalMonth; > > ELSE IF (I = 4) THEN > > MONTH_4 = MONTH_4 + nTotalMonth; > > ELSE IF (I = 5) THEN > > MONTH_5 = MONTH_5 + nTotalMonth; > > ELSE IF (I = 6) THEN > > MONTH_6 = MONTH_6 + nTotalMonth; > > ELSE IF (I = 7) THEN > > MONTH_7 = MONTH_7 + nTotalMonth; > > ELSE IF (I = 8) THEN > > MONTH_8 = MONTH_8 + nTotalMonth; > > ELSE IF (I = 9) THEN > > MONTH_9 = MONTH_9 + nTotalMonth; > > ELSE IF (I = 10) THEN > > MONTH_10 = MONTH_10 + nTotalMonth; > > ELSE IF (I = 11) THEN > > MONTH_11 = MONTH_11 + nTotalMonth; > > ELSE IF (I = 12) THEN > > MONTH_12 = MONTH_12 + nTotalMonth; > > ELSE IF (I = 13) THEN > > MONTH_13 = MONTH_13 + nTotalMonth; > > ELSE IF (I = 14) THEN > > MONTH_14 = MONTH_14 + nTotalMonth; > > ELSE IF (I = 15) THEN > > MONTH_15 = MONTH_15 + nTotalMonth; > > ELSE IF (I = 16) THEN > > MONTH_16 = MONTH_16 + nTotalMonth; > > ELSE IF (I = 17) THEN > > MONTH_17 = MONTH_17 + nTotalMonth; > > ELSE IF (I = 18) THEN > > MONTH_18 = MONTH_18 + nTotalMonth; > > ELSE IF (I = 19) THEN > > MONTH_19 = MONTH_19 + nTotalMonth; > > ELSE IF (I = 20) THEN > > MONTH_20 = MONTH_20 + nTotalMonth; > > ELSE IF (I = 21) THEN > > MONTH_21 = MONTH_21 + nTotalMonth; > > ELSE IF (I = 22) THEN > > MONTH_22 = MONTH_22 + nTotalMonth; > > ELSE IF (I = 23) THEN > > MONTH_23 = MONTH_23 + nTotalMonth; > > ELSE IF (I = 24) THEN > > MONTH_24 = MONTH_24 + nTotalMonth; > > ELSE IF (I = 25) THEN > > MONTH_25 = MONTH_25 + nTotalMonth; > > ELSE IF (I = 26) THEN > > MONTH_26 = MONTH_26 + nTotalMonth; > > ELSE IF (I = 27) THEN > > MONTH_27 = MONTH_27 + nTotalMonth; > > ELSE IF (I = 28) THEN > > MONTH_28 = MONTH_28 + nTotalMonth; > > ELSE IF (I = 29) THEN > > MONTH_29 = MONTH_29 + nTotalMonth; > > ELSE IF (I = 30) THEN > > MONTH_30 = MONTH_30 + nTotalMonth; > > ELSE IF (I = 31) THEN > > MONTH_31 = MONTH_31 + nTotalMonth; > > ELSE IF (I = 32) THEN > > MONTH_32 = MONTH_32 + nTotalMonth; > > ELSE IF (I = 33) THEN > > MONTH_33 = MONTH_33 + nTotalMonth; > > ELSE IF (I = 34) THEN > > MONTH_34 = MONTH_34 + nTotalMonth; > > ELSE IF (I = 35) THEN > > MONTH_35 = MONTH_35 + nTotalMonth; > > ELSE IF (I = 36) THEN > > MONTH_36 = MONTH_36 + nTotalMonth; > > ELSE IF (I = 37) THEN > > MONTH_37 = MONTH_37 + nTotalMonth; > > ELSE IF (I = 38) THEN > > MONTH_38 = MONTH_38 + nTotalMonth; > > ELSE IF (I = 39) THEN > > MONTH_39 = MONTH_39 + nTotalMonth; > > ELSE IF (I = 40) THEN > > MONTH_40 = MONTH_40 + nTotalMonth; > > ELSE IF (I = 41) THEN > > MONTH_41 = MONTH_41 + nTotalMonth; > > ELSE IF (I = 42) THEN > > MONTH_42 = MONTH_42 + nTotalMonth; > > ELSE IF (I = 43) THEN > > MONTH_43 = MONTH_43 + nTotalMonth; > > ELSE IF (I = 44) THEN > > MONTH_44 = MONTH_44 + nTotalMonth; > > ELSE IF (I = 45) THEN > > MONTH_45 = MONTH_45 + nTotalMonth; > > ELSE IF (I = 46) THEN > > MONTH_46 = MONTH_46 + nTotalMonth; > > ELSE IF (I = 47) THEN > > MONTH_47 = MONTH_47 + nTotalMonth; > > ELSE IF (I = 48) THEN > > MONTH_48 = MONTH_48 + nTotalMonth; > > ELSE IF (I = 49) THEN > > MONTH_49 = MONTH_49 + nTotalMonth; > > ELSE IF (I = 50) THEN > > MONTH_50 = MONTH_50 + nTotalMonth; > > ELSE IF (I = 51) THEN > > MONTH_51 = MONTH_51 + nTotalMonth; > > ELSE IF (I = 52) THEN > > MONTH_52 = MONTH_52 + nTotalMonth; > > ELSE IF (I = 53) THEN > > MONTH_53 = MONTH_53 + nTotalMonth; > > ELSE IF (I = 54) THEN > > MONTH_54 = MONTH_54 + nTotalMonth; > > ELSE IF (I = 55) THEN > > MONTH_55 = MONTH_55 + nTotalMonth; > > ELSE IF (I = 56) THEN > > MONTH_56 = MONTH_56 + nTotalMonth; > > ELSE IF (I = 57) THEN > > MONTH_57 = MONTH_57 + nTotalMonth; > > ELSE IF (I = 58) THEN > > MONTH_58 = MONTH_58 + nTotalMonth; > > ELSE IF (I = 59) THEN > > MONTH_59 = MONTH_59 + nTotalMonth; > > ELSE IF (I = 60) THEN > > MONTH_60 = MONTH_60 + nTotalMonth; > > ELSE IF (I = 61) THEN > > MONTH_61 = MONTH_61 + nTotalMonth; > > ELSE IF (I = 62) THEN > > MONTH_62 = MONTH_62 + nTotalMonth; > > ELSE IF (I = 63) THEN > > MONTH_63 = MONTH_63 + nTotalMonth; > > ELSE IF (I = 64) THEN > > MONTH_64 = MONTH_64 + nTotalMonth; > > ELSE IF (I = 65) THEN > > MONTH_65 = MONTH_65 + nTotalMonth; > > ELSE IF (I = 65) THEN > > MONTH_66 = MONTH_66 + nTotalMonth; > > ELSE IF (I = 67) THEN > > MONTH_67 = MONTH_67 + nTotalMonth; > > ELSE IF (I = 68) THEN > > MONTH_68 = MONTH_68 + nTotalMonth; > > ELSE IF (I = 69) THEN > > MONTH_69 = MONTH_69 + nTotalMonth; > > ELSE IF (I = 70) THEN > > MONTH_70 = MONTH_70 + nTotalMonth; > > ELSE IF (I = 71) THEN > > MONTH_71 = MONTH_71 + nTotalMonth; > > ELSE IF (I = 72) THEN > > MONTH_72 = MONTH_72 + nTotalMonth; > > ELSE IF (I = 73) THEN > > MONTH_73 = MONTH_73 + nTotalMonth; > > ELSE IF (I = 74) THEN > > MONTH_74 = MONTH_74 + nTotalMonth; > > ELSE IF (I = 75) THEN > > MONTH_75 = MONTH_75 + nTotalMonth; > > ELSE IF (I = 76) THEN > > MONTH_76 = MONTH_76 + nTotalMonth; > > ELSE IF (I = 77) THEN > > MONTH_77 = MONTH_77 + nTotalMonth; > > ELSE IF (I = 78) THEN > > MONTH_78 = MONTH_78 + nTotalMonth; > > ELSE IF (I = 79) THEN > > MONTH_79 = MONTH_79 + nTotalMonth; > > ELSE IF (I = 80) THEN > > MONTH_80 = MONTH_80 + nTotalMonth; > > ELSE IF (I = 81) THEN > > MONTH_81 = MONTH_81 + nTotalMonth; > > ELSE IF (I = 82) THEN > > MONTH_82 = MONTH_82 + nTotalMonth; > > ELSE IF (I = 83) THEN > > MONTH_83 = MONTH_83 + nTotalMonth; > > ELSE IF (I = 84) THEN > > MONTH_84 = MONTH_84 + nTotalMonth; > > ELSE IF (I = 85) THEN > > MONTH_85 = MONTH_85 + nTotalMonth; > > ELSE IF (I = 86) THEN > > MONTH_86 = MONTH_86 + nTotalMonth; > > ELSE IF (I = 87) THEN > > MONTH_87 = MONTH_87 + nTotalMonth; > > ELSE IF (I = 88) THEN > > MONTH_88 = MONTH_88 + nTotalMonth; > > ELSE IF (I = 89) THEN > > MONTH_89 = MONTH_89 + nTotalMonth; > > ELSE IF (I = 90) THEN > > MONTH_90 = MONTH_90 + nTotalMonth; > > ELSE IF (I = 91) THEN > > MONTH_91 = MONTH_91 + nTotalMonth; > > ELSE IF (I = 92) THEN > > MONTH_92 = MONTH_92 + nTotalMonth; > > ELSE IF (I = 93) THEN > > MONTH_93 = MONTH_93 + nTotalMonth; > > ELSE IF (I = 94) THEN > > MONTH_94 = MONTH_94 + nTotalMonth; > > ELSE IF (I = 95) THEN > > MONTH_95 = MONTH_95 + nTotalMonth; > > ELSE IF (I = 96) THEN > > MONTH_96 = MONTH_96 + nTotalMonth; > > ELSE IF (I = 97) THEN > > MONTH_97 = MONTH_97 + nTotalMonth; > > ELSE IF (I = 98) THEN > > MONTH_98 = MONTH_98 + nTotalMonth; > > ELSE IF (I = 99) THEN > > MONTH_99 = MONTH_99 + nTotalMonth; > > ELSE IF (I = 100) THEN > > MONTH_100 = MONTH_100 + nTotalMonth; > > ELSE IF (I = 101) THEN > > MONTH_101 = MONTH_101 + nTotalMonth; > > ELSE IF (I = 102) THEN > > MONTH_102 = MONTH_102 + nTotalMonth; > > ELSE IF (I = 103) THEN > > MONTH_103 = MONTH_103 + nTotalMonth; > > ELSE IF (I = 104) THEN > > MONTH_104 = MONTH_104 + nTotalMonth; > > ELSE IF (I = 105) THEN > > MONTH_105 = MONTH_105 + nTotalMonth; > > ELSE IF (I = 106) THEN > > MONTH_106 = MONTH_106 + nTotalMonth; > > ELSE IF (I = 107) THEN > > MONTH_107 = MONTH_107 + nTotalMonth; > > ELSE IF (I = 108) THEN > > MONTH_108 = MONTH_108 + nTotalMonth; > > ELSE IF (I = 109) THEN > > MONTH_109 = MONTH_109 + nTotalMonth; > > ELSE IF (I = 110) THEN > > MONTH_110 = MONTH_110 + nTotalMonth; > > ELSE IF (I = 111) THEN > > MONTH_111 = MONTH_111 + nTotalMonth; > > ELSE IF (I = 112) THEN > > MONTH_112 = MONTH_112 + nTotalMonth; > > ELSE IF (I = 113) THEN > > MONTH_113 = MONTH_113 + nTotalMonth; > > ELSE IF (I = 114) THEN > > MONTH_114 = MONTH_114 + nTotalMonth; > > ELSE IF (I = 115) THEN > > MONTH_115 = MONTH_115 + nTotalMonth; > > ELSE IF (I = 116) THEN > > MONTH_116 = MONTH_116 + nTotalMonth; > > ELSE IF (I = 117) THEN > > MONTH_117 = MONTH_117 + nTotalMonth; > > ELSE IF (I = 118) THEN > > MONTH_118 = MONTH_118 + nTotalMonth; > > ELSE IF (I = 119) THEN > > MONTH_119 = MONTH_119 + nTotalMonth; > > ELSE IF (I = 120) THEN > > MONTH_120 = MONTH_120 + nTotalMonth; > > END > > > > I = I + 1; > > ... > > > > Thanks to all that can lend any insight, > > Mike > > > > > > [Non-text portions of this message have been removed] > > > > > > > > [Non-text portions of this message have been removed] >
