Cadena extensa TEXT ENDTEXT - ( SOLUCIONADO )

[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  Expand view  RUN

**********************************
*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

[nageswaragunupudi]

METHOD-1

Code: Select all  Expand view  RUN

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  Expand view  RUN

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 )

 

[leandro]

Excelente

Mr. Nages, como siempre muchísimas gracias.

Otra pregunta, por solo funciona con variables de tipo PRIVATE?

[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.

[nageswaragunupudi]

METHOD-3

Code: Select all  Expand view  RUN

#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:

Code: Select all  Expand view  RUN

function fMovMeses( cDato )

we declared the function like this:

Code: Select all  Expand view  RUN

FUNCTION fMovMeses
   // local declarations
   PARAMETERS cDato
 

These parameters are equivalent to PRIVATE.