Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2003
    Posts
    49

    Unanswered: DB2 alter table statement with if

    Simple but getting stuck

    my file test.sql contains the script

    if 1=1 then
    alter table test10 add constraint pot primary key(col1);
    end if;
    @

    and I execute in the command prompt
    db2 -td@ -vfd:\db2\test.sql

    The error I get is

    if 1=1 then alter table test10 add constraint pot primary key(col1); end if;
    DB21034E The command was processed as an SQL statement because it was not a
    valid Command Line Processor command. During SQL processing it returned:
    SQL0104N An unexpected token "if 1=1 then" was found following
    "BEGIN-OF-STATEMENT". Expected tokens may include: "<space>".
    SQLSTATE=42601

    Any help is appreciated,
    Asha

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Asha,
    I know this was partially addresses in another of your posts, but the IF statement is only valid in Stored Procedures; not in a script. Even if it was alllowed, the argument in the other post is valid. Why have the IF anyway since it is always true?

    Just leave out the IF and just execute the ALTER statement.

    Andy

  3. #3
    Join Date
    Feb 2003
    Posts
    49
    Hi Andy,

    I know it has been replied to in my other posts, but what I am trying to do is, try to find out if the constraint it existing. If it is not then create it. These are actually patches that have to run in clients place and sometimes the same patch needs to be applied. I wanted the check so that errors do not occur at the client place. I do not want to catch the exception as then the script size will increase. I want the script to be neat.

    So I type this code in a file and saved it

    if (select count(*) from SYSIBM.SYSCONSTDEP where DCONSTNAME='ppok')=0 then
    alter table test10 add constraint ppok primary key(col1);
    end if;

    and when executing it, it gives me an error....

    I know u have replied that only stored procedures can have if statements but is there any other way...

    Thanx in adv,
    Asha

    Originally posted by ARWinner
    Asha,
    I know this was partially addresses in another of your posts, but the IF statement is only valid in Stored Procedures; not in a script. Even if it was alllowed, the argument in the other post is valid. Why have the IF anyway since it is always true?

    Just leave out the IF and just execute the ALTER statement.

    Andy

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Asha,
    I think you can just write the script without the checking.
    Just add the PK and if it already exists then ignore the error (i.e.
    do not use the -s option when you execute the script). If you
    write the output to a log file (-z option) then you can check that
    for the errors manually. THe only other way is to write your own program to do it.

    Andy


    Originally posted by asha192
    Hi Andy,

    I know it has been replied to in my other posts, but what I am trying to do is, try to find out if the constraint it existing. If it is not then create it. These are actually patches that have to run in clients place and sometimes the same patch needs to be applied. I wanted the check so that errors do not occur at the client place. I do not want to catch the exception as then the script size will increase. I want the script to be neat.

    So I type this code in a file and saved it

    if (select count(*) from SYSIBM.SYSCONSTDEP where DCONSTNAME='ppok')=0 then
    alter table test10 add constraint ppok primary key(col1);
    end if;

    and when executing it, it gives me an error....

    I know u have replied that only stored procedures can have if statements but is there any other way...

    Thanx in adv,
    Asha

  5. #5
    Join Date
    Feb 2003
    Posts
    49
    Hi Andy,

    Thanks, I think that is a good solution. I will try that.

    Thanks
    Asha

    Originally posted by ARWinner
    Asha,
    I think you can just write the script without the checking.
    Just add the PK and if it already exists then ignore the error (i.e.
    do not use the -s option when you execute the script). If you
    write the output to a log file (-z option) then you can check that
    for the errors manually. THe only other way is to write your own program to do it.

    Andy

  6. #6
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Originally posted by ARWinner

    I think you can just write the script without the checking.

    You could also switch error checking on and off dynamically within the script if you wish to control errors on other statements.

    Code:
    update command options using s off;
    alter table ...;
    update command options using s on;
    -- other statements

  7. #7
    Join Date
    Mar 2003
    Posts
    343
    how do you do that?

Posting Permissions

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