IDMS

 View Only
  • 1.  UPDATE with IDMS sql

    Posted Feb 01, 2021 09:00 AM

    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.



  • 2.  RE: UPDATE with IDMS sql

    Broadcom Employee
    Posted Feb 01, 2021 10:44 AM

    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
    ------------------------------



  • 3.  RE: UPDATE with IDMS sql

    Posted Feb 02, 2021 12:36 PM

    Thank you

     

    Sylvain Beaunoyer | DGTT | Service des produits d'enregistrement

    Revenu Québec | 3800, rue de Marly, Québec (Québec), secteur 2-2-6, G1X 4A5

    Tél. :

    Devez-vous vraiment imprimer ce courriel?

    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.






  • 4.  RE: UPDATE with IDMS sql

    Posted Oct 20, 2021 07:54 AM

    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




  • 5.  RE: UPDATE with IDMS sql

    Broadcom Employee
    Posted Oct 21, 2021 09:56 AM
    Hi, Marc,
    You need single quotes around the ROWID value:

    UPDATE EMPNET1.DEPARTMENT SET DEPT_NAME_0410 = 'XXX'
    WHERE ROWID=X'0125A50100010008';
    *+ Status = 0 SQLSTATE = 00000
    *+ 1 row processed

    Best regards,
    Ian.


  • 6.  RE: UPDATE with IDMS sql

    Posted Oct 21, 2021 09:56 AM

    That will not work if you don't enclose the rowid in quotes as follows:

     

    Update IDBACOLR.REQUESTR Set COMMUNIC='COM34.700.105 test' where ROWID=x'0007D902000B0008'

     

    HTH

     

    Steve

     

     

                   

                Steve Rundle

                Lead IDMS DBA @ BT

                Kyndryl UK Ltd.

                ------------------------------------------------

                Emailsteve.rundle@kyndryl.com

                Tel:      +44 117 929 5600

                Mobile: +44 7768 400019

                www.kyndryl.com

                ------------------------------------------------

                    Kyndryl UK Limited

                    Registered in England and Wales with number 13141201

                    Registered office: PO Box 41, North Harbour, Portsmouth, United Kingdom, PO6 3AU

     

    Kyndryl UK Limited
    Registered in England and Wales with number 13141201
    Registered office: PO Box 41, North Harbour, Portsmouth, United Kingdom, PO6 3AU





  • 7.  RE: UPDATE with IDMS sql

    Posted Feb 02, 2021 04:55 PM
    I would add that you have to turn off the AUTOCOMMIT in the SET OPTIONS command.

    Then you can add a ROLLBACK as the last command at test and adjust until it does what you want. 

    I would suggest using BCF because it is just easier with command sequences like this.

    And yes, I have been known to do things like this.