Session info

Database

Session info

Hi!

How do I find if the session in ANSI/Teardata mode?
Are there any differences in the SQL's for both?

I actually have a code that checks for 'ABC' but the data is 'Abc'. Only when I use UPPER('Abc') it returns rows.
I have read that in ANSI mode the case sensitivity comes in to picture. So want to check the mode of my session.
And how do I change the mode?

Thanks!
4 REPLIES

Re: Session info

You can come to know whether session is ANSI/Teradata by typing ".show control" on BTEQ prompt where you can observe
[SET] SESSION TRANSACTION = BTET which indicates Teradata mode

you can change by ".set session transaction ANSI" for ANSI mode.

Thanks

N/A

Re: Session info

HELP SESSION -> Transaction semantics

SELECT transaction_mode FROM dbc.SessionInfoV -- maybe SessionInfoVX
WHERE SessionNo = SESSION;

Dieter

Re: Session info

Thanks Dieter!
But my transaction_mode is 'T', which I think means Teradata. So why is my code case sensitive?
It does not return rows when I check for 'Abc', however it returns rows when I convert it to UPPER.
N/A

Re: Session info

Any comparison in ANSI mode is case sensitive by default, whereas Teradata mode is case insensitive.

Do a SHOW TABLE and check the column definition, it's probably CASESPECIFIC.

Now you got three posibilites:
1. WHERE UPPER(col) = 'ABC', as you already did
2. WHERE col (not casespecific) = 'ABC'
3. change the column definition to NOT CASESPECIFIC

Dieter