He everyone
we do have a big problem with very large numbers in real and money fields.
We use mSql 2.0.11 to program an information application for one of our
customers and there is one script which calculates totals for different
groups of articels. This script provides wrong totals. If I import the data
into Excel everything works fine, but the script, which should insert the
totals into the main table just inserts garbage.
PLEASE HELP
this is urgent.
I included the whole script at the end of this message.
Stefan Immel
MultiServicesNetworks
|N |O |C |
--|--|--|--|-------------
| GROVE
Network Operation Center
http://www.grove.de
/* Wechsel FB */
funct wecFB ()
{
echo("\nSumme FB @altFB\n");
@doq = "insert into main values(";
@doq = @doq + "@altFIR,";
@doq = @doq + "'@altLAN',";
@doq = @doq + "@altVL,'@altVLN',";
@doq = @doq + "@altFB,'@altFBN',";
@doq = @doq + "-1,'Gesamt','','','',";
@doq = @doq + "@altGAT,'@altGATN',";
@doq = @doq + "'@altTYP','@altTYPN',";
@doq = @doq + "@sFBMNGVJ,@sFBUMSOFVJ,@sFBUMSMFVJ,";
@doq = @doq + "@sFBMNGLJ,@sFBUMSOFLJ,@sFBUMSMFLJ)";
msqlQuery(@sock,@doq);
@altFB = @row[4];
@altFBN = @row[5];
@sFBMNGVJ = (real)0;
@sFBMNGLJ = (real)0;
@sFBUMSOFVJ = (real)0;
@sFBUMSMFVJ = (real)0;
@sFBUMSOFLJ = (real)0;
@sFBUMSMFLJ = (real)0;
}
/* Wechsel VL */
funct wecVL ()
{
echo("\nSumme VL @altVL\n");
@doq = "insert into main values(";
@doq = @doq + "@altFIR,";
@doq = @doq + "'@altLAN',";
@doq = @doq + "@altVL,'@altVLN',";
@doq = @doq + "-1,'Gesamt',";
@doq = @doq + "-1,'Gesamt','','','',";
@doq = @doq + "@altGAT,'@altGATN',";
@doq = @doq + "'@altTYP','@altTYPN',";
@doq = @doq + "@sVLMNGVJ,@sVLUMSOFVJ,@sVLUMSMFVJ,";
@doq = @doq + "@sVLMNGLJ,@sVLUMSOFLJ,@sVLUMSMFLJ)";
msqlQuery(@sock,@doq);
@altVL = @row[2];
@altVLN = @row[3];
@sVLMNGVJ = (real)0;
@sVLMNGLJ = (real)0;
@sVLUMSOFVJ = (real)0;
@sVLUMSMFVJ = (real)0;
@sVLUMSOFLJ = (real)0;
@sVLUMSMFLJ = (real)0;
}
/* Wechsel LAN */
funct wecLAN ()
{
echo("\nSumme LAN @altLAN\n");
@doq = "insert into main values(";
@doq = @doq + "@altFIR,";
@doq = @doq + "'@altLAN',";
@doq = @doq + "-1,'Gesamt',";
@doq = @doq + "-1,'Gesamt',";
@doq = @doq + "-1,'Gesamt','','','',";
@doq = @doq + "@altGAT,'@altGATN',";
@doq = @doq + "'@altTYP','@altTYPN',";
@doq = @doq + "@sLANMNGVJ,@sLANUMSOFVJ,@sLANUMSMFVJ,";
@doq = @doq + "@sLANMNGLJ,@sLANUMSOFLJ,@sLANUMSMFLJ)";
msqlQuery(@sock,@doq);
@altLAN = @row[1];
@sLANMNGVJ = (real)0;
@sLANMNGLJ = (real)0;
@sLANUMSOFVJ = (real)0;
@sLANUMSMFVJ = (real)0;
@sLANUMSOFLJ = (real)0;
@sLANUMSMFLJ = (real)0;
}
/* Wechsel FIR */
funct wecFIR ()
{
echo("\nSumme FIR @altFIR\n");
@doq = "insert into main values(";
@doq = @doq + "@altFIR,";
@doq = @doq + "'###',";
@doq = @doq + "-1,'Gesamt',";
@doq = @doq + "-1,'Gesamt',";
@doq = @doq + "-1,'Gesamt','','','',";
@doq = @doq + "@altGAT,'@altGATN',";
@doq = @doq + "'@altTYP','@altTYPN',";
@doq = @doq + "@sFIRMNGVJ,@sFIRUMSOFVJ,@sFIRUMSMFVJ,";
@doq = @doq + "@sFIRMNGLJ,@sFIRUMSOFLJ,@sFIRUMSMFLJ)";
msqlQuery(@sock,@doq);
@altFIR = @row[0];
@sFIRMNGVJ = (real)0;
@sFIRMNGLJ = (real)0;
@sFIRUMSOFVJ = (real)0;
@sFIRUMSMFVJ = (real)0;
@sFIRUMSOFLJ = (real)0;
@sFIRUMSMFLJ = (real)0;
}
/* Zum Server verbinden */
echo("Verbindung zum Server\n");
@sock=msqlConnect();
/* Öffnen der Datenbank */
echo("Oeffnen der Datenbank\n");
msqlSelectDB(@sock,"intra");
/* Löschen der Gesamtsummen */
echo("Loeschen der Gesamtsummen\n");
msqlQuery(@sock,"delete from main where KD = -1");
/* Öffnen der Tabelle Main nach Gattung, Typ, VL, FB */
echo("Oeffnen der Tabelle\n");
msqlQuery(@sock,"select * from main where KD > -1 order by GAT, TYP, FIR,
LAN, VL, FB");
@result=msqlStoreResult();
/* Anzahl der Zeilen ermitteln */
@num=msqlNumRows(@result);
/* Zähler auf 0 setzen */
echo("Init der Variablen\n");
@i=0;
/* Summenfelder init Gesamt */
@sMNGVJ = (real)0.000;
@sMNGLJ = (real)0.000;
@sUMSOFVJ = (real)0;
@sUMSMFVJ = (real)0;
@sUMSOFLJ = (real)0;
@sUMSMFLJ = (real)0;
/* Summenfelder init FIR */
@sFIRMNGVJ = (real)0.000;
@sFIRMNGLJ = (real)0.000;
@sFIRUMSOFVJ = (real)0;
@sFIRUMSMFVJ = (real)0;
@sFIRUMSOFLJ = (real)0;
@sFIRUMSMFLJ = (real)0;
/* Summenfelder init LAN */
@sLANMNGVJ = (real)0.000;
@sLANMNGLJ = (real)0.000;
@sLANUMSOFVJ = (real)0;
@sLANUMSMFVJ = (real)0;
@sLANUMSOFLJ = (real)0;
@sLANUMSMFLJ = (real)0;
/* Summenfelder init VL */
@sVLMNGVJ = (real)0.000;
@sVLMNGLJ = (real)0.000;
@sVLUMSOFVJ = (real)0;
@sVLUMSMFVJ = (real)0;
@sVLUMSOFLJ = (real)0;
@sVLUMSMFLJ = (real)0;
/* Summenfelder init FB */
@sFBMNGVJ = (real)0.000;
@sFBMNGLJ = (real)0.000;
@sFBUMSOFVJ = (real)0;
@sFBUMSMFVJ = (real)0;
@sFBUMSOFLJ = (real)0;
@sFBUMSMFLJ = (real)0;
/* Felder für Gruppenwechsel init */
@altFIR = 0;
@altLAN ="";
@altVL = 0;
@altVLN = "";
@altFB = 0;
@altFBN = "";
@altGAT = 0;
@altGATN = "";
@altTYP = "";
@altTYPN = "";
@start = 0;
/* Haupt verarbeitungschleife */
echo("Ermitteln der Summen laeuft\n");
while (@i < @num)
{
echo(".");
@row = msqlFetchRow(@result);
if (@start == 0)
{
@altFIR = @row[0];
@altLAN = @row[1];
@altVL = @row[2];
@altVLN = @row[3];
@altFB = @row[4];
@altFBN = @row[5];
@altGAT = @row[11];
@altGATN = @row[12];
@altTYP = @row[13];
@altTYPN = @row[14];
@start = 1;
}
if (@altGAT != @row[11] || @altTYP != @row[13])
{
wecFB();
wecVL();
wecLAN();
wecFIR();
@doq = "insert into main values(";
@doq = @doq + "-1,";
@doq = @doq + "'###',";
@doq = @doq + "-1,'Gesamt',";
@doq = @doq + "-1,'Gesamt',";
@doq = @doq + "-1,'Gesamt','','','',";
@doq = @doq + "@altGAT,'@altGATN',";
@doq = @doq + "'@altTYP','@altTYPN',";
@doq = @doq + "@sMNGVJ,@sUMSOFVJ,@sUMSMFVJ,";
@doq = @doq + "@sMNGLJ,@sUMSOFLJ,@sUMSMFLJ)";
msqlQuery(@sock,@doq);
@altGAT = @row[11];
@altGATN = @row[12];
@altTYP = @row[13];
@altTYPN = @row[14];
@sMNGVJ = (real)0;
@sMNGLJ = (real)0;
@sUMSOFVJ = (real)0;
@sUMSMFVJ = (real)0;
@sUMSOFLJ = (real)0;
@sUMSMFLJ = (real)0;
}
if (@altFIR != @row[0])
{
wecFB();
wecVL();
wecLAN();
wecFIR();
}
if (@altLAN != @row[1])
{
wecFB();
wecVL();
wecLAN();
}
if (@altVL != @row[2])
{
wecFB();
wecVL();
}
if (@altFB != @row[4])
{
wecFB();
}
@sMNGVJ = @sMNGVJ + (real)@row[15];
@sUMSOFVJ = @sUMSOFVJ + (real)@row[16];
@sUMSMFVJ = @sUMSMFVJ + (real)@row[17];
@sMNGLJ = @sMNGLJ + (real)@row[18];
@sUMSOFLJ = @sUMSOFLJ + (real)@row[19];
@sUMSMFLJ = @sUMSMFLJ + (real)@row[20];
@sFBMNGVJ = @sFBMNGVJ + (real)@row[15];
@sFBUMSOFVJ = @sFBUMSOFVJ + (real)@row[16];
@sFBUMSMFVJ = @sFBUMSMFVJ + (real)@row[17];
@sFBMNGLJ = @sFBMNGLJ + (real)@row[18];
@sFBUMSOFLJ = @sFBUMSOFLJ + (real)@row[19];
@sFBUMSMFLJ = @sFBUMSMFLJ + (real)@row[20];
@sVLMNGVJ = @sVLMNGVJ + (real)@row[15];
@sVLUMSOFVJ = @sVLUMSOFVJ + (real)@row[16];
@sVLUMSMFVJ = @sVLUMSMFVJ + (real)@row[17];
@sVLMNGLJ = @sVLMNGLJ + (real)@row[18];
@sVLUMSOFLJ = @sVLUMSOFLJ + (real)@row[19];
@sVLUMSMFLJ = @sVLUMSMFLJ + (real)@row[20];
@sLANMNGVJ = @sLANMNGVJ + (real)@row[15];
@sLANUMSOFVJ = @sLANUMSOFVJ + (real)@row[16];
@sLANUMSMFVJ = @sLANUMSMFVJ + (real)@row[17];
@sLANMNGLJ = @sLANMNGLJ + (real)@row[18];
@sLANUMSOFLJ = @sLANUMSOFLJ + (real)@row[19];
@sLANUMSMFLJ = @sLANUMSMFLJ + (real)@row[20];
@sFIRMNGVJ = @sFIRMNGVJ + (real)@row[15];
@sFIRUMSOFVJ = @sFIRUMSOFVJ + (real)@row[16];
@sFIRUMSMFVJ = @sFIRUMSMFVJ + (real)@row[17];
@sFIRMNGLJ = @sFIRMNGLJ + (real)@row[18];
@sFIRUMSOFLJ = @sFIRUMSOFLJ + (real)@row[19];
@sFIRUMSMFLJ = @sFIRUMSMFLJ + (real)@row[20];
@i++;
}
wecFB();
wecVL();
wecLAN();
wecFIR();
@doq = "insert into main values(";
@doq = @doq + "-1,";
@doq = @doq + "'###',";
@doq = @doq + "-1,'Gesamt',";
@doq = @doq + "-1,'Gesamt',";
@doq = @doq + "-1,'Gesamt','','','',";
@doq = @doq + "@altGAT,'@altGATN',";
@doq = @doq + "'@altTYP','@altTYPN',";
@doq = @doq + "@sMNGVJ,@sUMSOFVJ,@sUMSMFVJ,";
@doq = @doq + "@sMNGLJ,@sUMSOFLJ,@sUMSMFLJ)";
msqlQuery(@sock,@doq);
echo("\n");
-------------------------------------------------------------------------
To unsubscribe, go to http://www.Hughes.com.au/extras/email/
This archive was generated by hypermail 2b30 : Mon Mar 04 2002 - 09:04:03 EST