Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
 From: juancarlos jimenez
  Where is juancarlos jimenez?
 guayaquil
 Ecuador
 juancarlos jimenez
 Tags
Subject: group concat max len code from mysql
Thread ID: 395570 Message ID: 395570 # Views: 42 # Ratings: 0
Version: Visual FoxPro 6 Category: General VFP Topics
Date: Wednesday, January 15, 2014 10:08:48 PM         
   


hi i have this code in mysql

SET @@group_concat_max_len = 20024;
SET @sqlprueba=NULL,@sqlprueba0=NULL,@sqlprueba1=NULL,@sqlprueba2=NULL, @sqlprueba3=NULL, @sqlprueba4=NULL;
SELECT
GROUP_CONCAT(DISTINCT CONCAT(' COUNT(CASE WHEN (monthname(fecha)="',MES,'" AND day(fecha)="',DIA,'" ) THEN autorizacion ELSE NULL END) AS ',dia,mes) )
INTO @sqlprueba
FROM
(SELECT DAY(FECHA) DIA, MONTHNAME(FECHA) MES FROM servicio WHERE FECHA BETWEEN '2014-01-01' AND '2014-01-31' GROUP BY MONTHNAME(FECHA),DAY(FECHA)) T2;
SET @sqlprueba2=CONCAT('select hour(hora) hora,',@sqlprueba,' from servicio WHERE valido!=3 and fecha BETWEEN "2014-01-01" AND "2014-01-31" GROUP BY HOUR(hora)');
PREPARE stmt FROM @sqlprueba2;
EXECUTE stmt;

i use it without problems in my sql browser etc, but when i try to use it in the vf6 i cant make it run

i use it in this way

reporte = [ SET @@group_concat_max_len = 20024; ]+;
[ SET @sqlprueba=NULL, @sqlprueba0=NULL, @sqlprueba1=NULL, @sqlprueba2=NULL, @sqlprueba3=NULL, @sqlprueba4=NULL; ]+;
[ SELECT ]+;
[ GROUP_CONCAT(DISTINCT CONCAT(' COUNT(CASE WHEN (monthname(fecha)="',MES,'" AND day(fecha)="',DIA,'" ) THEN autorizacion ELSE NULL END) AS ',dia,mes) ) ]+;
[ INTO @sqlprueba ]+;
[ FROM ]+;
[(SELECT DAY(FECHA) DIA, MONTHNAME(FECHA) MES FROM servicio WHERE FECHA BETWEEN ('2014-01-01') and ('2014-01-31') ]+;
[ GROUP BY MONTHNAME(FECHA),DAY(FECHA)) T2; ]+;
[ SET @sqlprueba2=CONCAT('select hour(hora) hora,',@sqlprueba,' from servicio WHERE valido!=3 and fecha BETWEEN "2014-01-01" AND "2014-01-31" ]+;
[ GROUP BY HOUR(hora)'); ]+;
[ PREPARE stmt FROM @sqlprueba2; ]+;
[ EXECUTE stmt; ]+;
=SQLEXEC(enlace,reporte,"listareporte")

what im making wrong??? please a lil help

ENTIRE THREAD

group concat max len code from mysql Posted by juancarlos jimenez @ 1/15/2014 10:08:48 PM
RE: group concat max len code from mysql Posted by Martina Jindrov√° @ 1/16/2014 7:39:31 AM