Sqlite sum error

Moderator: Rathinagiri

User avatar
jairpinho
Posts: 301
Joined: Mon Jul 18, 2011 5:36 pm
Location: Rio Grande do Sul - Brasil
Has thanked: 3 times
Been thanked: 10 times
Contact:

Re: Sqlite sum error

Post by jairpinho » Mon Jun 17, 2019 3:39 am

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
Jair Pinho
HMG ALTA REVOLUÇÃO xBASE
HMG xBASE REVOLUTION HIGH
http://www.hmgforum.com.br

User avatar
mustafa
Posts: 824
Joined: Fri Mar 20, 2009 11:38 am
DBs Used: DBF
Location: Alicante - Spain
Been thanked: 117 times
Contact:

Post by mustafa » Mon Jun 17, 2019 2:12 pm

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
Attachments
new_modified.zip
(6.18 KiB) Downloaded 12 times

User avatar
jairpinho
Posts: 301
Joined: Mon Jul 18, 2011 5:36 pm
Location: Rio Grande do Sul - Brasil
Has thanked: 3 times
Been thanked: 10 times
Contact:

Post by jairpinho » Mon Jun 17, 2019 6:00 pm

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
Jair Pinho
HMG ALTA REVOLUÇÃO xBASE
HMG xBASE REVOLUTION HIGH
http://www.hmgforum.com.br

Post Reply