Teradata SQL assistant: limiting the number of rows returned in "Export results"

Analytics

Teradata SQL assistant: limiting the number of rows returned in "Export results"

I'm running some pregenerated queries that contain several (100+) subqueries, each of which is returning data to (spawning?) a separate answerset window.
All of the answersets are to be copied to a single document as testing documentation. I know I can limit the number of rows returned for each answerset window (tools/options/Answerset/max numbers...) but I have so many answersets, I can't cut/paste each answerset to a single text document. I'm using Export Results to return the results from all the queries directly to a single text document, but then I lose the ability to limit the number of rows returned in each query. Is there a way to do that? I want to Export Results, but only keep the first 50 rows from each answerset.

Thanks
Mat
7 REPLIES
N/A

Re: Teradata SQL assistant: limiting the number of rows returned in "Export results"

Hi Mat,
it would be easy with BTEQ, e.g.

.set width 65000
.set retlimit 50
.set retcancel on
.export report file = xxx;

your queries here

.export reset

Dieter

Re: Teradata SQL assistant: limiting the number of rows returned in "Export results"

Can I translate those directly into my query?
I used the QueryBuilder and chose the SET statement, and it put this code into the workspace:
SET SESSION EXPORT FOR UNICODE DEFAULT;

can I edit that statement to do what you said?
N/A

Re: Teradata SQL assistant: limiting the number of rows returned in "Export results"

Hi Mat,
no, AFAIK you can't do that with Queryman, only with BTEQ :-(

But maybe you should post that question at www.teradataforum.com, because Mike Dempsey (who progammed Queryman) is monitoring it and maybe he knows an answer...

Dieter
Teradata Employee

Re: Teradata SQL assistant: limiting the number of rows returned in "Export results"

There is currently no way to limit the # rows returned by an Export in SQL Assistant.

There are only 2 options here:

1) Add the clause 'TOP 50' after the Select keyword in each of your statements. (Not good for 100s stmts.)

2) Change your Answerset options to write all resultsets to the same window. (A separate tab will be created for each resultset.) Then use the Save menu to save the entire Answerset window to an Excel workbook.
(Each tab will be a separate sheet in the workbook.)

N/A

Re: Teradata SQL assistant: limiting the number of rows returned in "Export results"

Hi all...

I am Sreekanth. I am learning Teradata and its interesting to know more.

I have a similar situation. I have SQL Assistant.

I have four queries and ten input strings(each string containing approx 55 account numbers). My plan is to put the 10 strings in a text file and give them as input to each of the queries. I should get the output of the query's execution into a text file or any other file.

Any ideas?

Regards,
SK
N/A

Re: Teradata SQL assistant: limiting the number of rows returned in "Export results"

Hi MikeDempsey,

I've encounter one problem during exporting the data via SQL Assistant.
I've connect the SQL Assistant to Oracle DB to export some of the data. During exporting the data, at some point i've got this error message "Error occurred during DoQuery-11. Code=63. Bad record number"

Appreciate your advice on this matter soonest possible.

Re: Teradata SQL assistant: limiting the number of rows returned in "Export results"

Hi all, I'm new to Teradata. Sorry if this is a very basic question. I want to export a table/ mail a table generated using a query into my local. Can this be done? Help would be appreciated

Thanks in advance