Working with "WITH" clause in TSQLQuery Postgresql
lazarus / 16-Databases

----------------------------------------------------------------------
mizarra · 2013-03-06 21:37

Hi, I'm trying to make a "SELECT" using "WITH" clause in PostgreSQL
with TSQLQuery component but I get this message "SQLQuery1 : Cannot
open a non-select statement." . This is my SQL code:

Code: [Select]WITH c AS (SELECT DISTINCT cancvd.documento,sum
(cancvd.monto) OVER w AS monto FROM cancv,cancvd
WHERE cancvd.codcancv = cancv.codcancv AND cancv.estado != '3' AND
cancv.codcliente = 1
WINDOW w AS (PARTITION BY cancvd.documento) ORDER BY 1),
nd AS (SELECT notadcv.codventa,sum(notadcv.monto) OVER w AS monto FROM
notadcv
WHERE notadcv.codtipodocumento = 4 AND notadcv.estado !='3' AND
notadcv.codcliente = 1
WINDOW w AS (PARTITION BY notadcv.codventa) ORDER BY 1),
nc AS (SELECT notadcv.codventa, sum(SQLQuery1 : Cannot open a non-
select statement.notadcv.monto) OVER w AS monto
FROM notadcv WHERE notadcv.codtipodocumento = 5 AND notadcv.estado
!='3' AND notadcv.codcliente = 1
 WINDOW w AS (PARTITION BY notadcv.codventa) ORDER BY 1)
 SELECT venta.codventa,venta.factura,venta.codcliente,tipodocumento.co
dtipodocumento,
 tipodocumento.descripcion,venta.fecha,
 sum (ventad.cantidad *  ventad.precio + (ventad.cantidad * 
ventad.precio) * (ventad.impuesto/100)) AS total,
 MAX(ventad.impuesto) AS impuesto,c.monto AS montocancv,nd.monto AS
montonotad,nc.monto AS montonotac
 FROM venta LEFT OUTER JOIN c ON (venta.codventa = c.documento)
 LEFT OUTER JOIN nd ON (venta.codventa = nd.codventa)
  LEFT OUTER JOIN nc ON (venta.codventa =
nc.codventa),ventad,tipodocumento
  WHERE venta.codventa = ventad.codventa AND tipodocumento.iniciales =
'FAV' AND venta.estado != '3'
  AND  venta.codcliente = 1 GROUP BY
venta.codventa,venta.factura,venta.codcliente,
  tipodocumento.codtipodocumento,tipodocumento.descripcion,venta.fecha
,c.monto,nd.monto,nc.monto
  ORDER BY venta.factura
pd: This code work fine in PGAdminIII

----------------------------------------------------------------------
goodname · 2013-03-06 21:54

Assume that your working with FPC 2.6.2 or lower. The FPC 2.7.1
compiler should be able to handle this kind of query. That is a
complicated query and must have taken some time to build.

For anyone wondering about that query see here.
http://www.postgresql.org/docs/8.4/static/queries-with.html

----------------------------------------------------------------------
taazz · 2013-03-06 22:03

it is a known problem with the current TSqlQuery implementation. It
tries to be smart about which sql can and can not return a dataset 
and fails to take in to account all the various commands that can
return a dataset. It can be circumvent by giving it a dummy select as
the 1st line eg

select current_date as Serverdate;
with c as(.......

I haven't tried on the server to see how it will react and I do not
know what it will be returned. My suggestion is to open a bug report
with a complete test case and try to use the above trick to get you
going. Let me add at this point that in your test case you should
provide an sql file with the ddl statement to create the tables you
need and insert some data in them so the with c as(..) can be tested
by the developers before deciding on how to solve this problem.

----------------------------------------------------------------------
goodname · 2013-03-06 22:23

There has been a patch in SQLDB PostgreSQL code that should handle
this situation. It has been around for a very long time and don't know
why it was not in FPC 2.6.0. I can say that it is in FPC 2.7.1.

http://bugs.freepascal.org/view.php?id=20133

----------------------------------------------------------------------
taazz · 2013-03-06 23:07

well 2.6.0 seems that has a lot of sqldb bugs in it, I haven't checked
the latest release which is 2.6.2 how many of them are fixed and
knowing that SQLQuery might hit an other similar wall with other sql
statements doesn't do a lot for my confidence on the set. I need to re
evaluate them at some point the only thing I can offer until then is
the work around I have come across.

----------------------------------------------------------------------
BigChimp · 2013-03-07 07:31

Try 2.6.2 in any case; a lot of bugs were fixed.

taazz' suggestion:
Code: [Select]select current_date as Serverdate;
with c assounds good.

IIRC, you can set SQLQuerybla.ParseSQL:=false as well to get FPC to
ignore it's parsing of whether something is a selectable statement or
an executable statement.

----------------------------------------------------------------------
Lacak2 · 2013-03-07 12:37

In 2.6.2 was improved SQLParser to detect WITH (...) SELECT ... and
mark it as stSelect.
In 2.7.1 was introduced new functionality, which does not depend on
StatementType, but takes into account what DB backend returns (either
in Prepare stage or Execute stage)

So try 2.6.2 and if does not help, then try 2.7.1 ;-)

----------------------------------------------------------------------
mizarra · 2013-03-07 13:05

Thank you all, works perfect on 2.6.2