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

    #Db2forz/OS


  • 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

     




    Original Message


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

    IBM Champion
    Posted Tue August 30, 2022 01:31 AM
    IBM just released APAR PH48073 with PTFs UI82094 UI82095 to fix this and it is also a HIPER...

    ------------------------------
    Roy Boxwell
    ------------------------------

    Original Message