Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2003
    Posts
    7

    Thumbs down Unanswered: modify smallint column to boolean

    Hi all,

    I am in a major problem...
    Earlier we have created one column as smallint.
    Now we have been asked to convert it to boolean.
    I fire following statement ....
    alter table user modify(is_consignor boolean);

    This gives me error message....
    9633: ALTER TABLE can not modify column (is_consignor) type.
    Need a cast from the current type to the new type.

    At present their are no values in this columns.
    In fact I have made them all "null".

    I have to do this ASAP.

    Please SMA.

    Thanks in advance.

    Bhushan Sawant

  2. #2
    Join Date
    Nov 2003
    Location
    Russia
    Posts
    7

    by adding new column

    Hi!

    alter table <u table> add bcol boolean;
    update <u table> set bcol=<some cast from old column>;
    alter table <u table> drop <old column>;

    4 u plesure ))

  3. #3
    Join Date
    Nov 2003
    Posts
    7

    Re: by adding new column

    Originally posted by Suguby
    Hi!

    alter table <u table> add bcol boolean;
    update <u table> set bcol=<some cast from old column>;
    alter table <u table> drop <old column>;

    4 u plesure ))
    Thanks for the solution you have provided...
    But problem is that if I create another column after dropping first one the syntax which programmers has written will fell. Because the new column will be added at the last position of the table.
    When I tried using dbaccess utility , it dosen't have boolean data type.

    What I should do....help me out....

  4. #4
    Join Date
    Nov 2003
    Location
    Russia
    Posts
    7
    whict type of column?

    try
    (on test copy of BD!!!)
    select distinct <column> from <u table>

    after this modify values in <column> to 1 or 0 by update series:

    update <u table> set <column>=0 where <column>=...
    update <u table> set <column>=1 where <column>=...
    ...

    and try change type in dbaccess

    may be this will be work

  5. #5
    Join Date
    Nov 2003
    Posts
    7
    Originally posted by Suguby
    whict type of column?

    try
    (on test copy of BD!!!)
    select distinct <column> from <u table>

    after this modify values in <column> to 1 or 0 by update series:

    update <u table> set <column>=0 where <column>=...
    update <u table> set <column>=1 where <column>=...
    ...

    and try change type in dbaccess

    may be this will be work
    What is the data_type I should select from dbaccess utility. Because I couldn't find "boolean" data type in the list.

  6. #6
    Join Date
    Nov 2003
    Location
    Russia
    Posts
    7
    just use "alter table" - dbaccess is not for all lifetime cases...

  7. #7
    Join Date
    Aug 2003
    Location
    India
    Posts
    262
    Originally posted by Bhushan_sawant
    What is the data_type I should select from dbaccess utility. Because I couldn't find "boolean" data type in the list.
    Hi Bhushan,

    A very easier way to do is get a unload of your table first, then take a schema of the table using dbschema and store it in a file, make changes to that file. now go to dbaccess, drop that table and run this script and then load the data.

    Regards,

    lloyd

Posting Permissions

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