Original Message:
Sent: 10-20-2021 06:21 AM
From: Marc De Potter
Subject: UPDATE with IDMS sql
Ian,
I would like to perform an update of a row using the AQT tool. When selecting update, it tries to execute following syntax:
Update IDBACOLR.REQUESTR Set COMMUNIC='COM34.700.105 test' where ROWID=0x0007D902000B0008
This syntax gives following error when executing:
Error during Execute 37000(0)[CA][CA IDMS ODBC Driver]Syntax error token = x0007D902000B0008 01000(-4)[CA][CA IDMS ODBC Driver][CA IDMS]DB006001 T923 C-4M330: UNRECOGNIZABLE TOKEN
I did not find any solution while going thru the CA IDMS SQL Reference Guide on knowledge.broadcom.com
Is there any way to be able to launch update statemens via SQL outside of OCF/BCF using ROWID as unique qualifier to determine the row to be updated as in the example?
Or is it not possible to use ROWID in this way when working with virtual foreign keys?
Grts,
Marc
Original Message:
Sent: 02-01-2021 10:44 AM
From: Ian Hill
Subject: UPDATE with IDMS sql
If it's OK for you to do this in an OCF/IDMSBCF script, then you could try something like this:
CREATE TEMPORARY TABLE TT1
( ROWID1 BIN(8) NOT NULL );
INSERT INTO TT1
SELECT ROWID FROM "ETAB-TAXE"
WHERE HEX(NO_FCE_1_ETAB) IN ('00000000','40404040');
CREATE TEMPORARY TABLE TT2
( ROWID2 BIN(8) NOT NULL,
SEQUENCE INTEGER NOT NULL );
INSERT INTO TT2
SELECT A.ROWID1, COUNT(*) FROM TT1 A, TT1 B
WHERE B.ROWID1 <= A.ROWID1 GROUP BY A.ROWID1;
DELETE FROM TT2 WHERE SEQUENCE > 5;
UPDATE "ETAB-TAXE" SET NO_FCE_1_ETAB=0
WHERE ROWID IN (SELECT ROWID2 FROM TT2);
TT1 contains the ROWIDs of only the rows that meet the WHERE clause criteria.
TT2 contains them with a sequence number which puts them in order.
After the DELETE, TT2 contains only the first 5.
Then you use that as the basis for the UPDATE.
If you must do this in one statement, it might be possible but would be very convoluted.
------------------------------
Principal Support Engineer
Broadcom
Original Message:
Sent: 01-28-2021 10:30 AM
From: Sylvain Beaunoyer
Subject: UPDATE with IDMS sql
Hi
I would like to update by using IDMS SQL.
UPDATE "ETAB-TAXE"
SET NO_FCE_1_ETAB = 0
WHERE HEX(NO_FCE_1_ETAB) IN ('00000000','40404040')
AND rowid < 5 ;
How can I do that ?
How can I access the 5 first rows ?
Thank you for your help.
Sylvain
Les renseignements contenus dans ce message peuvent être confidentiels.
Si vous n'êtes pas le destinataire visé ou une personne autorisée à lui remettre ce courriel, vous êtes par la présente avisé qu'il est strictement interdit d'utiliser, de copier ou de distribuer ce courriel, de dévoiler la teneur de ce message ou de prendre quelque mesure fondée sur l'information contenue. Vous êtes donc prié d'aviser immédiatement l'expéditeur de cette erreur et de détruire ce message sans garder de copie.