PL32263: AFT.OPERAT.SUBSTR||,DATE,DAY..IMBED.IN A INSERT SELECT..DISTINCT (CHNG A CHAR-DATEFIELD INTO INTEGER FORMAT) OUTPUT CONT.GARBAGE

APAR status

• Closed as duplicate of another APAR.

Error description

• OPERATION:SUBSTR,||,DATE,DAY,MONTH,YEAR IMBEDED IN A INSERT
  SELECT DISTINCT STMNT (CHANGE DATEFIELD (CHAR(8)) INTO
  DATEFIELD (INTEGER) FORMAT LIKE:
  'YYYYMMDD'-->DATE('YYYY-MM-DD')->DAY(DATE)
  +100*MONTH(DATE)+10000*YEAR(DATE) I.E (19880118). EXECUTE OPER:
  1.DROP AND CREATE TS 2.CREATE TABLE AND UNIQUE INDEX 3.SELECT
  (OPERATION) 4.SELECT DISTINCT (OPERATION) 5.DELETE FROM (TABLE
  NAME) 6.INSERT INTO (TABLENAME) SELECT (OPERATION) 7.SELECT *
  FROM (TABLENAME)(WORKS FINE) 8.DELETE FROM (TABLENAME) 9.INSERT
  INTO (TABLENAME) SELECT DISTINCT(OPERATION) 10.SELECT * FROM
  (TABLENAME) (GARBAGE)
  ALL STEPS RESULTS IN POSITIVE SYS.RESPONDS (SQLCODE00 AND
  SUCCESSFUL RETRIEVAL) BUT AFTER STEP4 THE DATEFIELDS CONTAIN
  GARBAGE.
  

Local fix

Problem summary

Problem conclusion

Temporary fix

Comments

• THIS APAR IS A DUPLICATE OF APAR PL29650.  SEE APAR PL29650
  FOR FURTHER DESCRIPTION OF THE PROBLEM AND FIX.  PLEASE APPLY
  PTF UL37244 TO FIX THIS PROBLEM.
                                                                 .
  USER REPORTED THE FOLLOWING PROBLEM:
                                                                 .
  INCORRECT OUTPUT WHEN ISSUING INSERT WITH SUBSELECT AND THE
  SUBSELECT CONTAINS AN ARITHMETIC EXPRESSION WITH SCALAR
  FUNCTIONS:
                                                                 .
  * THE FOLLOWING INSERTED INCORRECT DATA INTO TABLE T2:
                                                                 .
     INSERT INTO       T2
       SELECT DISTINCT
       DAY(DATE(SUBSTR(KIS00120, 1, 4)
       ||'-'||SUBSTR(KIS00120, 5, 2)
       ||'-'||SUBSTR(KIS00120, 7, 2)))
       +
       MONTH(DATE(SUBSTR(KIS00120, 1, 4)
       ||'-'||SUBSTR(KIS00120, 5, 2)
       ||'-'||SUBSTR(KIS00120, 7, 2)))*100
       +
       YEAR(DATE(SUBSTR(KIS00120, 1, 4)
       ||'-'||SUBSTR(KIS00120, 5, 2)
       ||'-'||SUBSTR(KIS00120, 7, 2)))*10000
       , DEV00001, DEV00010 FROM T1:
                                                                 .
  IF THE DISTINCT WAS REMOVED OR THE INSERT WAS REMOVED, THE
  CORRECT DATA IS SELECTED:
                                                                 .
  * THE FOLLOWING WORKS:
                                                                 .
     INSERT INTO       T2
       SELECT
       DAY(DATE(SUBSTR(KIS00120, 1, 4)
       ||'-'||SUBSTR(KIS00120, 5, 2)
       ||'-'||SUBSTR(KIS00120, 7, 2)))
       +
       MONTH(DATE(SUBSTR(KIS00120, 1, 4)
       ||'-'||SUBSTR(KIS00120, 5, 2)
       ||'-'||SUBSTR(KIS00120, 7, 2)))*100
       +
       YEAR(DATE(SUBSTR(KIS00120, 1, 4)
       ||'-'||SUBSTR(KIS00120, 5, 2)
       ||'-'||SUBSTR(KIS00120, 7, 2)))*10000
       , DEV00001, DEV00010 FROM       T1:
                                                                 .
                                                                 .
       SELECT
         DAY(DATE(SUBSTR(KIS00120, 1, 4)
         ||'-'||SUBSTR(KIS00120, 5, 2)
         ||'-'||SUBSTR(KIS00120, 7, 2)))
         +
         MONTH(DATE(SUBSTR(KIS00120, 1, 4)
         ||'-'||SUBSTR(KIS00120, 5, 2)
         ||'-'||SUBSTR(KIS00120, 7, 2)))*100
         +
         YEAR(DATE(SUBSTR(KIS00120, 1, 4)
         ||'-'||SUBSTR(KIS00120, 5, 2)
         ||'-'||SUBSTR(KIS00120, 7, 2)))*10000
         , DEV00001, DEV00010 FROM       T1:
                                                                 .
  ----------------------------------------------------------------
  ADDITIONAL KEYWORDS: INCORROUT SCALAR FUNCTION DISTINCT INSERT
  SQLINCORR
  

APAR Information

• APAR is sysrouted FROM one or more of the following:

• APAR is sysrouted TO one or more of the following:

Fix information

Applicable component levels