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 :)
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.
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'
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
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.