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]
