topic-38182

Posts: 1818

Joined: Wed Oct 26, 2005 02:49 PM

Cadena extensa TEXT ENDTEXT - ( SOLUCIONADO )

Posted: Thu Dec 12, 2019 01:47 AM

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 (fw): Select all Collapse

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

Saludos
LEANDRO AREVALO
Bogotá (Colombia)
https://hymlyma.com
https://hymplus.com/
leandroalfonso111@gmail.com
leandroalfonso111@hotmail.com

[ Turbo Incremental Link64 6.98 Embarcadero 7.70 ] [ FiveWin 25.01 ] [ xHarbour 64 bits) ]

nageswaragunupudi

Posts: 10733

Joined: Sun Nov 19, 2006 05:22 AM

Re: Cadena extensa TEXT ENDTEXT

Posted: Thu Dec 12, 2019 04:52 AM

METHOD-1

Code (fw): Select all Collapse

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 (fw): Select all Collapse

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 )

Regards

G. N. Rao.

Hyderabad, India

leandro

Posts: 1818

Joined: Wed Oct 26, 2005 02:49 PM

Re: Cadena extensa TEXT ENDTEXT

Posted: Thu Dec 12, 2019 02:49 PM

Excelente

Mr. Nages, como siempre muchísimas gracias.

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

Saludos
LEANDRO AREVALO
Bogotá (Colombia)
https://hymlyma.com
https://hymplus.com/
leandroalfonso111@gmail.com
leandroalfonso111@hotmail.com

[ Turbo Incremental Link64 6.98 Embarcadero 7.70 ] [ FiveWin 25.01 ] [ xHarbour 64 bits) ]

nageswaragunupudi

Posts: 10733

Joined: Sun Nov 19, 2006 05:22 AM

Re: Cadena extensa TEXT ENDTEXT - ( SOLUCIONADO )

Posted: Thu Dec 12, 2019 03:25 PM

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.

Regards

G. N. Rao.

Hyderabad, India

nageswaragunupudi

Posts: 10733

Joined: Sun Nov 19, 2006 05:22 AM

Re: Cadena extensa TEXT ENDTEXT - ( SOLUCIONADO )

Posted: Thu Dec 12, 2019 03:44 PM

METHOD-3

Code (fw): Select all Collapse

#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 (fw): Select all Collapse

function fMovMeses( cDato )

we declared the function like this:

Code (fw): Select all Collapse

FUNCTION fMovMeses
   // local declarations
   PARAMETERS cDato

These parameters are equivalent to PRIVATE.

Regards

G. N. Rao.

Hyderabad, India

FiveTech Support Forums

Cadena extensa TEXT ENDTEXT - ( SOLUCIONADO )

Re: Cadena extensa TEXT ENDTEXT

Re: Cadena extensa TEXT ENDTEXT

Re: Cadena extensa TEXT ENDTEXT - ( SOLUCIONADO )

Re: Cadena extensa TEXT ENDTEXT - ( SOLUCIONADO )

Continue the discussion