Db2 for z/OS & Db2ZAI

 View Only
Expand all | Collapse all

Result of expression "-(SELECT 3 FROM SYSIBM.SYSDUMMY1 )" in update statement is zero and not -3

  • 1.  Result of expression "-(SELECT 3 FROM SYSIBM.SYSDUMMY1 )" in update statement is zero and not -3

    Posted Wed July 06, 2022 08:18 AM

    Hi all,

    the result of the update statement below is zero. That is not the value I realy expected (-3).
    Does anyone have an explanation?

    SET SCHEMA=SESSION; 
    DECLARE GLOBAL TEMPORARY TABLE T(A INTEGER); 
    INSERT INTO T VALUES(99); 
    SELECT * FROM SESSION.T; 
    SELECT -(SELECT 3 FROM SYSIBM.SYSDUMMY1 ) 
    FROM SYSIBM.SYSDUMMY1; 
    UPDATE T 
    SET A= -(SELECT 3 FROM SYSIBM.SYSDUMMY1 ) 
    ; 
    SELECT * FROM SESSION.T; 
    
    SPUFI output
    ============
    SELECT * FROM SESSION.T; 
    ---------+---------+---------+---------+---------+---------+
    SET SCHEMA=SESSION; 
    ---------+---------+---------+---------+---------+---------+
    DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0 
    ---------+---------+---------+---------+---------+---------+
    
    DECLARE GLOBAL TEMPORARY TABLE T(A INTEGER); 
    ---------+---------+---------+---------+---------+---------+
    DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0 
    ---------+---------+---------+---------+---------+---------+
    
    INSERT INTO T VALUES(99); 
    ---------+---------+---------+---------+---------+---------+
    DSNE615I NUMBER OF ROWS AFFECTED IS 1 
    DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0 
    ---------+---------+---------+---------+---------+---------+
    
    SELECT * FROM SESSION.T; 
    ---------+---------+---------+---------+---------+---------+
    A 
    ---------+---------+---------+---------+---------+---------+
    99 
    DSNE610I NUMBER OF ROWS DISPLAYED IS 1 
    DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100 
    ---------+---------+---------+---------+---------+---------+
    
    SELECT -(SELECT 3 FROM SYSIBM.SYSDUMMY1 ) 
    FROM SYSIBM.SYSDUMMY1; 
    ---------+---------+---------+---------+---------+---------+
    
    ---------+---------+---------+---------+---------+---------+
    -3 
    DSNE610I NUMBER OF ROWS DISPLAYED IS 1 
    DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100 
    ---------+---------+---------+---------+---------+---------+
    
    UPDATE T 
    SET A= -(SELECT 3 FROM SYSIBM.SYSDUMMY1 ) 
    ; 
    ---------+---------+---------+---------+---------+---------+
    DSNE615I NUMBER OF ROWS AFFECTED IS 1 
    DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0 
    ---------+---------+---------+---------+---------+---------+
    
    SELECT * FROM SESSION.T; 
    ---------+---------+---------+---------+---------+---------+
    A 
    ---------+---------+---------+---------+---------+---------+
    0 
    DSNE610I NUMBER OF ROWS DISPLAYED IS 1 
    DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100 
    ---------+---------+---------+---------+---------+---------+
    



    Thanks.



    ------------------------------
    Peter Hartig
    ------------------------------


  • 2.  RE: Result of expression "-(SELECT 3 FROM SYSIBM.SYSDUMMY1 )" in update statement is zero and not -3

    Posted Wed July 06, 2022 11:37 AM

    Try this:

     

     

    UPDATE T  SET A=                                                  

    (SELECT -(SELECT 3 FROM SYSIBM.SYSDUMMY1 ) FROM SYSIBM.SYSDUMMY1);

     

    Sally Mir

    Senior Db2 Consultant

     

    IntelliMagic – Availability Intelligence

    T:   (817) 809-8829

    M: (336) 655-1323

    www.intellimagic.com