CASESPECIFIC What is its purpose?

Database

CASESPECIFIC What is its purpose?

I am a newbie to Teradata; I have lot of experience with Oracle and SQL Server.

I am trying to understand what exactly is CASESPECIFIC?

I have created a table

CREATE MULTISET TABLE premtest.Employee

     (

      Name VARCHAR(80) CHARACTER SET LATIN NOT CASESPECIFIC,

      start_date date format 'MM/DD/YYYY'

)

PRIMARY INDEX ( Name )

;

Now I inserted two rows in the table from a file:

"Prem Mehrotra",06/23/2008

"Sushma Mehrotra",11/23/2012

My Teradata database is 14.x

I do see in database table  (when viewing from Teradata Studio Express),  that Name is in mixed case; i.e.

Prem Mehrotra

Sushma Mehrotra

So far so good. However when I query:

select * from premtest.EMPLOYEE where Name like 'SUshma%'

No rows are returned.

However when I query

Select * from premtest.EMPLOYEE where Name like ‘Sushma%’

A row is returned.

So what is the purpose of NOT CASESPECIFIC?

5 REPLIES
Teradata Employee

Re: CASESPECIFIC What is its purpose?

Hello,

It seems that you are working in ANSI mode. Please not that any comparison in 'ANSI' mode is case sensitive by default, whereas Teradata mode is case insensitive.

I checked your SQL in Teradata mode and I  got result for your below query as well:

select * from premtest.EMPLOYEE where Name like 'SUshma%'

A row is returned.

O/P:

 Name                          start_date

Sushma Mehrotra        11/23/2012

N/A

Re: CASESPECIFIC What is its purpose?

To get a case insensitive result both operands must be NOT CASESPECIFIC.

Any literal in an ANSI mode session is CASESPECIFIC by default, but NOT CS in a Teradata session.

To get the desired result in an ANSI session you must change that default:

select * from premtest.EMPLOYEE where Name like 'SUshma%' (NOT CASESPECIFIC)

Dieter

Re: CASESPECIFIC What is its purpose?

Dieter, Chinmay:

Thanks a lot.

I am actually using Terdata Studio Express 14.x on Suse Linux. By default., it probably uses ANSI mode. I have not found how to set it to use TRadata mode. Since I am coming from Oracle and SQL Server, it is probably better to use ANSI mode.

Dieter:

Yes, I could run your statement and get NOT CASESEFIC match.

N/A

Re: CASESPECIFIC What is its purpose?

You set the transaction mode in the connection string:

TMODE=ANSI/TERA/DEFAULT

Regarding "best mode" you should check your system's default. Most systems still use Teradata sessions and there are some important differences:

- Teradata defaults to SET tables, NOT CASESPECIFIC chars and silently truncates strings which exceed the target colunm's size.

Each request is by default an auto-commited transaction unless you use BEGIN/END TRANSACTION (BT/ET)

- ANSI defaults to MULTISET tables, CASEPECIFIC chars and returns an error when a string is trucated.

Transaction are handled like Oracle, the first request within a session opens a transaction which is explicitly commited using COMMIT.

Dieter

Re: CASESPECIFIC What is its purpose?

Dieter:

Thanks so much, I looked in JDBC Connecton parameter set in Terdata Studio Express and it was ANSI, but I now changed it to TERA, I can verify that results NOT  CASESPECIFIC

select * from premtest.EMPLOYEE where Name like 'SUshma%'