Error when refresh mview (WHERE WHERE)

[maximiliano orrico]

Hi

I've created an INCREMENTAL mview, successfully but I get an error when I try to refresh it.

I did the following things:

1) tables for changelogs added  - was OK (currently updating log tables when data from source changes)

2) mview created with CALL flexviews.create(...) - was OK

3)  expressions added with CALL flexviews.add_expr(...) - was OK

4) tables added with CALL flexviews.add_table(..) - was OK

5) mview added with CALL flexviews.enable(..) - was OK (mview created and filled with data from source tables)

But, when I try to execute CALL flexviews.refresh(..) I get an error like "Error Code: 1064

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE  WHERE (table.field = ..." 

what's wrong?

Consumer script is running ok. no errors.

SELECT flexviews.get_sql(...) returns :

 

SELECT  NULL as mview$pk, (f.id_usuario_vendedor) as `id_usuario_vendedor`, (f.id_moneda) as `id_moneda`, (f.id_file) as `id_file`, (f.id_empresa) as `id_empresa`, (clI.id_compania) as `id_compania`, (cta.id_moneda_file) as `id_moneda_files`, (cta.id_cliente) as `id_cliente`, (clI.nombre) as `nombre_cliente`, (clI.cod_cliente) as `cod_cliente`, (clI.observaciones) as `observaciones`, SUM(cta.debe) as `debe`, SUM(cta.haber) as `haber`, SUM(cta.debe - cta.haber) as `RSALDO`, MAX(r.fecha) as `FUP`, COUNT(fa.id_factura) as `NFAC`, COUNT(*) as `CNT`

FROM  sigmmanet.cta_cte_clientes as `cta`  

JOIN sigmmanet.clientes as `clI` ON clI.id_cliente=cta.id_cliente AND COALESCE(clI.eliminado,0)!=1 

JOIN sigmmanet.files as `f` ON cta.id_empresa=f.id_empresa AND cta.id_file=f.id_file 

JOIN sigmmanet.facturas as `fa` ON cta.documento IN ('FAC','NDC','NDD') AND cta.codigo_documento=fa.id_factura AND fa.estado!='ANULADA' 

JOIN sigmmanet.recibos as `r` ON cta.documento IN ('REC','DVL') AND cta.codigo_documento=r.id_recibo AND r.estado!='ANULADO' WHERE (cta.codigo_documento=0 OR cta.documento NOT IN ('FAC','NDC','NDD') OR fa.id_factura IS NOT NULL) AND (cta.documento NOT IN ('ANL') OR r.id_recibo IS NULL) AND (cta.vencimiento <= NOW() )

GROUP BY (cta.id_cliente) 

Thanks

[Justin Swanhart]

Hi,

It looks like a bug.  When you try to enable the view can you run:

select @v_sql

so I can see exactly where the problem is?

--
You received this message because you are subscribed to the Google Groups "flexviews-discuss" group.
To unsubscribe from this group and stop receiving emails from it, send an email to flexviews-disc...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[maximiliano orrico]

The output : 

INSERT INTO flexviews.mv$8_delta 

SELECT (cta.dml_type * 1) AS dml_type,cta.uow_id AS uow_id,cta.fv$gsn AS fv$gsn ,(cta.id_cliente) AS `id_cliente`, 

(r.fecha) AS `FUP`, IFNULL(SUM((cta.dml_type * 1)),0) AS `CNT`

FROM  flexviews.mvlog_30f854bd1f786a534b4841458f36356e AS cta   

JOIN  sigmmanet.clientes AS clI ON clI.id_cliente=cta.id_cliente AND COALESCE(clI.eliminado,0)!=1  

JOIN  sigmmanet.facturas AS fa ON cta.documento IN ('FAC','NDC','NDD') AND cta.codigo_documento=fa.id_factura AND fa.estado!='ANULADA'  

JOIN  sigmmanet.recibos AS r ON cta.documento IN ('REC','DVL') AND cta.codigo_documento=r.id_recibo AND r.estado!='ANULADO'  

JOIN  sigmmanet.files AS f ON cta.id_empresa=f.id_empresa AND cta.id_file=f.id_file

WHERE  WHERE (cta.codigo_documento=0 OR cta.documento NOT IN ('FAC','NDC','NDD') OR fa.id_factura IS NOT NULL) AND (cta.documento NOT IN ('ANL') OR r.id_recibo IS NULL) AND (cta.vencimiento <= NOW() ) 

AND cta.uow_id >241 

AND cta.uow_id <=309 

AND (cta.dml_type * 1 IS NOT NULL)  

GROUP BY (cta.id_cliente), (r.fecha);

[Justin Swanhart]

Hi,

I believe the problem is because your where clause begins with (.  I believe I've fixed the problem in git.  You will need to run upgrade.sql after downloading the new code.  Don't run install.sql or you will lose your view definitions!

[maximiliano orrico]

Thank you Justin, I'm going to update  and check my repo.

[maximiliano orrico]

Thank you very much Justin , it's work :)

But now I'm having problems trying to generate a MV based on a query with LEFT JOIN clauses (that's the finel real query that I wanto to convert in an MV). 

Like this :

SELECT t1.f1, t2.f2,t3.f3,t4,f4

FROM table AS t

    INNER JOIN table1 AS t1 ON ..

    LEFT JOIN table2 AS t2 ON ..

    LEFT JOIN table3 AS t3 ON ..

    LEFT JOIN table4 AS t4 ON ..

WHERE .. 

Is this posible with flexvews ?

[Justin Swanhart]

Unfortunately, outer joins are not supported.  The refresh algorithm only works for equijoins.

Sent from my iPhone

--