How to find No of rows that a query has returned?

Database
Enthusiast

How to find No of rows that a query has returned?

hi

i want to find the no of rows returned by a select query.
based on the no of rows returned i have to run another query.
i.e if the query returns one row then a query should be run and if it returns more than one row another query should be run..

it should be like this..

select * from table1

if no of rows returned = 1 then

select * from table 2

else if no of rows returned > 1 then

select * from table 3

end if

i dont know whether we can use if conditions like this in between queries, but my requirement is like this only. please help me on this.
7 REPLIES
Enthusiast

Re: How to find No of rows that a query has returned?

if you are using bteq you can use the bteq variable ACTIVITYCOUNT to do this.

.IF ACTIVITYCOUNT = 0 THEN

you might also have to the bteq .GOTO command to get your logic into bundle.

refer the bteq manual for detailed syntax descriptions and samples.
Enthusiast

Re: How to find No of rows that a query has returned?

hi

thanks for the reply. but the problem is i want this to run in Query man or some tool used for running the Sql queries. i m not using BTEQ. Is there any way to use conditions in SQL directly? please let me know.
Enthusiast

Re: How to find No of rows that a query has returned?

The standard (?) SQL is a set oriented language and does not have provision for procedural logic like IF THEN processing...

You can however try doing this inside a stored procedure, which can make use of IF condition checks (the variable is called ACTIVITY_COUNT there)

And then you should be able to call the stored procedure from a SQL client like queryman/bteq
Enthusiast

Re: How to find No of rows that a query has returned?

You could potentially wrap your SQL within a Stored Procedure which you can execute from within Queryman, this would allow you to have the conditional statements which you will require. Obviously this will require you to precompile the procedure onto Teradata before you run the script.

I would however strongly advise you to use BTEQ and the ActivityCount statement previously described, this will allow you much greater flexibility going forwards and would not be limited to sequential (single amp) processing.

Good Luck.
Enthusiast

Re: How to find No of rows that a query has returned?

select columnlist from B where 1<=(select count(*) from A)

union

select columnlist from C where 0=(select count(*) from A)

from the above query at any time one of the condition will get satisfy.

the first select statemetn will get execute when count is greater than or equal to one and second select stmt will get execute only when coun is zero.

only condition is column list must be the same in both the select statement
Enthusiast

Re: How to find No of rows that a query has returned?

folks thanks for all the replies. i found them all useful. thank you once again.
Enthusiast

Re: How to find No of rows that a query has returned?

could you pls give me an equivalent function in Teradata similar to ROWCOUNT in SQL ?