Hi Guys This is Sybase really, but

[dickiebird]

Hi Guys
This is Sybase really, but the same principle applies( I think ) across all DBs.
In a shellscript on a Unix server, I want to loop through an entire table and format the output ( if there was no need to format I'd just select * from tablename and output it to a file.)
Here's what I've tried - all I get is the last row of the table.

OUTFILE=/tempts/test2
isql -Usa -Psa > $OUTFILE <<END
use sdb
go

declare @printline1 varchar(55)
declare @agent varchar(10)
declare @short_na varchar(30)

/*Dummy to set @@rowcount > 0 ?? */
select min(agent_code) from agent

/* loop round all AGENTS */
while (@@rowcount != 0 )
begin
select @agent=ltrim(rtrim(substring(agent_code, 2, 9))),
@short_na=short_na
from agent

while (@@rowcount != 0 )
begin
select @printline1 = @agent + '|' + @short_na
print @printline1
end
end
go
END

This is a cut-down version - there's many more variables and inserted text - removed for clarity.
Anyone got any thoughts, please Dickie Bird
db@dickiebird.freeserve.co.uk

 

[grega]

Seems overly complex ...

To loop through records in a table in a SQL script (which is essentiall what you've written) use cursors.

Can you do this in a S/P?

Can you not dump the table contents to a file and use awk to format?

Greg.

 

[dickiebird]

I was sure there was a way - and I found it meself, thanks
(I knew my start and last agent codes) and selected 1 row at a time.......

declare @printline1 varchar(255)
declare @agenta varchar(10)
declare @agent varchar(10)
declare @short_na varchar(30)
set rowcount 1
set nocount on
select @agenta=&quot;300000&quot;
while(@agenta) < &quot;BZ9901&quot;
begin
select @agent=agent_code,
@short_na=short_na
from agent
where agent_code > @agenta
select @printline1 = @agent + '|' + @short_na
print @printline1
select @agenta=@agent
end
go

;-)
Dickie Bird
db@dickiebird.freeserve.co.uk