consulta mysql y tdolphin

[kpidata]

Estimados, he tratado de hacer la siguiente consulta, probe varios ejemplos de mysql, que vi o trate de adaptar y me arrojo error o me boto por error de sintaxis.. asi que llegue a esta opción,,, del tipo dbf, alguien me puede tirar una mano, de como hacer un conteo o suma de registros que cumplan cierta opcion.

cExt_Otr1:=xServer:Query("SELECT * FROM ext_otr1 WHERE fec_ing >='"+Dtos(cFecha1)+"' and fec_ing<='"+Dtos(cFecha2)+"' ORDER BY ite_rec")
cExt_Otr1:GoTop()

Do While !cExt_Otr1:EOF()
If !empty(cod_text)
cuenta++
Endif
cExt_Otr1:SKIP()
Enddo
cExt_Otr1:GoTop()

MsgInfo(Str(cuenta))

[horacio]

Quizás con count(*) si quieres contar los registros, sum( nCampoNum ) para sumar.

Saludos

[kpidata]

Estimado, probe esto.

Cuenta:=cExt_Otr1:=xServer:Query("SELECT COUNT(cod_text) FROM ext_otr1 WHERE fec_ing >='"+Dtos(cFecha1)+"' and fec_ing<='"+Dtos(cFecha2)+"' ORDER BY ite_rec")

MsgInfo(Str(cuenta))

probe eso y no funciono

[acuellar]

Kpidata intenta asi:

Code: Select all  Expand view

cExt_Otr1:=xServer:Query("SELECT * FROM ext_otr1 WHERE fec_ing >='"+Dtos(cFecha1)+"' and fec_ing<='"+Dtos(cFecha2)+"' ORDER BY ite_rec")
Cuenta:=cExt_Otr1:nRecCount
?Cuenta
cExt_Otr1:GoTop()
 

[Armando]

Kapidata:

Intenta con el siguiente código:

Code: Select all  Expand view

cExt_Otr1:=xServer:Query("SELECT *,COUNT(*) AS nNumRegs  FROM ext_otr1 WHERE fec_ing >='"+Dtos(cFecha1)+"' and fec_ing<='"+Dtos(cFecha2)+"' ORDER BY ite_rec")
cExt_Otr1:GoTop()
 

y en la variable nNumRegs debes obtener el número total de registros

Saludos

[nageswaragunupudi]

Sql for counting number of records:

SELECT COUNT(*) [AS name] FROM <table> [ WHERE <condition>]
Example:
cSql := "SELECT COUNT(*) AS NumRecs FROM customer WHERE state = 'NY'"
oQry := oServer:Query( cSql )

Sql for summing fields:

SELECT SUM( <field1> ) [AS <sum1>],.. FROM <table> [WHERE <condition>]
Example:
cSql := "SELECT SUM(salary) AS TotSalary FROM customer WHERE married = 1"
oQry := oServer:Query( cSql )

For using in SQL statements, we need to convert date values to the format "'YYYY-MM-DD'"
We can largely simply our work by using Dolphin's built-in function ClipValue2SQL( <harbourvalue> )
We can also use parameters clause while opening a query

Example:

Code: Select all  Expand view

oQry := oServer:Query( "SELECT COUNT(*) AS NumRecs FROM ext_otr1 WHERE fec_ing BETWEEN &1 AND &2", ;
{ ClipValue2SQL( dDate1 ), ClipValue2SQL( dDate2 ) } )
 

[Armando]

Mr. Rao:

Just out of curiosity, using the function DTOS() is not enough?

Regards

[nageswaragunupudi]

Mr. Rao:

Just out of curiosity, using the function DTOS() is not enough?

Regards

DTOS() is enough too.