Thanks for your reply.

MONTH_* are not fields in a table, they are output parameters of my stored procedure.

Mike

----- Original Message -----
From: thp_pkmi
To: firebird-support@yahoogroups.com
Sent: Tuesday, June 18, 2013 10:52 PM
Subject: [firebird-support] Re: Does Firebird have a way to reference similar named fields? (Like MONTH_1 thru MONTH_120)



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 firebird-support@yahoogroups.com, "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: firebird-support@yahoogroups.com
> 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 firebird-support@yahoogroups.com, "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]
>





[Non-text portions of this message have been removed]