All Forums Database
abhi_insignia 13 posts Joined 06/11
20 Aug 2013
Like Operator in Teradata

Hello,
 
While fetching information from dbc.columns, I am using a filter condition in the where clause - databasename like 'H%DB'. It should retrieve details of all  those databases that starts with H and ends with DB with any number of characters in between. However, the query is returning result for only one database and not for all the databases that starts with H and ends with DB. Any clue what is happening here? or am i missing some basic concepts of Like operator :)

Adharssh 36 posts Joined 08/13
20 Aug 2013

Hi, 
You can use the Query like this, 
SEL * FROM DBC.COLUMNS WHERE DATABASENAME LIKE 'H%' AND DATABASENAME LIKE '%DB';
From My understaing, the Problem in the Query is that TD will look for the Column starting with position H followed by any character and then ending with  DB. That is, it will look for values like H1DB,HADB,H6DB,etc.

Thanks & Regards,

Adharssh.
 

Share the Knowledge. Feel the Happiness, When you share/Teach it.

ulrich 816 posts Joined 09/09
20 Aug 2013

Hi,
databasename has length 30 so like'H%DB' will only work for databasenames which are actually 30 characters long.
an so use trim(databasename) like 'H%DB'
Ulrich

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

CarlosAL 512 posts Joined 04/08
20 Aug 2013

Hi.
DBC.Columns.DatabaseName is CHAR(30). Trailing blanks matter!
Cheers.
Carlos.

dnoeth 4628 posts Joined 11/04
21 Aug 2013

Hi Adharssh,
instead of TRIM(DatabasesName) you can also switch to dbc.ColumnsV instead where DatabaseName is a VARCHAR(128).
Since TD12 there's the new set of dbc views all ending on V or VX, the old ones are for backward compatibility only, e.g.
dbc.Columns & dbc.ColumnsX - old, don't use
 
dbc.ColumnsV & dbc.ColumnsVX - new, recommended

In TD14.10 you must use the new version when you want to implement the new long table names.

 

Dieter

Dieter

Adharssh 36 posts Joined 08/13
23 Aug 2013

Hi Dieter,
I didn't post the question. I just replied saying we can use TRIM.
Very valuable information about the dbc.columnsV & dbc.columnsVX. I am actually working in TD 13. When i see tables like dbc.columns & dbc.columnsV.
I thought first one is a Table version and the second one is a View version of the former table. I didn't check the Databasename length and all.
Thanks for the information.

Share the Knowledge. Feel the Happiness, When you share/Teach it.

You must sign in to leave a comment.