Results 1 to 8 of 8
  1. #1
    Join Date
    Sep 2015
    Posts
    4

    Unhappy Unanswered: Update database tab le on as400

    I haven't used SQL to update database on as400 in a few years and need help with this command.

    Thank you in advance for assistance with this update process.

    Null values not allowed in column or variable PEFLEX1CD. <------ ERROR MESSAGE

    UPDATE FRAUDINV/CFLPE A SET (A.PEFLEX1CD,A.PEFLEX1AMT,A.PEFLEX1PCT,
    A.PEFLEX2CD,A.PEFLEX2AMT,A.PEFLEX2PCT,
    A.PEFLEX3CD,A.PEFLEX3AMT,A.PEFLEX3PCT,
    A.PEFLEX4CD,A.PEFLEX4AMT,A.PEFLEX4PCT,
    A.PEFLEX5CD,A.PEFLEX5AMT,A.PEFLEX5PCT)
    = (SELECT B.PEFLEX1CD,B.PEFLEX1AMT,B.PEFLEX1PCT,
    B.PEFLEX2CD,B.PEFLEX2AMT,B.PEFLEX2PCT,
    B.PEFLEX3CD,B.PEFLEX3AMT,B.PEFLEX3PCT,
    B.PEFLEX4CD,B.PEFLEX4AMT,B.PEFLEX4PCT,
    B.PEFLEX5CD,B.PEFLEX5AMT,B.PEFLEX5PCT
    FROM FRAUDINV/CFLPE B,FRAUDINV/CAMPA C
    WHERE A.PEFLPROP = B.PEFLPROP
    AND A.PEFLPROP = C.PAPROP
    AND B.PEFLEX1CD <> '' AND
    AND B.PEFLPYR = INTEGER(C.PAPUD7 -1)
    AND C.PAPUD7 > '2008')
    Last edited by pugsgma; 09-16-15 at 14:49.

  2. #2
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    sql is almost the same on platform and the rules either
    empty string = zero length string is not the same as NULL

    ..column is not null..
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  3. #3
    Join Date
    May 2010
    Location
    India
    Posts
    84
    Provided Answers: 2
    Hi,

    You have to update only those records that have matching records. Otherwise, you will get above error when there is no matching record. See the change SQL below.


    Satya..


    UPDATE FRAUDINV/CFLPE A SET (A.PEFLEX1CD,A.PEFLEX1AMT,A.PEFLEX1PCT,
    A.PEFLEX2CD,A.PEFLEX2AMT,A.PEFLEX2PCT,
    A.PEFLEX3CD,A.PEFLEX3AMT,A.PEFLEX3PCT,
    A.PEFLEX4CD,A.PEFLEX4AMT,A.PEFLEX4PCT,
    A.PEFLEX5CD,A.PEFLEX5AMT,A.PEFLEX5PCT)
    = (SELECT B.PEFLEX1CD,B.PEFLEX1AMT,B.PEFLEX1PCT,
    B.PEFLEX2CD,B.PEFLEX2AMT,B.PEFLEX2PCT,
    B.PEFLEX3CD,B.PEFLEX3AMT,B.PEFLEX3PCT,
    B.PEFLEX4CD,B.PEFLEX4AMT,B.PEFLEX4PCT,
    B.PEFLEX5CD,B.PEFLEX5AMT,B.PEFLEX5PCT
    FROM FRAUDINV/CFLPE B,FRAUDINV/CAMPA C
    WHERE A.PEFLPROP = B.PEFLPROP
    AND A.PEFLPROP = C.PAPROP
    AND B.PEFLEX1CD <> '' AND
    AND B.PEFLPYR = INTEGER(C.PAPUD7 -1)
    AND C.PAPUD7 > '2008')
    where exists
    (SELECT 1
    FROM FRAUDINV/CFLPE B,FRAUDINV/CAMPA C
    WHERE A.PEFLPROP = B.PEFLPROP
    AND A.PEFLPROP = C.PAPROP
    AND B.PEFLEX1CD <> '' AND
    AND B.PEFLPYR = INTEGER(C.PAPUD7 -1)
    AND C.PAPUD7 > '2008')

  4. #4
    Join Date
    Sep 2015
    Posts
    4

    Angry Result of SELECT more than one row.

    I'm now getting this message. Result of SELECT more than one row.

    Table A will have multiple rows to be updated using Table B; Table B will only have 1. Which select is this error referring to.

    Thank you so much for taking the time to help with this.
    Last edited by pugsgma; 09-17-15 at 17:09.

  5. #5
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    as indicated by message : the resultset keeps more than 1 row - try to change the sql : before the update, so you get only 1 row..
    then execute the update..
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  6. #6
    Join Date
    Sep 2015
    Posts
    4

    Still receiving the same error "null value not allowed"

    Hi,

    You have to update only those records that have matching records. Otherwise, you will get above error when there is no matching record. See the change SQL below.


    Satya..


    UPDATE FRAUDINV/CFLPE A SET (A.PEFLEX1CD,A.PEFLEX1AMT,A.PEFLEX1PCT,
    A.PEFLEX2CD,A.PEFLEX2AMT,A.PEFLEX2PCT,
    A.PEFLEX3CD,A.PEFLEX3AMT,A.PEFLEX3PCT,
    A.PEFLEX4CD,A.PEFLEX4AMT,A.PEFLEX4PCT,
    A.PEFLEX5CD,A.PEFLEX5AMT,A.PEFLEX5PCT)
    = (SELECT B.PEFLEX1CD,B.PEFLEX1AMT,B.PEFLEX1PCT,
    B.PEFLEX2CD,B.PEFLEX2AMT,B.PEFLEX2PCT,
    B.PEFLEX3CD,B.PEFLEX3AMT,B.PEFLEX3PCT,
    B.PEFLEX4CD,B.PEFLEX4AMT,B.PEFLEX4PCT,
    B.PEFLEX5CD,B.PEFLEX5AMT,B.PEFLEX5PCT
    FROM FRAUDINV/CFLPE B,FRAUDINV/CAMPA C
    WHERE A.PEFLPROP = B.PEFLPROP
    AND A.PEFLPROP = C.PAPROP
    AND B.PEFLEX1CD <> '' AND
    AND B.PEFLPYR = INTEGER(C.PAPUD7 -1)
    AND C.PAPUD7 > '2008')
    where exists
    (SELECT 1
    FROM FRAUDINV/CFLPE B,FRAUDINV/CAMPA C
    WHERE A.PEFLPROP = B.PEFLPROP
    AND A.PEFLPROP = C.PAPROP
    AND B.PEFLEX1CD <> '' AND
    AND B.PEFLPYR = INTEGER(C.PAPUD7 -1)
    AND C.PAPUD7 > '2008')

  7. #7
    Join Date
    Sep 2015
    Posts
    4

    Still receiving the same error "null value not allowed" after adding "where exists"

    Satya,

    After adding the Where Exists to the sql I'm still getting the null values not allowed message.
    I am at a loss, I thought this would be an easy update.
    Thank you so much for your help.

  8. #8
    Join Date
    May 2010
    Location
    India
    Posts
    84
    Provided Answers: 2
    Looks like there are columns that allow null in source table and null not allowed in target table columns. Add condition in where clause to exclude those records in source table where column value is null.

    AND B.PEFLEX1CD is not null
    .
    .
    .
    AND B.PEFLEX5CD is not null

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •