How to change CASE NOT SPECIFIC to CASE SPECIFIC for PI fields

Database
N/A

How to change CASE NOT SPECIFIC to CASE SPECIFIC for PI fields

Hi,

I need to change a PI field constraint CASE NOT SPECIFIC to CASE SPECIFIC, without droping the table or deleting the data in the table.

Any pointers please..

Thanks,
Nalini
4 REPLIES

Re: How to change CASE NOT SPECIFIC to CASE SPECIFIC for PI fields

Hi Nalini,

Do you really need to change this attribute in the PI column? I think that the only way to change that is recreating the table. Probably the case sensitive attribute might change the hash bucket for the PI column.

Anoter way is change the case sensitive attribute when performing the query.
Below a simple example.

CREATE VOLATILE TABLE VT_TEST
(
COL1 VARCHAR(10) CASESPECIFIC
)
PRIMARY INDEX (COL1)
ON COMMIT PRESERVE ROWS
;

INSERT INTO VT_TEST ('A');
INSERT INTO VT_TEST ('a');

SELECT *
FROM VT_TEST
WHERE COL1 (NOT CASESPECIFIC) = 'A';

SELECT *
FROM VT_TEST
WHERE COL1 = 'A';

I hope that this can help you.

Regards,

Cruz

Re: How to change CASE NOT SPECIFIC to CASE SPECIFIC for PI fields

You can't change PI after you have create the table,
as it will cause the data to redistribute,

the better way is to drop the table,
If no more choice ,you can convert it in SQL, but it not the prefered way for efficiency,

Re: How to change CASE NOT SPECIFIC to CASE SPECIFIC for PI fields

How would I select from a casespecific table to a non case specific table (field)

I tried select upper(myfield) from mytable 

no worky. Is there a function that translates?

Why wouldn't this be built in to the system?

N/A

Re: How to change CASE NOT SPECIFIC to CASE SPECIFIC for PI fields

I don't understand what you're trying to achieve.
An Insert/Select?
Could you post the SQL which is "not working"?

Dieter