Sqlbase syntax question

Discussion forum about all things SqlBase.
Tom L
Switzerland
Posts: 8
Joined: 21 Jun 2019, 12:26
Location: Switzerland

Sqlbase syntax question

Post by Tom L » 15 Jan 2020, 10:42

Hello

Maybe someone can help me with this problem, thanks in advance.

there are 2 tables, a and b with the columns id and wert
Create table a
(
Id int not null,
wert int,
primary key (id)
);
create unique index a_id on a (id);

Create table b
(
Id int not null,
wert int,
primary key (id)
);
create unique index b_id on b (id);

insert into a values(1,10);
insert into a values(2,20);
insert into a values(3,30);
insert into a values(4,40);
insert into a values(5,50);

insert into b values(1,100);
insert into b values(2,200);
insert into b values(3,300);


Updating the wert columns in a with wert value from b:

update a set a.wert = (select b.wert from b where a.id = b.id)
where exists( select * from b where a.id = b.id);

this works as expected.

Updading the wert columns in a with wert value from b + the wert value from a

update a set a.wert = a.wert + (select b.wert from b where a.id = b.id)
where exists( select * from b where a.id = b.id);

This does'nt work.

What is the right syntax for this?

thanks alot for any help

EwaldP
Austria
Posts: 339
Joined: 07 Mar 2017, 08:00
Location: Austria

Re: Sqlbase syntax question

Post by EwaldP » 15 Jan 2020, 15:03

as far as I can remember, arithmetical operation aren't allowed with subselects. maybe you can solve it with 2 steps? You can add an addiontal column in table a, update this value with



Ewald
Ewald P. Palmetshofer
EDV-Hausleitner GmbH
4020 Linz
www.edv-hausleitner.at

Tom L
Switzerland
Posts: 8
Joined: 21 Jun 2019, 12:26
Location: Switzerland

Re: Sqlbase syntax question

Post by Tom L » 20 Jan 2020, 11:35

thanks for your support.

i opened a ticket, and this is the response for anyone curious:

Development have looked into this. Definitely a bug in the parser for the query. We specifically look for a subselect in the UPDATE statement but we do not currently allow any operations on the return value from the SELECT - just a direct assignment. This will be fixed in the next release. In the meantime, you can create a stored procedure that will do this.

edit: adding the stored procedure for reference

STORE UPDWERT
PROCEDURE UPDWERT
LOCAL VARIABLES
NUMBER ID
NUMBER WERT_SUM
NUMBER NIND
SQL HANDLE CUR_A
SQL HANDLE CUR_B
ACTIONS
ON PROCEDURE EXECUTE
Call SqlConnect(CUR_A)
Call SqlConnect(CUR_B)
Call Sqlprepare(CUR_A, \
'SELECT A.ID, A.WERT + B.WERT FROM A, B WHERE A.ID = B.ID INTO :ID, :WERT_SUM')
Call SqlPrepare(CUR_B, \
'UPDATE A SET A.WERT = :WERT_SUM WHERE A.ID = :ID')
Call SqlExecute(CUR_A)
while SqlFetchNext(CUR_A, NIND)
Call SqlExecute(CUR_B)
Call SqlCommit(CUR_B)
Call SqlCommit(CUR_A)
;

Execute this:
execute UPDWERT;
Last edited by Tom L on 20 Jan 2020, 11:45, edited 1 time in total.

Dave Rabelink
Founder/Site Admin
Founder/Site Admin
Netherlands
Posts: 3145
Joined: 24 Feb 2017, 09:12
Location: Gouda, The Netherlands

Re: Sqlbase syntax question

Post by Dave Rabelink » 20 Jan 2020, 11:41

Could you post the ticket and TD defect number here?
This to be able to track it in future
Regards,
Dave Rabelink

Image
Articles and information on Team Developer Tips & Tricks Wiki
Download samples, documents and resources from TD Sample Vault
Videos on TDWiki YouTube Channel

Tom L
Switzerland
Posts: 8
Joined: 21 Jun 2019, 12:26
Location: Switzerland

Re: Sqlbase syntax question

Post by Tom L » 20 Jan 2020, 11:43

the defect is: SQLB-2870
Ticket Number is: 4355932

Return to “General Discussion”

Who is online

Users browsing this forum: [Ccbot] and 1 guest