#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2012
    Posts
    24
    Rep Power
    0

    Change Data Type


    I am converting a text field to integer. Former field contained 'Yes' or 'No'. New type will be smallint 1 or 0. Have updated the old text fields to '1' or '0', and saved entire table to csv.

    Now want to import the csv to new table containing the smallint field.

    Will this work?
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2006
    Location
    Carlsbad, CA
    Posts
    2,057
    Rep Power
    383
    Now that you have done the hard work of converting, why don't you just try it?
    If you run into any problems then post the problems and ask for advice on how to overcome them.

    Clive
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2008
    Posts
    55
    Rep Power
    7

    Thumbs up Converting content FIelds for Types Diferents


    Look: DONT NEED SAVE THE TABLE IN THE EXTERNAL FILE AND IMPORT IT THE NEW VALUES!

    Before: Field type TEXT (char, varchar)
    After: Field type INTEGER, SMALLINT, BIGINT or floats

    Simple form:
    1 - Make one COLUMN for the new TYPE (in case, SMALLINT = 0 or 1 content OK)
    2 - For instante, do it = accept = NULL, after, change for not NULL if necessary = see if there is not record EMPTY value.

    3 - Use one UPDATE procedure for COPY the value of the OLD column for the NEW column. AS:

    UPDATE tablex
    set xNEWCOLUMN = 0 (OR 1)
    where xOLDCOLUMN = 'No' (OR 'False', etc...)

    NOTE: TRIM() function, can help if the value contain space before or after the text in the field. UPPER(), LOWER() for case Upper or Lower

    OR

    Update tablex
    set xNewColumn = IIF( xOldColumn='True', 1, 0)

    If the (xOldColumn = NULL) value you can use the function COALESCE( Expr1, Expr2, Expr3, ... ) for "catch" the first value NOT NULL

    AFTER ALL ITS OK, DROP the xOldColumn:

    queryxxxx.sql.text := 'ALTER TABLE tablex DROP xOldColumn;'
    queryxxxx.exec

    NOTE: Use need have privilage for ADMIN OK! (SYSDBA)

    I See, that you'll use one type BOOLEAN for this column, so, make a new DOMAIN for future values, as:

    CREATE DOMAIN BOOLEAN
    AS SMALLINT
    CHECK (value is null or value in (0, 1));

    and use for this xNewColumn after all its ok!


  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2012
    Posts
    24
    Rep Power
    0
    Thanks very helpful. Is there some simplification if I adopt the boolean domain name? I think smallint 1 or 0 is OK or is there a reason why it is better as boolean? Same thing?


    Originally Posted by emailx45
    Look: DONT NEED SAVE THE TABLE IN THE EXTERNAL FILE AND IMPORT IT THE NEW VALUES!

    Before: Field type TEXT (char, varchar)
    After: Field type INTEGER, SMALLINT, BIGINT or floats

    Simple form:
    1 - Make one COLUMN for the new TYPE (in case, SMALLINT = 0 or 1 content OK)
    2 - For instante, do it = accept = NULL, after, change for not NULL if necessary = see if there is not record EMPTY value.

    3 - Use one UPDATE procedure for COPY the value of the OLD column for the NEW column. AS:

    UPDATE tablex
    set xNEWCOLUMN = 0 (OR 1)
    where xOLDCOLUMN = 'No' (OR 'False', etc...)

    NOTE: TRIM() function, can help if the value contain space before or after the text in the field. UPPER(), LOWER() for case Upper or Lower

    OR

    Update tablex
    set xNewColumn = IIF( xOldColumn='True', 1, 0)

    If the (xOldColumn = NULL) value you can use the function COALESCE( Expr1, Expr2, Expr3, ... ) for "catch" the first value NOT NULL

    AFTER ALL ITS OK, DROP the xOldColumn:

    queryxxxx.sql.text := 'ALTER TABLE tablex DROP xOldColumn;'
    queryxxxx.exec

    NOTE: Use need have privilage for ADMIN OK! (SYSDBA)

    I See, that you'll use one type BOOLEAN for this column, so, make a new DOMAIN for future values, as:

    CREATE DOMAIN BOOLEAN
    AS SMALLINT
    CHECK (value is null or value in (0, 1));

    and use for this xNewColumn after all its ok!


  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2008
    Posts
    55
    Rep Power
    7

    Thumbs up


    Firebird, not HAVE, fields type BOOLEAN (as Paradox tables).

    So, the only way its make a field type NUMERIC (integer, smallint, Bigint) only.

    The name there is not have one rule, specificaly.

    Make a DOMAIN, you can, in future, chance the data type with a little bit problems in your database.

    FOr example:
    If the fields it SMALLINT, you can to chance for another type numeric: INTEGER, BIGINT, FLOAT (no will have decimals), etc...

    For fields = fields, you can to use the same DOMAIN, what its very good for chance various type fields in the same time.

    BUt..... stay alert with content of all fields heheheh
    -----------------------------------------------
    so, ALWAYS MAKE DOMAINS FOR YOUR FIELDS OK?
    -----------------------------------------------
    With Firebird and components FIBPLUS, you can make a DOMAIN for fields BOOLEAN, with name as:

    DOMAIN name: some_BOOLEAN_some

    Thats way, the components know that field is one type BOOLEAN, because of part of the name (BOOLEAN). SO...

    CREATE DOMAIN myBOOLEANdomain
    AS SMALLINT
    CHECK (value is null or value in (0, 1));


    CREATE DOMAIN mycharBOOLEANdomainname
    AS CHAR
    CHECK (value is null or value in ('f', 't', 'F', 'T'));

    I alway, do :

    CREATE DOMAIN d_BOOLEAN_type
    AS SMALLINT
    CHECK (value is null or value in (0, 1));

    Using numeric type, give you more chance for working with various possibility into your language as:

    1- use one field boolean for show one ICON in component cmpIMAGE.INDEX = (value)
    2- use type CHAR or STRING, for another other code needed in your program language.

    ok?

IMN logo majestic logo threadwatch logo seochat tools logo