Page 1 of 1

use "[" and "]" in dbpExecSQL

PostPosted: Fri Sep 19, 2014 8:52 am
by michi
Hi all, I've another problem with dbpExecSQL.
I must create a query with MSAccess where there are some field determinated by expression.

For example:

FieldX
FieldY

FieldZ is determinated by "FIELD X" - "FIELD Y"

SQL basic code is:

SELECT FieldX, FieldY, [FieldX]-[FieldY] AS FieldZ
[...]

but now Neobook think that [FieldX] is a publication variable and on publication run doesn't execute the query but show error message.

Someone could help me to solve this trouble?

thanks in advance guys

Re: use "[" and "]" in dbpExecSQL

PostPosted: Fri Sep 19, 2014 9:40 am
by Gaev
michi:

I haven't verified this myself within the context of an SQL statement being passed to the database engine ... but in keeping with the general workaround elsewhere in NeoBook, try ...
Code: Select all
SELECT FieldX, FieldY, [#91]FieldX[#93]-[#91]FieldY[#93] AS FieldZ

Re: use "[" and "]" in dbpExecSQL

PostPosted: Sun Sep 21, 2014 4:32 am
by michi
Hi Gaev and thanks for your reply.
I've tried but it doesn't work.

I've tried in MSAccess to obmit [] so...

SELECT FieldX-FieldY AS FieldZ

and works.

In neobook dbpExecSQL go on error "Cannot perform this operation on a closed dataset".

SQL code works until I use FieldX-FieldY AS FieldZ statement.

I'm going crazy

Re: use "[" and "]" in dbpExecSQL

PostPosted: Sun Sep 21, 2014 4:59 am
by michi
This is the code:

Code: Select all
SELECT Sum(Warehouse.IN) AS IN, Sum(Warehouse.OUT) AS OUT, Warehouse.ProductCode, IN-OUT AS STOCK
FROM Warehouse
GROUP BY Warehouse.ProductCode;


Neobook reply "Cannot perform this operation on a closed dataset"

I can't understand why Neobook reject this basic code.

If you obmit IN-OUT AS STOCK all works fine.

Re: use "[" and "]" in dbpExecSQL

PostPosted: Sun Sep 21, 2014 5:59 am
by Gaev
michi:

Neobook reply "Cannot perform this operation on a closed dataset"

Try ...
Code: Select all
dbpOpenAccessDatabase "database id" "file name" "options"
dbpOpenTable "database id" "Warehouse" ""
... before doing the dbpExecSQL command

I can't understand why Neobook reject this basic code.
If you obmit IN-OUT AS STOCK all works fine.

Try and place the expression (IN-OUT) within brackets ... like so ...
Code: Select all
SELECT Sum(Warehouse.IN) AS IN, Sum(Warehouse.OUT) AS OUT, Warehouse.ProductCode, (IN-OUT) AS STOCK
FROM Warehouse
GROUP BY Warehouse.ProductCode;
... or if you want to be consistent, specify the Table name for each of the fields in the expression ... like so ...
Code: Select all
SELECT Sum(Warehouse.IN) AS IN, Sum(Warehouse.OUT) AS OUT, Warehouse.ProductCode, (Warehouse.IN-Warehouse.OUT) AS STOCK
FROM Warehouse
GROUP BY Warehouse.ProductCode;

Re: use "[" and "]" in dbpExecSQL

PostPosted: Sun Sep 21, 2014 9:18 am
by michi
Hi Gaev.

Database is opened, and tables too.

I've tried to use brackets without success.

Same error: "cannot perform this operation on a closed dataset"

So I try to not specify temporary result table in dbpExecSQL action... and error message now change:

"Impossibile eseguire una query che non include l'espressione "IN-OUT" specificata come parte di una funzione di aggregazione"

in english can be: Impossible to execute a query that not include expression "IN-OUT" specified as a part of aggregation function

code is the same

Code: Select all
SELECT Sum(Warehouse.IN) AS IN, Sum(Warehouse.OUT) AS OUT, Warehouse.ProductCode, (IN-OUT) AS STOCK
FROM Warehouse
GROUP BY Warehouse.ProductCode;


(IN-OUT) AS STOCK generates the problem. I can't use warehouse.in - warehouse.out as STOCK as you suggest, because IN AND OUT are sum, not single records.

:?: :?: :?:

Re: use "[" and "]" in dbpExecSQL

PostPosted: Sun Sep 21, 2014 9:33 am
by michi
:D now works.

I changed name of generated field.

before I used sum of warehouse.IN as IN. Maybe go on error because the engine consider the IN (the sum) as warehouse.IN.

Changing sum field name command is OK!!!

Re: use "[" and "]" in dbpExecSQL

PostPosted: Sun Sep 21, 2014 11:01 am
by Gaev
michi:

before I used sum of warehouse.IN as IN. Maybe go on error because the engine consider the IN (the sum) as warehouse.IN.
Changing sum field name command is OK!!!

See list of reserved words in MS Access ... IN is one of them ... never use Reserved Words as Field Names.

However, I wonder how you were previously able to achieve this before ...
If you obmit IN-OUT AS STOCK all works fine.

Re: use "[" and "]" in dbpExecSQL

PostPosted: Mon Sep 22, 2014 12:16 am
by michi
it was to explain that error was generated when I add this part of code.

Finally I solve the problem. Thanks for your support!

Michi