Results 1 to 3 of 3

Thread: Update Query

  1. #1
    Join Date
    Dec 2016
    Posts
    2

    Unanswered: Update Query

    Hi All,

    I have to update values in db2 tables , i have written below code, but this give me error. can anyone of you please correct my code. There are no NULL values in my code, i also tried to hard code values and run , but it still gives error.my inner query has no null values.


    Error:
    ====
    DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned:
    SQL0407N Assignment of a NULL value to a NOT NULL column "TBSPACEID=474, TABLEID=8, COLNO=13" is not allowed. SQLSTATE=23502




    Code:
    ======
    create view appdm.test_vw as
    select mbr_num,
    sex_cd,
    mbr_age,
    age_band_cd,
    svc_yr_mth,
    svc_yr

    from (
    select c.mbr_num,
    c.sex_cd,
    c.mbr_age,
    c.age_band_cd,
    c.svc_yr_mth,
    cast(substr(c.svc_yr_mth,1,4) as integer) as svc_yr,
    ROW_NUMBER() OVER (PARTITION BY c.mbr_num ORDER BY c.svc_yr_mth DESC) as rn

    from appdm.test_table c where MEMBER_MONTH_IND = 'Y' and c.svc_yr_mth <> ' '

    )
    where rn = 1 and mbr_age >= 0
    ;

    UPDATE appdm.test_table d
    SET (mbr_age, sex_cd, age_band_cd) =
    (SELECT cast(b.mbr_age - (b.svc_yr - cast(substr(d.svc_yr_mth,1,4) as integer)) as integer),
    b.sex_cd,
    CASE when b.mbr_age - (b.svc_yr - cast(substr(d.svc_yr_mth,1,4) as integer)) < 1 then 'A'
    when b.mbr_age - (b.svc_yr - cast(substr(d.svc_yr_mth,1,4) as integer)) between 1 and 17 then 'B'
    when b.mbr_age - (b.svc_yr - cast(substr(d.svc_yr_mth,1,4) as integer)) between 18 and 29 then 'C'
    when b.mbr_age - (b.svc_yr - cast(substr(d.svc_yr_mth,1,4) as integer)) between 30 and 39 then 'D'
    when b.mbr_age - (b.svc_yr - cast(substr(d.svc_yr_mth,1,4) as integer)) between 40 and 49 then 'E'
    when b.mbr_age - (b.svc_yr - cast(substr(d.svc_yr_mth,1,4) as integer)) between 50 and 59 then 'F'
    when b.mbr_age - (b.svc_yr - cast(substr(d.svc_yr_mth,1,4) as integer)) between 60 and 64 then 'G'
    when b.mbr_age - (b.svc_yr - cast(substr(d.svc_yr_mth,1,4) as integer)) >= 65 then 'H'
    END as age_band_cd

    FROM (select mbr_num,
    sex_cd,
    mbr_age,
    age_band_cd,
    svc_yr_mth,
    svc_yr
    from appdm.test_vw


    ) b
    WHERE d.MBR_NUM = b.mbr_num
    )
    WHERE d.cap_ind = 'Y' and
    d.member_month_ind = ' ' and
    d.phm_ind = ' ' and
    d.clm_ind = ' '
    ;



    commit;

  2. #2
    Join Date
    Jul 2016
    Location
    Moscow
    Posts
    261
    Provided Answers: 39
    Hi,

    select ... from appdm.test_vw b WHERE d.MBR_NUM = b.mbr_num
    returns no rows for some d.MBR_NUM probably.
    Regards,
    Mark.

  3. #3
    Join Date
    Nov 2004
    Posts
    76
    Try this select. It should give you the column name that creates the problem.

    db2 "select c.tabschema,c.tabname,c.colname from syscat.tables t,syscat.columns c where c.tabschema=t.tabschema and c.tabname=t.tabname and t.tbspaceid=your_tbspaceid and t.tableid=your_tableid and c.colno=your_colno"

Posting Permissions

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