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]
