need a associated select query defined from view table in teradata?

Database
Enthusiast

need a associated select query defined from view table in teradata?

Hi Gurus,

I have an issue trying to get the associated select query (couple of tables - whole query) defined from view table in teradata?

Issue is, i am not getting the whole select query in SQL Assistant instead i am getting the Half of query in Answer Sheet

i have tried from the below dbc tables but having no luck on this.

Dbc.Tables

SHOW SELECT * FROM view1

dbc.tvm

Anyone can assist on this to get the whole select query which defined from view table

Thanks

7 REPLIES
Enthusiast

Re: need a associated select query defined from view table in teradata?

Here is the example i am facing the issue from SQL Assistant 

Select * from View1
SHOW QUALIFIED Select * from View1
select t1.col1
,t1.col2
,t1.col3
,t2.col1
,t2.col2
,t2.col3
,t3.col1
,t3.col2
,t3.col3
from table1 t1
join table2 t2
on t1.col1 = t2.col1
join table3 t3
on t2.col1 = t3.col1
join table4 t4
on t3.col1 = t1.col1

i am getting query from SQL Assistant in Answer Sheet

select t1.col1
,t1.col2
,t1.col3
,t2.col1
,t2.col2
,t2.col3
,t3.col1
,t3.col2
,t3.col3
from table1 t1
Enthusiast

Re: need a associated select query defined from view table in teradata?

Can you please elaborate what exactly you are looking for?

If you just want to see the SQL (View DDL) then why don't you use SHOW VIEW?

SHOW VIEW View1;
Enthusiast

Re: need a associated select query defined from view table in teradata?

Hi,
The easiest way is to export the results. It is available in "File..". You can run the query and it will ask you the directory where you want to place. Hope it helps.Cheers,
Raja
Enthusiast

Re: need a associated select query defined from view table in teradata?

I got your problem, I have faced this many times, SQL Assistant sometimes trim the result of show view or table due to its length. 

What you can do is to use administrator, go to your database and then right click on the desired view and click show definition. 

Here you will get the complete definition of view :)

Khurram
Enthusiast

Re: need a associated select query defined from view table in teradata?

@ Khurram

Thanks.. i just tried many options and googled and finally i posted here

This is from Prod so we don’t have the administrator access

so we need to raise the request to get the details

i am just trying , Is there any chances of getting this..

@ Kawish Siddiqui -

i have tried many options to get that but no luck

SHOW VIEW View1;



Dbc.Tables
SHOW SELECT * FROM view1
dbc.tvm
Enthusiast

Re: need a associated select query defined from view table in teradata?

Its pretty strange, did you try same thing using BTEQ? Which provider you are using to connect SQLA? Is it ODBC or .Net Provider?

Enthusiast

Re: need a associated select query defined from view table in teradata?

Yes, Kawish is right, I have read somewhere that ODBC also has a limit for string to show.

I was just thinking of one possiblity, can we cast the result of SHOW Command?I think if its possible then it will not truncate results.

Khurram