Results 1 to 13 of 13
  1. #1
    Join Date
    Jan 2013
    Posts
    9

    Unanswered: Help please - Update/case statement

    I'm trying to add and then update a new field on a table like so:

    EXEC SQL
    ALTER TABLE HMSG.HMKYMDET
    ADD KYMDET_ENTRYTYPE SMALLINT
    ADD KYMDET_CODE CHAR(16)
    ADD KYMDET_DESC CHAR(20)
    ADD KYMDET_SUBKEY SMALLINT
    END-EXEC.

    EXEC SQL
    UPDATE HMSG.HMKYMDET
    SET KYMDET_ENTRYTYPE =
    CASE
    WHEN KYMDET_ITEMCODE = " " THEN 1
    WHEN KYMDET_SUBMENU = 0 THEN 2
    ELSE 0
    END
    END-EXEC.


    I got the technique from here:
    http://www.dbforums.com/db2/1214780-...statement.html

    but it doesn't seem to be working.

    Any help appreciated.

    regards

    Ian

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    You need to supply more information. What DB2 version and OS are you using? Are you getting an error? What does "it doesn't seem to be working" mean.

    Andy

  3. #3
    Join Date
    Jan 2013
    Posts
    9
    LINE MESSAGES FOR EXPSRCE.SQB
    ------ --------------------------------------------------------
    SQL0060W The "COBOL" precompiler is in progress.
    3570 SQL0206N "KYMDET_ENTRYTYPE" is not valid in the context
    where it is used. SQLSTATE=42703
    SQL0095N No bind file was created because of previous
    errors.
    SQL0092N No package was created because of previous
    errors.
    SQL0091W Precompilation or binding was ended with "3"
    errors and "0" warnings.

    This is the compiler error I receive. Version 9.7 DB2 I think and win 7, although I don't think that's relevant. Just a pesky syntax error I think.

    Your help is very much appreciated.

    Ian

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Did the first statement get committed before you try the second?

    Andy

  5. #5
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1
    When you add a column, it can have a DEFAULT, CHECK() constraints, etc. and you need to consider them. How can you have a KYMDET_ITEM_CODE = ' ' ? In a valid data model all possible values of "<attribute>_code" should be defined and you would never use a blank or empty string. Doesn't it start as a NULL, then have to be modified?

    Think about how silly a blank postal_code would be! Slow steps, good design and get everyone else out of the schema while you do it.

  6. #6
    Join Date
    Jan 2013
    Posts
    9
    Andy

    No I didn't issue a commit between the statements. However, this is just a compile so the pre-processor wouldn't know that the flow of my program would create the column BEFORE the statement in question. Maybe its not possible to do what I want here? Or does the pre-processor work out that the new column has been created and allow it?

    Celko

    The data here has come from a non-relational system (ISAM) where a value of " " is valid and does actually mean something.

    Ian

  7. #7
    Join Date
    Jan 2013
    Posts
    9
    I have simplified my SQL and now have a test bit of code just to establish if my UPDATE is syntactically correct:

    EXEC SQL
    UPDATE HMSG.HMKYMDET
    SET KYMDET_SUBMENU =
    CASE
    WHEN KYMDET_ITEMCODE = "X" THEN 1
    WHEN KYMDET_SUBMENU = 0 THEN 2
    ELSE 0
    END
    END-EXEC.

    I now get this error:

    LINE MESSAGES FOR EXPSRCE.SQB

    ------ --------------------------------------------------------------------

    SQL0060W The "COBOL" precompiler is in progress.

    3572 SQL0206N "X" is not valid in the context where it is used.

    SQLSTATE=42703

    SQL0095N No bind file was created because of previous

    errors.

    SQL0092N No package was created because of previous

    errors.

    SQL0091W Precompilation or binding was ended with "3"

    errors and "0" warnings.

  8. #8
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    WHEN KYMDET_ITEMCODE = "X"

    use single quote : WHEN KYMDET_ITEMCODE = 'X'
    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

  9. #9
    Join Date
    Jan 2013
    Posts
    9
    Thanks przytula

    It shows how much of a novice I am at the moment.

    Ok, so now I have fixed the quotes problem I am getting to the nub of my problem. It seems I can't create a column and set it to a value? This code is failing:

    EXEC SQL
    ALTER TABLE HMSG.HMKYMDET
    ADD KYMDET_ENTRYTYPE SMALLINT
    END-EXEC.
    EXEC SQL
    UPDATE HMSG.HMKYMDET
    SET KYMDET_ENTRYTYPE = 1
    END-EXEC.

    The error now is

    3563 SQL0206N "KYMDET_ENTRYTYPE" is not valid in the context where it is used. SQLSTATE=42703

  10. #10
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    ADD KYMDET_ENTRYTYPE SMALLINT
    UPDATE HMSG.HMKYMDET SET KYMDET_ENTRYTYPE = 1
    at compile time, the column is not known (added just before in same appl) = so -206
    to avoid this problem :
    1.to this operation in spufi : first add column - next:update column
    2. use cobol program : declare/prep the update stmt as a dynamic sql. this will only be compiled at runtime and column will be known. see the doc for dynamic sql..
    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

  11. #11
    Join Date
    Jan 2013
    Posts
    9
    Przytula Guy

    Thanks for the tip. I have found another one of our legacy programs that does this kind of thing. I get it now in that the dynamic SQl can't be verified so it gets through the precompiler.

    Drop constraints, create new (temp) table, dynamic bit of SQl to write to the new table from the old, drop old table, rename temp to new one, create index, add primary key, add foreign key/constraints.

    Thanks

    Ian

  12. #12
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1
    Drop constraints, create new (temp) table, dynamic bit of SQl to write to the new table from the old, drop old table, rename temp to new one, create index, add primary key, add foreign key/constraints.
    I would also look for a way to repair those blank postal codes. In the US, Melissa Data can format mailing addresses to the CASS standards. Thre is no reason for that lack of darta quality today.

  13. #13
    Join Date
    Jan 2013
    Posts
    9
    Celko

    What????

Posting Permissions

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