FbScript Parsing Bug
--------------------
Key: DNET-244
URL: http://tracker.firebirdsql.org/browse/DNET-244
Project: .NET Data provider
Issue Type: Bug
Components: ADO.NET Provider
Affects Versions: 2.5.0
Environment: Windows Vista SP1, Visual Studio 2008 SP1, .NET Framework
3.5 SP1, Firebird 1.5.5 Classic (Dialect 1)
Reporter: Phillip Cannon
Assignee: Jiri Cincura
I am trying to use the FbBatchExecution class to run a number SQL scripts that
create stored procedures in my database. However, when loading these scripts
into the FbScript class the SQL Statements do not appear to be parsed correctly
and the term character is left at the end. As a result the FbBatchExecution
fails with the following error:
FirebirdSql.Data.FirebirdClient.FbException: An exception was thrown when
executing command: CREATE OR ALTER PROCEDURE DAILYDEMANDROLLUPMONTHLY (FCTYPENO
Integer)
AS
declare variable VCURRENTPERIOD integer;
declare variable VCAL integer;
declare variable VFCTYPENO integer;
declare variable VITEMNO integer;
declare variable VLOOPITEM integer;
declare variable VLOOPCAL integer;
declare variable VLOOPFCTYPENO integer;
declare variable VFCSUM double precision;
declare variable vFORECAST_0 double precision;
declare variable vFORECAST_1 double precision;
declare variable vFORECAST_2 double precision;
declare variable vFORECAST_3 double precision;
declare variable vFORECAST_4 double precision;
declare variable vFORECAST_5 double precision;
declare variable vFORECAST_6 double precision;
declare variable vFORECAST_7 double precision;
declare variable vFORECAST_8 double precision;
declare variable vFORECAST_9 double precision;
declare variable vFORECAST_10 double precision;
declare variable vFORECAST_11 double precision;
declare variable vFORECAST_12 double precision;
declare variable vFORECAST_13 double precision;
declare variable vFORECAST_14 double precision;
declare variable vFORECAST_15 double precision;
declare variable vFORECAST_16 double precision;
declare variable vFORECAST_17 double precision;
declare variable vFORECAST_18 double precision;
declare variable vFORECAST_19 double precision;
declare variable vFORECAST_20 double precision;
declare variable vFORECAST_21 double precision;
declare variable vFORECAST_22 double precision;
declare variable vFORECAST_23 double precision;
declare variable vFORECAST_24 double precision;
declare variable vFORECAST_25 double precision;
declare variable vFORECAST_26 double precision;
declare variable vFORECAST_27 double precision;
declare variable vFORECAST_28 double precision;
declare variable vFORECAST_29 double precision;
declare variable vFORECAST_30 double precision;
declare variable vFORECAST_31 double precision;
declare variable vFORECAST_32 double precision;
declare variable vFORECAST_33 double precision;
declare variable vFORECAST_34 double precision;
declare variable vFORECAST_35 double precision;
declare variable vFORECAST_36 double precision;
declare variable vFORECAST_37 double precision;
declare variable vFORECAST_38 double precision;
declare variable vFORECAST_39 double precision;
declare variable vFORECAST_40 double precision;
declare variable vFORECAST_41 double precision;
declare variable vFORECAST_42 double precision;
declare variable vFORECAST_43 double precision;
declare variable vFORECAST_44 double precision;
declare variable vFORECAST_45 double precision;
declare variable vFORECAST_46 double precision;
declare variable vFORECAST_47 double precision;
declare variable vFORECAST_48 double precision;
declare variable vFORECAST_49 double precision;
declare variable vFORECAST_50 double precision;
declare variable vFORECAST_51 double precision;
begin
VCURRENTPERIOD = 0;
VCAL = 0;
VFCSUM = 0.00;
VFCTYPENO = 0;
VITEMNO = 0;
VLOOPITEM = -1;
VLOOPCAL = 0;
VLOOPFCTYPENO = 0;
vFORECAST_0 = 0.00;
vFORECAST_1 = 0.00;
vFORECAST_2 = 0.00;
vFORECAST_3 = 0.00;
vFORECAST_4 = 0.00;
vFORECAST_5 = 0.00;
vFORECAST_6 = 0.00;
vFORECAST_7 = 0.00;
vFORECAST_8 = 0.00;
vFORECAST_9 = 0.00;
vFORECAST_10 = 0.00;
vFORECAST_11 = 0.00;
vFORECAST_12 = 0.00;
vFORECAST_13 = 0.00;
vFORECAST_14 = 0.00;
vFORECAST_15 = 0.00;
vFORECAST_16 = 0.00;
vFORECAST_17 = 0.00;
vFORECAST_18 = 0.00;
vFORECAST_19 = 0.00;
vFORECAST_20 = 0.00;
vFORECAST_21 = 0.00;
vFORECAST_22 = 0.00;
vFORECAST_23 = 0.00;
vFORECAST_24 = 0.00;
vFORECAST_25 = 0.00;
vFORECAST_26 = 0.00;
vFORECAST_27 = 0.00;
vFORECAST_28 = 0.00;
vFORECAST_29 = 0.00;
vFORECAST_30 = 0.00;
vFORECAST_31 = 0.00;
vFORECAST_32 = 0.00;
vFORECAST_33 = 0.00;
vFORECAST_34 = 0.00;
vFORECAST_35 = 0.00;
vFORECAST_36 = 0.00;
vFORECAST_37 = 0.00;
vFORECAST_38 = 0.00;
vFORECAST_39 = 0.00;
vFORECAST_40 = 0.00;
vFORECAST_41 = 0.00;
vFORECAST_42 = 0.00;
vFORECAST_43 = 0.00;
vFORECAST_44 = 0.00;
vFORECAST_45 = 0.00;
vFORECAST_46 = 0.00;
vFORECAST_47 = 0.00;
vFORECAST_48 = 0.00;
vFORECAST_49 = 0.00;
vFORECAST_50 = 0.00;
vFORECAST_51 = 0.00;
select TYPEOFINTEGER
from CONFIGURATION
where CONFIGURATIONNO = 100
into :VCURRENTPERIOD;
for select ITEMNO, FORECASTTYPENO, CALENDARNO, sum(FCVALUE)
from ITEMDAILYFORECAST
where CALENDARNO >= :VCURRENTPERIOD
and FORECASTTYPENO = :FCTypeNo
group by ITEMNO, FORECASTTYPENO, CALENDARNO
order by ITEMNO, FORECASTTYPENO, CALENDARNO
into :VITEMNO, :VFCTYPENO, :VCAL, :VFCSUM
do
begin
if (:VLOOPITEM = -1) then
begin
VLOOPITEM = :VITEMNO;
end
if (:VITEMNO > :VLOOPITEM) then
begin
update ITEMFORECAST
set FORECAST_0 = :vFORECAST_0,
FORECAST_1 = :vFORECAST_1,
FORECAST_2 = :vFORECAST_2,
FORECAST_3 = :vFORECAST_3,
FORECAST_4 = :vFORECAST_4,
FORECAST_5 = :vFORECAST_5,
FORECAST_6 = :vFORECAST_6,
FORECAST_7 = :vFORECAST_7,
FORECAST_8 = :vFORECAST_8,
FORECAST_9 = :vFORECAST_9,
FORECAST_10 = :vFORECAST_10,
FORECAST_11 = :vFORECAST_11,
FORECAST_12 = :vFORECAST_12,
FORECAST_13 = :vFORECAST_13,
FORECAST_14 = :vFORECAST_14,
FORECAST_15 = :vFORECAST_15,
FORECAST_16 = :vFORECAST_16,
FORECAST_17 = :vFORECAST_17,
FORECAST_18 = :vFORECAST_18,
FORECAST_19 = :vFORECAST_19,
FORECAST_20 = :vFORECAST_20,
FORECAST_21 = :vFORECAST_21,
FORECAST_22 = :vFORECAST_22,
FORECAST_23 = :vFORECAST_23,
FORECAST_24 = :vFORECAST_24,
FORECAST_25 = :vFORECAST_25,
FORECAST_26 = :vFORECAST_26,
FORECAST_27 = :vFORECAST_27,
FORECAST_28 = :vFORECAST_28,
FORECAST_29 = :vFORECAST_29,
FORECAST_30 = :vFORECAST_30,
FORECAST_31 = :vFORECAST_31,
FORECAST_32 = :vFORECAST_32,
FORECAST_33 = :vFORECAST_33,
FORECAST_34 = :vFORECAST_34,
FORECAST_35 = :vFORECAST_35,
FORECAST_36 = :vFORECAST_36,
FORECAST_37 = :vFORECAST_37,
FORECAST_38 = :vFORECAST_38,
FORECAST_39 = :vFORECAST_39,
FORECAST_40 = :vFORECAST_40,
FORECAST_41 = :vFORECAST_41,
FORECAST_42 = :vFORECAST_42,
FORECAST_43 = :vFORECAST_43,
FORECAST_44 = :vFORECAST_44,
FORECAST_45 = :vFORECAST_45,
FORECAST_46 = :vFORECAST_46,
FORECAST_47 = :vFORECAST_47,
FORECAST_48 = :vFORECAST_48,
FORECAST_49 = :vFORECAST_49,
FORECAST_50 = :vFORECAST_50,
FORECAST_51 = :vFORECAST_51
where ITEMNO = :VLOOPITEM
and CALENDARNO = :VCURRENTPERIOD
and FORECASTTYPENO = :VLOOPFCTYPENO;
vFORECAST_0 = 0.00;
vFORECAST_1 = 0.00;
vFORECAST_2 = 0.00;
vFORECAST_3 = 0.00;
vFORECAST_4 = 0.00;
vFORECAST_5 = 0.00;
vFORECAST_6 = 0.00;
vFORECAST_7 = 0.00;
vFORECAST_8 = 0.00;
vFORECAST_9 = 0.00;
vFORECAST_10 = 0.00;
vFORECAST_11 = 0.00;
vFORECAST_12 = 0.00;
vFORECAST_13 = 0.00;
vFORECAST_14 = 0.00;
vFORECAST_15 = 0.00;
vFORECAST_16 = 0.00;
vFORECAST_17 = 0.00;
vFORECAST_18 = 0.00;
vFORECAST_19 = 0.00;
vFORECAST_20 = 0.00;
vFORECAST_21 = 0.00;
vFORECAST_22 = 0.00;
vFORECAST_23 = 0.00;
vFORECAST_24 = 0.00;
vFORECAST_25 = 0.00;
vFORECAST_26 = 0.00;
vFORECAST_27 = 0.00;
vFORECAST_28 = 0.00;
vFORECAST_29 = 0.00;
vFORECAST_30 = 0.00;
vFORECAST_31 = 0.00;
vFORECAST_32 = 0.00;
vFORECAST_33 = 0.00;
vFORECAST_34 = 0.00;
vFORECAST_35 = 0.00;
vFORECAST_36 = 0.00;
vFORECAST_37 = 0.00;
vFORECAST_38 = 0.00;
vFORECAST_39 = 0.00;
vFORECAST_40 = 0.00;
vFORECAST_41 = 0.00;
vFORECAST_42 = 0.00;
vFORECAST_43 = 0.00;
vFORECAST_44 = 0.00;
vFORECAST_45 = 0.00;
vFORECAST_46 = 0.00;
vFORECAST_47 = 0.00;
vFORECAST_48 = 0.00;
vFORECAST_49 = 0.00;
vFORECAST_50 = 0.00;
vFORECAST_51 = 0.00;
end
VLOOPITEM = :VITEMNO;
VLOOPCAL = :VCAL;
VLOOPFCTYPENO = :VFCTYPENO;
if (:VCAL = :VCURRENTPERIOD) then
begin
vFORECAST_0 = :VFCSUM;
end
else if (:VCAL = :VCURRENTPERIOD + 1) then
begin
vFORECAST_1 = :VFCSUM;
end
else if (:VCAL = :VCURRENTPERIOD + 2) then
begin
vFORECAST_2 = :VFCSUM;
end
else if (:VCAL = :VCURRENTPERIOD + 3) then
begin
vFORECAST_3 = :VFCSUM;
end
else if (:VCAL = :VCURRENTPERIOD + 4) then
begin
vFORECAST_4 = :VFCSUM;
end
else if (:VCAL = :VCURRENTPERIOD + 5) then
begin
vFORECAST_5 = :VFCSUM;
end
else if (:VCAL = :VCURRENTPERIOD + 6) then
begin
vFORECAST_6 = :VFCSUM;
end
else if (:VCAL = :VCURRENTPERIOD + 7) then
begin
vFORECAST_7 = :VFCSUM;
end
else if (:VCAL = :VCURRENTPERIOD + 8) then
begin
vFORECAST_8 = :VFCSUM;
end
else if (:VCAL = :VCURRENTPERIOD + 9) then
begin
vFORECAST_9 = :VFCSUM;
end
else if (:VCAL = :VCURRENTPERIOD + 10) then
begin
vFORECAST_10 = :VFCSUM;
end
else if (:VCAL = :VCURRENTPERIOD + 11) then
begin
vFORECAST_11 = :VFCSUM;
end
else if (:VCAL = :VCURRENTPERIOD + 12) then
begin
vFORECAST_12 = :VFCSUM;
end
else if (:VCAL = :VCURRENTPERIOD + 13) then
begin
vFORECAST_13 = :VFCSUM;
end
else if (:VCAL = :VCURRENTPERIOD + 14) then
begin
vFORECAST_14 = :VFCSUM;
end
else if (:VCAL = :VCURRENTPERIOD + 15) then
begin
vFORECAST_15 = :VFCSUM;
end
else if (:VCAL = :VCURRENTPERIOD + 16) then
begin
vFORECAST_16 = :VFCSUM;
end
else if (:VCAL = :VCURRENTPERIOD + 17) then
begin
vFORECAST_17 = :VFCSUM;
end
else if (:VCAL = :VCURRENTPERIOD + 18) then
begin
vFORECAST_18 = :VFCSUM;
end
else if (:VCAL = :VCURRENTPERIOD + 19) then
begin
vFORECAST_19 = :VFCSUM;
end
else if (:VCAL = :VCURRENTPERIOD + 20) then
begin
vFORECAST_20 = :VFCSUM;
end
else if (:VCAL = :VCURRENTPERIOD + 21) then
begin
vFORECAST_21 = :VFCSUM;
end
else if (:VCAL = :VCURRENTPERIOD + 22) then
begin
vFORECAST_22 = :VFCSUM;
end
else if (:VCAL = :VCURRENTPERIOD + 23) then
begin
vFORECAST_23 = :VFCSUM;
end
else if (:VCAL = :VCURRENTPERIOD + 24) then
begin
vFORECAST_24 = :VFCSUM;
end
else if (:VCAL = :VCURRENTPERIOD + 25) then
begin
vFORECAST_25 = :VFCSUM;
end
else if (:VCAL = :VCURRENTPERIOD + 26) then
begin
vFORECAST_26 = :VFCSUM;
end
else if (:VCAL = :VCURRENTPERIOD + 27) then
begin
vFORECAST_27 = :VFCSUM;
end
else if (:VCAL = :VCURRENTPERIOD + 28) then
begin
vFORECAST_28 = :VFCSUM;
end
else if (:VCAL = :VCURRENTPERIOD + 29) then
begin
vFORECAST_29 = :VFCSUM;
end
else if (:VCAL = :VCURRENTPERIOD + 30) then
begin
vFORECAST_30 = :VFCSUM;
end
else if (:VCAL = :VCURRENTPERIOD + 31) then
begin
vFORECAST_31 = :VFCSUM;
end
else if (:VCAL = :VCURRENTPERIOD + 32) then
begin
vFORECAST_32 = :VFCSUM;
end
else if (:VCAL = :VCURRENTPERIOD + 33) then
begin
vFORECAST_33 = :VFCSUM;
end
else if (:VCAL = :VCURRENTPERIOD + 34) then
begin
vFORECAST_34 = :VFCSUM;
end
else if (:VCAL = :VCURRENTPERIOD + 35) then
begin
vFORECAST_35 = :VFCSUM;
end
else if (:VCAL = :VCURRENTPERIOD + 36) then
begin
vFORECAST_36 = :VFCSUM;
end
else if (:VCAL = :VCURRENTPERIOD + 37) then
begin
vFORECAST_37 = :VFCSUM;
end
else if (:VCAL = :VCURRENTPERIOD + 38) then
begin
vFORECAST_38 = :VFCSUM;
end
else if (:VCAL = :VCURRENTPERIOD + 39) then
begin
vFORECAST_39 = :VFCSUM;
end
else if (:VCAL = :VCURRENTPERIOD + 40) then
begin
vFORECAST_40 = :VFCSUM;
end
else if (:VCAL = :VCURRENTPERIOD + 41) then
begin
vFORECAST_41 = :VFCSUM;
end
else if (:VCAL = :VCURRENTPERIOD + 42) then
begin
vFORECAST_42 = :VFCSUM;
end
else if (:VCAL = :VCURRENTPERIOD + 43) then
begin
vFORECAST_43 = :VFCSUM;
end
else if (:VCAL = :VCURRENTPERIOD + 44) then
begin
vFORECAST_44 = :VFCSUM;
end
else if (:VCAL = :VCURRENTPERIOD + 45) then
begin
vFORECAST_45 = :VFCSUM;
end
else if (:VCAL = :VCURRENTPERIOD + 46) then
begin
vFORECAST_46 = :VFCSUM;
end
else if (:VCAL = :VCURRENTPERIOD + 47) then
begin
vFORECAST_47 = :VFCSUM;
end
else if (:VCAL = :VCURRENTPERIOD + 48) then
begin
vFORECAST_48 = :VFCSUM;
end
else if (:VCAL = :VCURRENTPERIOD + 49) then
begin
vFORECAST_49 = :VFCSUM;
end
else if (:VCAL = :VCURRENTPERIOD + 50) then
begin
vFORECAST_50 = :VFCSUM;
end
else if (:VCAL = :VCURRENTPERIOD + 51) then
begin
vFORECAST_51 = :VFCSUM;
end
end
if (:VLOOPITEM <> 0) then
begin
update ITEMFORECAST
set FORECAST_0 = :vFORECAST_0,
FORECAST_1 = :vFORECAST_1,
FORECAST_2 = :vFORECAST_2,
FORECAST_3 = :vFORECAST_3,
FORECAST_4 = :vFORECAST_4,
FORECAST_5 = :vFORECAST_5,
FORECAST_6 = :vFORECAST_6,
FORECAST_7 = :vFORECAST_7,
FORECAST_8 = :vFORECAST_8,
FORECAST_9 = :vFORECAST_9,
FORECAST_10 = :vFORECAST_10,
FORECAST_11 = :vFORECAST_11,
FORECAST_12 = :vFORECAST_12,
FORECAST_13 = :vFORECAST_13,
FORECAST_14 = :vFORECAST_14,
FORECAST_15 = :vFORECAST_15,
FORECAST_16 = :vFORECAST_16,
FORECAST_17 = :vFORECAST_17,
FORECAST_18 = :vFORECAST_18,
FORECAST_19 = :vFORECAST_19,
FORECAST_20 = :vFORECAST_20,
FORECAST_21 = :vFORECAST_21,
FORECAST_22 = :vFORECAST_22,
FORECAST_23 = :vFORECAST_23,
FORECAST_24 = :vFORECAST_24,
FORECAST_25 = :vFORECAST_25,
FORECAST_26 = :vFORECAST_26,
FORECAST_27 = :vFORECAST_27,
FORECAST_28 = :vFORECAST_28,
FORECAST_29 = :vFORECAST_29,
FORECAST_30 = :vFORECAST_30,
FORECAST_31 = :vFORECAST_31,
FORECAST_32 = :vFORECAST_32,
FORECAST_33 = :vFORECAST_33,
FORECAST_34 = :vFORECAST_34,
FORECAST_35 = :vFORECAST_35,
FORECAST_36 = :vFORECAST_36,
FORECAST_37 = :vFORECAST_37,
FORECAST_38 = :vFORECAST_38,
FORECAST_39 = :vFORECAST_39,
FORECAST_40 = :vFORECAST_40,
FORECAST_41 = :vFORECAST_41,
FORECAST_42 = :vFORECAST_42,
FORECAST_43 = :vFORECAST_43,
FORECAST_44 = :vFORECAST_44,
FORECAST_45 = :vFORECAST_45,
FORECAST_46 = :vFORECAST_46,
FORECAST_47 = :vFORECAST_47,
FORECAST_48 = :vFORECAST_48,
FORECAST_49 = :vFORECAST_49,
FORECAST_50 = :vFORECAST_50,
FORECAST_51 = :vFORECAST_51
where ITEMNO = :VLOOPITEM
and CALENDARNO = :VCURRENTPERIOD
and FORECASTTYPENO = :VLOOPFCTYPENO;
end
suspend;
end^
Batch execution aborted
The returned message was: Dynamic SQL Error
SQL error code = -104
Token unknown - line 536, column 4
^
at FirebirdSql.Data.Isql.FbBatchExecution.Execute(Boolean autoCommit) in
D:\Library\Development\Tools\FirebirdClient\NETProvider\source\FirebirdSql\Data\Isql\FbBatchExecution.cs:line
455
at FirebirdSql.Data.Isql.FbBatchExecution.Execute() in
D:\Library\Development\Tools\FirebirdClient\NETProvider\source\FirebirdSql\Data\Isql\FbBatchExecution.cs:line
153
at BarloworldOptimus.Firebird.FirebirdQuery.ExecuteScript(String FileName)
in
D:\Projects\Internal\CommonComponents\BarloworldOptimus.Firebird\FirebirdQuery.cs:line
223
at ScheduleScriptRunner.WindowMain._backgroundWorker_DoWork(Object sender,
DoWorkEventArgs e) in
D:\Projects\Internal\ScheduleScriptRunner\ScheduleScriptRunner\WindowMain.xaml.cs:line
144
=== START SCRIPT ===
SET TERM ^ ;
CREATE OR ALTER PROCEDURE DAILYDEMANDROLLUPMONTHLY (FCTYPENO Integer)
AS
declare variable VCURRENTPERIOD integer;
declare variable VCAL integer;
declare variable VFCTYPENO integer;
declare variable VITEMNO integer;
declare variable VLOOPITEM integer;
declare variable VLOOPCAL integer;
declare variable VLOOPFCTYPENO integer;
declare variable VFCSUM double precision;
declare variable vFORECAST_0 double precision;
declare variable vFORECAST_1 double precision;
declare variable vFORECAST_2 double precision;
declare variable vFORECAST_3 double precision;
declare variable vFORECAST_4 double precision;
declare variable vFORECAST_5 double precision;
declare variable vFORECAST_6 double precision;
declare variable vFORECAST_7 double precision;
declare variable vFORECAST_8 double precision;
declare variable vFORECAST_9 double precision;
declare variable vFORECAST_10 double precision;
declare variable vFORECAST_11 double precision;
declare variable vFORECAST_12 double precision;
declare variable vFORECAST_13 double precision;
declare variable vFORECAST_14 double precision;
declare variable vFORECAST_15 double precision;
declare variable vFORECAST_16 double precision;
declare variable vFORECAST_17 double precision;
declare variable vFORECAST_18 double precision;
declare variable vFORECAST_19 double precision;
declare variable vFORECAST_20 double precision;
declare variable vFORECAST_21 double precision;
declare variable vFORECAST_22 double precision;
declare variable vFORECAST_23 double precision;
declare variable vFORECAST_24 double precision;
declare variable vFORECAST_25 double precision;
declare variable vFORECAST_26 double precision;
declare variable vFORECAST_27 double precision;
declare variable vFORECAST_28 double precision;
declare variable vFORECAST_29 double precision;
declare variable vFORECAST_30 double precision;
declare variable vFORECAST_31 double precision;
declare variable vFORECAST_32 double precision;
declare variable vFORECAST_33 double precision;
declare variable vFORECAST_34 double precision;
declare variable vFORECAST_35 double precision;
declare variable vFORECAST_36 double precision;
declare variable vFORECAST_37 double precision;
declare variable vFORECAST_38 double precision;
declare variable vFORECAST_39 double precision;
declare variable vFORECAST_40 double precision;
declare variable vFORECAST_41 double precision;
declare variable vFORECAST_42 double precision;
declare variable vFORECAST_43 double precision;
declare variable vFORECAST_44 double precision;
declare variable vFORECAST_45 double precision;
declare variable vFORECAST_46 double precision;
declare variable vFORECAST_47 double precision;
declare variable vFORECAST_48 double precision;
declare variable vFORECAST_49 double precision;
declare variable vFORECAST_50 double precision;
declare variable vFORECAST_51 double precision;
begin
VCURRENTPERIOD = 0;
VCAL = 0;
VFCSUM = 0.00;
VFCTYPENO = 0;
VITEMNO = 0;
VLOOPITEM = -1;
VLOOPCAL = 0;
VLOOPFCTYPENO = 0;
vFORECAST_0 = 0.00;
vFORECAST_1 = 0.00;
vFORECAST_2 = 0.00;
vFORECAST_3 = 0.00;
vFORECAST_4 = 0.00;
vFORECAST_5 = 0.00;
vFORECAST_6 = 0.00;
vFORECAST_7 = 0.00;
vFORECAST_8 = 0.00;
vFORECAST_9 = 0.00;
vFORECAST_10 = 0.00;
vFORECAST_11 = 0.00;
vFORECAST_12 = 0.00;
vFORECAST_13 = 0.00;
vFORECAST_14 = 0.00;
vFORECAST_15 = 0.00;
vFORECAST_16 = 0.00;
vFORECAST_17 = 0.00;
vFORECAST_18 = 0.00;
vFORECAST_19 = 0.00;
vFORECAST_20 = 0.00;
vFORECAST_21 = 0.00;
vFORECAST_22 = 0.00;
vFORECAST_23 = 0.00;
vFORECAST_24 = 0.00;
vFORECAST_25 = 0.00;
vFORECAST_26 = 0.00;
vFORECAST_27 = 0.00;
vFORECAST_28 = 0.00;
vFORECAST_29 = 0.00;
vFORECAST_30 = 0.00;
vFORECAST_31 = 0.00;
vFORECAST_32 = 0.00;
vFORECAST_33 = 0.00;
vFORECAST_34 = 0.00;
vFORECAST_35 = 0.00;
vFORECAST_36 = 0.00;
vFORECAST_37 = 0.00;
vFORECAST_38 = 0.00;
vFORECAST_39 = 0.00;
vFORECAST_40 = 0.00;
vFORECAST_41 = 0.00;
vFORECAST_42 = 0.00;
vFORECAST_43 = 0.00;
vFORECAST_44 = 0.00;
vFORECAST_45 = 0.00;
vFORECAST_46 = 0.00;
vFORECAST_47 = 0.00;
vFORECAST_48 = 0.00;
vFORECAST_49 = 0.00;
vFORECAST_50 = 0.00;
vFORECAST_51 = 0.00;
select TYPEOFINTEGER
from CONFIGURATION
where CONFIGURATIONNO = 100
into :VCURRENTPERIOD;
for select ITEMNO, FORECASTTYPENO, CALENDARNO, sum(FCVALUE)
from ITEMDAILYFORECAST
where CALENDARNO >= :VCURRENTPERIOD
and FORECASTTYPENO = :FCTypeNo
group by ITEMNO, FORECASTTYPENO, CALENDARNO
order by ITEMNO, FORECASTTYPENO, CALENDARNO
into :VITEMNO, :VFCTYPENO, :VCAL, :VFCSUM
do
begin
if (:VLOOPITEM = -1) then
begin
VLOOPITEM = :VITEMNO;
end
if (:VITEMNO > :VLOOPITEM) then
begin
update ITEMFORECAST
set FORECAST_0 = :vFORECAST_0,
FORECAST_1 = :vFORECAST_1,
FORECAST_2 = :vFORECAST_2,
FORECAST_3 = :vFORECAST_3,
FORECAST_4 = :vFORECAST_4,
FORECAST_5 = :vFORECAST_5,
FORECAST_6 = :vFORECAST_6,
FORECAST_7 = :vFORECAST_7,
FORECAST_8 = :vFORECAST_8,
FORECAST_9 = :vFORECAST_9,
FORECAST_10 = :vFORECAST_10,
FORECAST_11 = :vFORECAST_11,
FORECAST_12 = :vFORECAST_12,
FORECAST_13 = :vFORECAST_13,
FORECAST_14 = :vFORECAST_14,
FORECAST_15 = :vFORECAST_15,
FORECAST_16 = :vFORECAST_16,
FORECAST_17 = :vFORECAST_17,
FORECAST_18 = :vFORECAST_18,
FORECAST_19 = :vFORECAST_19,
FORECAST_20 = :vFORECAST_20,
FORECAST_21 = :vFORECAST_21,
FORECAST_22 = :vFORECAST_22,
FORECAST_23 = :vFORECAST_23,
FORECAST_24 = :vFORECAST_24,
FORECAST_25 = :vFORECAST_25,
FORECAST_26 = :vFORECAST_26,
FORECAST_27 = :vFORECAST_27,
FORECAST_28 = :vFORECAST_28,
FORECAST_29 = :vFORECAST_29,
FORECAST_30 = :vFORECAST_30,
FORECAST_31 = :vFORECAST_31,
FORECAST_32 = :vFORECAST_32,
FORECAST_33 = :vFORECAST_33,
FORECAST_34 = :vFORECAST_34,
FORECAST_35 = :vFORECAST_35,
FORECAST_36 = :vFORECAST_36,
FORECAST_37 = :vFORECAST_37,
FORECAST_38 = :vFORECAST_38,
FORECAST_39 = :vFORECAST_39,
FORECAST_40 = :vFORECAST_40,
FORECAST_41 = :vFORECAST_41,
FORECAST_42 = :vFORECAST_42,
FORECAST_43 = :vFORECAST_43,
FORECAST_44 = :vFORECAST_44,
FORECAST_45 = :vFORECAST_45,
FORECAST_46 = :vFORECAST_46,
FORECAST_47 = :vFORECAST_47,
FORECAST_48 = :vFORECAST_48,
FORECAST_49 = :vFORECAST_49,
FORECAST_50 = :vFORECAST_50,
FORECAST_51 = :vFORECAST_51
where ITEMNO = :VLOOPITEM
and CALENDARNO = :VCURRENTPERIOD
and FORECASTTYPENO = :VLOOPFCTYPENO;
vFORECAST_0 = 0.00;
vFORECAST_1 = 0.00;
vFORECAST_2 = 0.00;
vFORECAST_3 = 0.00;
vFORECAST_4 = 0.00;
vFORECAST_5 = 0.00;
vFORECAST_6 = 0.00;
vFORECAST_7 = 0.00;
vFORECAST_8 = 0.00;
vFORECAST_9 = 0.00;
vFORECAST_10 = 0.00;
vFORECAST_11 = 0.00;
vFORECAST_12 = 0.00;
vFORECAST_13 = 0.00;
vFORECAST_14 = 0.00;
vFORECAST_15 = 0.00;
vFORECAST_16 = 0.00;
vFORECAST_17 = 0.00;
vFORECAST_18 = 0.00;
vFORECAST_19 = 0.00;
vFORECAST_20 = 0.00;
vFORECAST_21 = 0.00;
vFORECAST_22 = 0.00;
vFORECAST_23 = 0.00;
vFORECAST_24 = 0.00;
vFORECAST_25 = 0.00;
vFORECAST_26 = 0.00;
vFORECAST_27 = 0.00;
vFORECAST_28 = 0.00;
vFORECAST_29 = 0.00;
vFORECAST_30 = 0.00;
vFORECAST_31 = 0.00;
vFORECAST_32 = 0.00;
vFORECAST_33 = 0.00;
vFORECAST_34 = 0.00;
vFORECAST_35 = 0.00;
vFORECAST_36 = 0.00;
vFORECAST_37 = 0.00;
vFORECAST_38 = 0.00;
vFORECAST_39 = 0.00;
vFORECAST_40 = 0.00;
vFORECAST_41 = 0.00;
vFORECAST_42 = 0.00;
vFORECAST_43 = 0.00;
vFORECAST_44 = 0.00;
vFORECAST_45 = 0.00;
vFORECAST_46 = 0.00;
vFORECAST_47 = 0.00;
vFORECAST_48 = 0.00;
vFORECAST_49 = 0.00;
vFORECAST_50 = 0.00;
vFORECAST_51 = 0.00;
end
VLOOPITEM = :VITEMNO;
VLOOPCAL = :VCAL;
VLOOPFCTYPENO = :VFCTYPENO;
if (:VCAL = :VCURRENTPERIOD) then
begin
vFORECAST_0 = :VFCSUM;
end
else if (:VCAL = :VCURRENTPERIOD + 1) then
begin
vFORECAST_1 = :VFCSUM;
end
else if (:VCAL = :VCURRENTPERIOD + 2) then
begin
vFORECAST_2 = :VFCSUM;
end
else if (:VCAL = :VCURRENTPERIOD + 3) then
begin
vFORECAST_3 = :VFCSUM;
end
else if (:VCAL = :VCURRENTPERIOD + 4) then
begin
vFORECAST_4 = :VFCSUM;
end
else if (:VCAL = :VCURRENTPERIOD + 5) then
begin
vFORECAST_5 = :VFCSUM;
end
else if (:VCAL = :VCURRENTPERIOD + 6) then
begin
vFORECAST_6 = :VFCSUM;
end
else if (:VCAL = :VCURRENTPERIOD + 7) then
begin
vFORECAST_7 = :VFCSUM;
end
else if (:VCAL = :VCURRENTPERIOD + 8) then
begin
vFORECAST_8 = :VFCSUM;
end
else if (:VCAL = :VCURRENTPERIOD + 9) then
begin
vFORECAST_9 = :VFCSUM;
end
else if (:VCAL = :VCURRENTPERIOD + 10) then
begin
vFORECAST_10 = :VFCSUM;
end
else if (:VCAL = :VCURRENTPERIOD + 11) then
begin
vFORECAST_11 = :VFCSUM;
end
else if (:VCAL = :VCURRENTPERIOD + 12) then
begin
vFORECAST_12 = :VFCSUM;
end
else if (:VCAL = :VCURRENTPERIOD + 13) then
begin
vFORECAST_13 = :VFCSUM;
end
else if (:VCAL = :VCURRENTPERIOD + 14) then
begin
vFORECAST_14 = :VFCSUM;
end
else if (:VCAL = :VCURRENTPERIOD + 15) then
begin
vFORECAST_15 = :VFCSUM;
end
else if (:VCAL = :VCURRENTPERIOD + 16) then
begin
vFORECAST_16 = :VFCSUM;
end
else if (:VCAL = :VCURRENTPERIOD + 17) then
begin
vFORECAST_17 = :VFCSUM;
end
else if (:VCAL = :VCURRENTPERIOD + 18) then
begin
vFORECAST_18 = :VFCSUM;
end
else if (:VCAL = :VCURRENTPERIOD + 19) then
begin
vFORECAST_19 = :VFCSUM;
end
else if (:VCAL = :VCURRENTPERIOD + 20) then
begin
vFORECAST_20 = :VFCSUM;
end
else if (:VCAL = :VCURRENTPERIOD + 21) then
begin
vFORECAST_21 = :VFCSUM;
end
else if (:VCAL = :VCURRENTPERIOD + 22) then
begin
vFORECAST_22 = :VFCSUM;
end
else if (:VCAL = :VCURRENTPERIOD + 23) then
begin
vFORECAST_23 = :VFCSUM;
end
else if (:VCAL = :VCURRENTPERIOD + 24) then
begin
vFORECAST_24 = :VFCSUM;
end
else if (:VCAL = :VCURRENTPERIOD + 25) then
begin
vFORECAST_25 = :VFCSUM;
end
else if (:VCAL = :VCURRENTPERIOD + 26) then
begin
vFORECAST_26 = :VFCSUM;
end
else if (:VCAL = :VCURRENTPERIOD + 27) then
begin
vFORECAST_27 = :VFCSUM;
end
else if (:VCAL = :VCURRENTPERIOD + 28) then
begin
vFORECAST_28 = :VFCSUM;
end
else if (:VCAL = :VCURRENTPERIOD + 29) then
begin
vFORECAST_29 = :VFCSUM;
end
else if (:VCAL = :VCURRENTPERIOD + 30) then
begin
vFORECAST_30 = :VFCSUM;
end
else if (:VCAL = :VCURRENTPERIOD + 31) then
begin
vFORECAST_31 = :VFCSUM;
end
else if (:VCAL = :VCURRENTPERIOD + 32) then
begin
vFORECAST_32 = :VFCSUM;
end
else if (:VCAL = :VCURRENTPERIOD + 33) then
begin
vFORECAST_33 = :VFCSUM;
end
else if (:VCAL = :VCURRENTPERIOD + 34) then
begin
vFORECAST_34 = :VFCSUM;
end
else if (:VCAL = :VCURRENTPERIOD + 35) then
begin
vFORECAST_35 = :VFCSUM;
end
else if (:VCAL = :VCURRENTPERIOD + 36) then
begin
vFORECAST_36 = :VFCSUM;
end
else if (:VCAL = :VCURRENTPERIOD + 37) then
begin
vFORECAST_37 = :VFCSUM;
end
else if (:VCAL = :VCURRENTPERIOD + 38) then
begin
vFORECAST_38 = :VFCSUM;
end
else if (:VCAL = :VCURRENTPERIOD + 39) then
begin
vFORECAST_39 = :VFCSUM;
end
else if (:VCAL = :VCURRENTPERIOD + 40) then
begin
vFORECAST_40 = :VFCSUM;
end
else if (:VCAL = :VCURRENTPERIOD + 41) then
begin
vFORECAST_41 = :VFCSUM;
end
else if (:VCAL = :VCURRENTPERIOD + 42) then
begin
vFORECAST_42 = :VFCSUM;
end
else if (:VCAL = :VCURRENTPERIOD + 43) then
begin
vFORECAST_43 = :VFCSUM;
end
else if (:VCAL = :VCURRENTPERIOD + 44) then
begin
vFORECAST_44 = :VFCSUM;
end
else if (:VCAL = :VCURRENTPERIOD + 45) then
begin
vFORECAST_45 = :VFCSUM;
end
else if (:VCAL = :VCURRENTPERIOD + 46) then
begin
vFORECAST_46 = :VFCSUM;
end
else if (:VCAL = :VCURRENTPERIOD + 47) then
begin
vFORECAST_47 = :VFCSUM;
end
else if (:VCAL = :VCURRENTPERIOD + 48) then
begin
vFORECAST_48 = :VFCSUM;
end
else if (:VCAL = :VCURRENTPERIOD + 49) then
begin
vFORECAST_49 = :VFCSUM;
end
else if (:VCAL = :VCURRENTPERIOD + 50) then
begin
vFORECAST_50 = :VFCSUM;
end
else if (:VCAL = :VCURRENTPERIOD + 51) then
begin
vFORECAST_51 = :VFCSUM;
end
end
if (:VLOOPITEM <> 0) then
begin
update ITEMFORECAST
set FORECAST_0 = :vFORECAST_0,
FORECAST_1 = :vFORECAST_1,
FORECAST_2 = :vFORECAST_2,
FORECAST_3 = :vFORECAST_3,
FORECAST_4 = :vFORECAST_4,
FORECAST_5 = :vFORECAST_5,
FORECAST_6 = :vFORECAST_6,
FORECAST_7 = :vFORECAST_7,
FORECAST_8 = :vFORECAST_8,
FORECAST_9 = :vFORECAST_9,
FORECAST_10 = :vFORECAST_10,
FORECAST_11 = :vFORECAST_11,
FORECAST_12 = :vFORECAST_12,
FORECAST_13 = :vFORECAST_13,
FORECAST_14 = :vFORECAST_14,
FORECAST_15 = :vFORECAST_15,
FORECAST_16 = :vFORECAST_16,
FORECAST_17 = :vFORECAST_17,
FORECAST_18 = :vFORECAST_18,
FORECAST_19 = :vFORECAST_19,
FORECAST_20 = :vFORECAST_20,
FORECAST_21 = :vFORECAST_21,
FORECAST_22 = :vFORECAST_22,
FORECAST_23 = :vFORECAST_23,
FORECAST_24 = :vFORECAST_24,
FORECAST_25 = :vFORECAST_25,
FORECAST_26 = :vFORECAST_26,
FORECAST_27 = :vFORECAST_27,
FORECAST_28 = :vFORECAST_28,
FORECAST_29 = :vFORECAST_29,
FORECAST_30 = :vFORECAST_30,
FORECAST_31 = :vFORECAST_31,
FORECAST_32 = :vFORECAST_32,
FORECAST_33 = :vFORECAST_33,
FORECAST_34 = :vFORECAST_34,
FORECAST_35 = :vFORECAST_35,
FORECAST_36 = :vFORECAST_36,
FORECAST_37 = :vFORECAST_37,
FORECAST_38 = :vFORECAST_38,
FORECAST_39 = :vFORECAST_39,
FORECAST_40 = :vFORECAST_40,
FORECAST_41 = :vFORECAST_41,
FORECAST_42 = :vFORECAST_42,
FORECAST_43 = :vFORECAST_43,
FORECAST_44 = :vFORECAST_44,
FORECAST_45 = :vFORECAST_45,
FORECAST_46 = :vFORECAST_46,
FORECAST_47 = :vFORECAST_47,
FORECAST_48 = :vFORECAST_48,
FORECAST_49 = :vFORECAST_49,
FORECAST_50 = :vFORECAST_50,
FORECAST_51 = :vFORECAST_51
where ITEMNO = :VLOOPITEM
and CALENDARNO = :VCURRENTPERIOD
and FORECASTTYPENO = :VLOOPFCTYPENO;
end
suspend;
end^
=== END SCRIPT ===
For this script to compile create a database with the following tables:
CREATE TABLE CONFIGURATION (
CONFIGURATIONNO Integer NOT NULL,
DESCRIPTION Varchar(60),
TYPEOFSTRING Varchar(20),
TYPEOFINTEGER Integer,
TYPEOFDATE Date,
TYPEOFFLOAT Double precision,
LOC_DEFAULT Char(1) DEFAULT 'N',
TYPEOFLONGSTRING Varchar(255),
CONSTRAINT PKCONFIGURATION PRIMARY KEY (CONFIGURATIONNO)
);
CREATE TABLE ITEMDAILYFORECAST (
ITEMDAILYFORECASTNO Integer NOT NULL,
ITEMNO Integer NOT NULL,
FORECASTTYPENO Integer NOT NULL,
FORECASTDATE Date NOT NULL,
FCVALUE Double precision DEFAULT 0,
LOCATIONNO Integer NOT NULL,
CALENDARNO Integer NOT NULL,
CONSTRAINT PK_ITEMDAILYFORECAST PRIMARY KEY (ITEMDAILYFORECASTNO)
);
CREATE TABLE ITEMFORECAST (
ITEMFORECASTNO Integer NOT NULL,
ITEMNO Integer NOT NULL,
CALENDARNO Integer NOT NULL,
FORECASTTYPENO Integer NOT NULL,
FCHISTORY_13 Double precision,
FCHISTORY_12 Double precision,
FCHISTORY_11 Double precision,
FCHISTORY_10 Double precision,
FCHISTORY_9 Double precision,
FCHISTORY_8 Double precision,
FCHISTORY_7 Double precision,
FCHISTORY_6 Double precision,
FCHISTORY_5 Double precision,
FCHISTORY_4 Double precision,
FCHISTORY_3 Double precision,
FCHISTORY_2 Double precision,
FCHISTORY_1 Double precision,
FORECAST_0 Double precision NOT NULL,
FORECAST_1 Double precision,
FORECAST_2 Double precision,
FORECAST_3 Double precision,
FORECAST_4 Double precision,
FORECAST_5 Double precision,
FORECAST_6 Double precision,
FORECAST_7 Double precision,
FORECAST_8 Double precision,
FORECAST_9 Double precision,
FORECAST_10 Double precision,
FORECAST_11 Double precision,
FORECAST_12 Double precision,
FORECAST_13 Double precision,
FORECAST_14 Double precision,
FORECAST_15 Double precision,
FORECAST_16 Double precision,
FORECAST_17 Double precision,
FORECAST_18 Double precision,
FORECAST_19 Double precision,
FORECAST_20 Double precision,
FORECAST_21 Double precision,
FORECAST_22 Double precision,
FORECAST_23 Double precision,
FORECAST_24 Double precision,
FORECAST_25 Double precision,
FORECAST_26 Double precision,
FORECAST_27 Double precision,
FORECAST_28 Double precision,
FORECAST_29 Double precision,
FORECAST_30 Double precision,
FORECAST_31 Double precision,
FORECAST_32 Double precision,
FORECAST_33 Double precision,
FORECAST_34 Double precision,
FORECAST_35 Double precision,
FORECAST_36 Double precision,
FORECAST_37 Double precision,
FORECAST_38 Double precision,
FORECAST_39 Double precision,
FORECAST_40 Double precision,
FORECAST_41 Double precision,
FORECAST_42 Double precision,
FORECAST_43 Double precision,
FORECAST_44 Double precision,
FORECAST_45 Double precision,
FORECAST_46 Double precision,
FORECAST_47 Double precision,
FORECAST_48 Double precision,
FORECAST_49 Double precision,
FORECAST_50 Double precision,
FORECAST_51 Double precision,
LOCATIONNO Integer NOT NULL,
CONSTRAINT PK_ITEMFORECASTNO PRIMARY KEY (ITEMFORECASTNO)
);
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://tracker.firebirdsql.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
------------------------------------------------------------------------------
Crystal Reports - New Free Runtime and 30 Day Trial
Check out the new simplified licensing option that enables unlimited
royalty-free distribution of the report engine for externally facing
server and web deployment.
http://p.sf.net/sfu/businessobjects
_______________________________________________
Firebird-net-provider mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/firebird-net-provider
