Page 2 of 2

Re: Sqlite sum error

Posted: Mon Jun 17, 2019 3:39 am
by jairpinho
mustafa wrote: Sun Jun 16, 2019 1:07 pm Hola amigos:
Siguiendo con los experimentos con SQLite
La suma de cantidades económicas de precios.
No consigo que en los GRID y en el Listado PDF
las cantidades económicas que terminan en "0"
salgan , vean la muestra sale 3,8 cuando tendía
que ser 3,80 ó 4,0 cuando tendría que ser 4,00
el problema de la "," de los Euros no es el problema
porque si no se aplica STRTRAN ( precio , '.',',')
sale lo mismo falta el "0"
También he aplicado --> ROUND( precio, 2 ) , indicando
que son 2 decimales, solo pasa con los "0" ?

Si alguien puede indicar alguna solución, agradecido
Un cordial saludo.
Mustafa

*---------------------------- Google ---------------------------------------*

Hello friends:
Continuing with the experiments with SQLite
The sum of economic quantities of prices.
I do not get that in the GRID and in the PDF List
the economic amounts that end in "0"
come out, see the sample comes out 3.8 when I tended
that would be 3.80 or 4.0 when it would have to be 4.00
the problem of the "," of the Euros is not the problem
because if STRTRAN is not applied ( precio , '.', ',')
the same thing is missing the "0"
I have also applied -> ROUND ( precio, 2), indicating
which are 2 decimals, only happens with the "0"?

If someone can indicate some solution, grateful
A cordial greeting.
Mustafa
ola Mustafá utilizo una función para tratar variables tipo moneda en sql sigue

english

Hello Mustafa I use a function to treat currency type variables in sql follows



Code: Select all


 poner   = "aCurRow[1],aCurRow[2],aCurRow[3], MoedaMysql( VAL(aCurRow[4])),aCurRow[5]"  //   La coma "," decimal para €


*************************************************************************************************************************************
Function MoedaMysql(nVar)
*************************************************************************************************************************************
  
  cTotal := ALLTRIM(TransForm( nVar , "9999,999.99"))
 //Msginfo("nVar Entrada " + cTotal ) 
 
  IF LEN(cTotal) <= 6 
    cTotal := Alltrim(StrTran( cTotal , ".", "," ,1,1 ))
  ENDIF
  
  IF LEN(cTotal) >= 7

    cTotal := Alltrim(StrTran( cTotal , ".", "," ,1,1 ))
    cTotal := Alltrim(StrTran( cTotal , ",", "." ,1,1 ))
  ENDIF
     // Msginfo("nVar saida " + cTotal )
Return cTotal

Re: Sqlite sum error

Posted: Mon Jun 17, 2019 2:12 pm
by mustafa
Hola Jair Pinho "jairpinho"
Todo perfecto
en el Prg de Listado "Repor_4.prg"
he modificado
Anulado
* @ nCurLine, nCurCol PRINT STRTRAN ( PRECIO , ".",",") FONT cFontName SIZE nFontSize

Por
@ nCurLine, nCurCol PRINT MoedaMysql( VAL(PRECIO)) FONT cFontName SIZE nFontSize
y esta saliendo todo perfecto
Muchas Gracias amigo
muito obrigado amigo
Saludos
Mustafa
*---------------------------------------- Google
Hello Jair Pinho "jairpinho"
All perfect
in the Listing Prg "Repor_4.prg"
I have modified
Canceled
* @ nCurLine, nCurCol PRINT STRTRAN (PRICE, ".", ",") FONT cFontName SIZE nFontSize
By
    @ nCurLine, nCurCol PRINT MoedaMysql (VAL (PRICE)) FONT cFontName SIZE nFontSize
and everything is coming out perfect
Thank you very much friend
regards
muito obrigado amigo
Mustafa

Re: Sqlite sum error

Posted: Mon Jun 17, 2019 6:00 pm
by jairpinho
karweru wrote: Mon Apr 02, 2018 7:28 am Hi Serge, Dragancesu,

The table:

CREATE TABLE `Vclear` (
`T_TYPE` VARCHAR ( 3 ),
`T_DOC` VARCHAR ( 4 ),
`T_REF` VARCHAR ( 20 ),
`T_DATE` DATE ( 8 ),
`T_STAMP` VARCHAR ( 20 ),
`VC_TYPE` VARCHAR ( 10 ),
`VC_DATE` DATE ( 8 ),
`VC_STAMP` VARCHAR ( 15 ),
`VC_PERIOD` FLOAT ( 2 , 0 ),
`ACCOUNT_CODE` VARCHAR ( 20 ),
`VKEY_CODE` VARCHAR ( 20 ),
`VKEY_INFO` VARCHAR ( 40 ),
`VC_AMOUNT` FLOAT ( 15 , 2 ),
PRIMARY KEY(`T_DOC`,`T_REF`,`T_STAMP`,`VC_STAMP`,`ACCOUNT_CODE`,`VKEY_CODE`)
);

The querry:

SELECT TOTAL(vc_amount) FROM Vclear;

The data is as below,...the expected result is Zero, not 2.32830643653870000

data.png
Hello Gilbert could post the complete project of this problem with the database for me to do the tests