Expanding the column size

Database

Expanding the column size

All,
I have a tabel that has a column defined as varchar(20)
I expanded this column to varchar(30)
after expanding the column it adds FORMAT 'X(20)' to the column
Here is the scenario.

create table devdata.feroz_testing
(emp_no char(10)
, emp_name varchar(20)
)
primary index (emp_no);

show table info:

CREATE SET TABLE devdata.feroz_testing ,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT
(
emp_no CHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC,
emp_name VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC)
PRIMARY INDEX ( emp_no );

now i altered the table to expand the emp_name from 20 to 30

alter table devdata.feroz_testing
add emp_name varchar(30);

show table info:
CREATE SET TABLE devdata.feroz_testing ,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT
(
emp_no CHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC,
emp_name VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC FORMAT 'X(20)')
PRIMARY INDEX ( emp_no );

it expands the column and then add this part FORMAT 'X(20)'
why does it do this?
I was able to add rows with char length 30 and display the details with no issue.

6 REPLIES

Re: Expanding the column size

Hi - Did anyone answer this question? I was wondering the same thing.

Re: Expanding the column size

I think I hit this before. If I remember correctly, when I ALTEREDed the table, I had to include the FORMAT 'X(30)' clause and then it didn't show up in the SHOW TABLE. Seems like a little bug, but I didn't have time to submit it.

CREATE TABLE db.PhoneList,
NO FALLBACK,
NO BEFORE JOURNAL,
NO AFTER JOURNAL
(
Number CHAR(10) NOT NULL,
Take VARCHAR(10)
)
UNIQUE PRIMARY INDEX( Number );

SHOW TABLE db.phonelist;

This doesn't have the format statement. OK so far.

ALTER TABLE db.Phonelist
ADD Take VARCHAR(20);

SHOW TABLE db.phonelist;

Now, this has the format statement.

If you drop and recreate the table and run the ALTER like this:

ALTER TABLE db.Phonelist
ADD Take VARCHAR(20) FORMAT 'X(20)';

SHOW TABLE db.phonelist;

Then the FORMAT statement is gone from the SHOW TABLE.

So, when you ALTER a VARCHAR, include a FORMAT 'X(n)' WHERE n is the new size.
N/A

Re: Expanding the column size

It is not a bug.
When you define the table (or add a new column) it sets appropriate defaults - Format x(20) in this case for a Char(20) or Varchar(20) column.
When you change a tables properties, it only changes the properties you specify. As you only changed the columnsize, it had no reason to change the format. (This is why you can specify a column as Null(able) on an alter tabe, but not when you initially create the table or column.)
I should not worry too much about it though - most BI tools do not use the format - they are only used as defaults in BTEQ or ITEQ.

Re: Expanding the column size

hi,guys give me query below the requriment 

Emp-name containing the letter 'a' 3 times Not necessarily consecutively  the letter 'a' should appear three times in the name ....any where, NOT together. 

Not '..aaa...but '..a..a...a'  like 'Ramana'

N/A

Re: Expanding the column size

Is there any relation to the existing topic? No? So why don't you open a new topic?

where col like '%a%_%a%_%a%'

Re: Expanding the column size

Hi,

After the modification of table: VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC FORMAT 'X(20)' says that it's previous/ex/X length was VARCHAR(20) which has now been modified to VARCHAR(30).

It's making the history for the devolopers to see it the column was ever changed.

Thanks ;)