Page 1 of 1
Cadena extensa TEXT ENDTEXT - ( SOLUCIONADO )
Posted: Thu Dec 12, 2019 1:47 am
by leandro
Buenas noches para todos, en este momento me surge la necesidad que crear una cadena de texto bastante extensa, para realizar una consulta sql, pero requiero reemplazar algunos datos dentro de la cadena de texto, buscando en el foro, encontré la forma de escribir la super consulta, pero lo que no logro es reemplazar valores dentro del contenido de la cadena.
Code: Select all
**********************************
*MOVIMIENTO DE ARTICULOS POR MESES
**********************************
function fMovMeses()
Local cQry
Local cDato := "CORP012"
TEXT INTO cQry
SELECT mes,SUM(entradas) AS entradas,SUM(salidas) AS salidas FROM (
SELECT MONTH(MOV.o_fechas) AS mes,SUM(MOV.o_cansal) AS salidas,SUM(MOV.o_canent) AS entradas
FROM tbl_movi MOV WHERE MOV.o_articu='cDato' GROUP BY MONTH(MOV.o_fechas)
UNION
SELECT MONTH(FAC.mm_fechas) AS mes,SUM(FAC.mm_cansal) AS salidas,SUM(FAC.mm_canent) AS entradas
FROM tbl_mofa FAC WHERE FAC.mm_articu='cDato' GROUP BY MONTH(FAC.mm_fechas)
) MOV GROUP BY mes;
ENDTEXT
memoedit(cQry)
Return nil
Para este caso requiero reemplazar el valor de la variable cDato dentro de la cadena de texto.
De antemano gracias
Re: Cadena extensa TEXT ENDTEXT
Posted: Thu Dec 12, 2019 4:52 am
by nageswaragunupudi
METHOD-1
Code: Select all
function fMovMeses()
Local cQry
Local cDato := "CORP012"
TEXT INTO cQry
SELECT mes,SUM(entradas) AS entradas,SUM(salidas) AS salidas FROM (
SELECT MONTH(MOV.o_fechas) AS mes,SUM(MOV.o_cansal) AS salidas,SUM(MOV.o_canent) AS entradas
FROM tbl_movi MOV WHERE MOV.o_articu='cDato' GROUP BY MONTH(MOV.o_fechas)
UNION
SELECT MONTH(FAC.mm_fechas) AS mes,SUM(FAC.mm_cansal) AS salidas,SUM(FAC.mm_canent) AS entradas
FROM tbl_mofa FAC WHERE FAC.mm_articu='cDato' GROUP BY MONTH(FAC.mm_fechas)
) MOV GROUP BY mes;
ENDTEXT
cQry := StrTran( cQry, "cDato", cDato )
memoedit(cQry)
METHOD-2
Code: Select all
function fMovMeses()
Local cQry
//Local cDato := "CORP012"
PRIVATE cDato := "CORP012"
TEXT INTO cQry
SELECT mes,SUM(entradas) AS entradas,SUM(salidas) AS salidas FROM (
SELECT MONTH(MOV.o_fechas) AS mes,SUM(MOV.o_cansal) AS salidas,SUM(MOV.o_canent) AS entradas
FROM tbl_movi MOV WHERE MOV.o_articu='&cDato' GROUP BY MONTH(MOV.o_fechas)
UNION
SELECT MONTH(FAC.mm_fechas) AS mes,SUM(FAC.mm_cansal) AS salidas,SUM(FAC.mm_canent) AS entradas
FROM tbl_mofa FAC WHERE FAC.mm_articu='&cDato' GROUP BY MONTH(FAC.mm_fechas)
) MOV GROUP BY mes;
ENDTEXT
MEMOEDIT( cQry )
Re: Cadena extensa TEXT ENDTEXT
Posted: Thu Dec 12, 2019 2:49 pm
by leandro
Excelente
Mr. Nages, como siempre muchísimas gracias.
Otra pregunta, por solo funciona con variables de tipo PRIVATE?
Re: Cadena extensa TEXT ENDTEXT - ( SOLUCIONADO )
Posted: Thu Dec 12, 2019 3:25 pm
by nageswaragunupudi
Otra pregunta, por solo funciona con variables de tipo PRIVATE?
If you want to use the macro expansion feature, i.e., &cVar, inside TEXT ... ENDTEXT, then the cVar has to be a PRIVATE or PUBLIC variable.
Otherwise, you can use a token like "<token>" and use StrTran(...) replacing that token with your value,
I gave both the approaches above.
Re: Cadena extensa TEXT ENDTEXT - ( SOLUCIONADO )
Posted: Thu Dec 12, 2019 3:44 pm
by nageswaragunupudi
METHOD-3
Code: Select all
#include "fivewin.ch"
function Main()
local cQry
cQry := fMovMeses( "CORP012" )
MEMOEDIT ( cQry )
return nil
function fMovMeses
local cQry
PARAMETERS cDato
TEXT INTO cQry
SELECT mes,SUM(entradas) AS entradas,SUM(salidas) AS salidas FROM (
SELECT MONTH(MOV.o_fechas) AS mes,SUM(MOV.o_cansal) AS salidas,SUM(MOV.o_canent) AS entradas
FROM tbl_movi MOV WHERE MOV.o_articu='&cDato' GROUP BY MONTH(MOV.o_fechas)
UNION
SELECT MONTH(FAC.mm_fechas) AS mes,SUM(FAC.mm_cansal) AS salidas,SUM(FAC.mm_canent) AS entradas
FROM tbl_mofa FAC WHERE FAC.mm_articu='&cDato' GROUP BY MONTH(FAC.mm_fechas)
) MOV GROUP BY mes;
ENDTEXT
return cQry
Here we defined the FUNCTION with the Oldest Clipper syntax.
Instead of defining the function as:
we declared the function like this:
Code: Select all
FUNCTION fMovMeses
// local declarations
PARAMETERS cDato
These parameters are equivalent to PRIVATE.