ISQL in Script - Formatting problems 2

[dickiebird]

Hi Guys
I have 1 or 2 little problems running this script - hope you can help :-

isql -Usa -Psa <<END
set nocount on
set rowcount 0
select ltrim(rtrim(substring(agent_code, 2, 9))),
&quot;|&quot;,
agent_type,
&quot;|&quot;,
short_na
&quot;|&quot;,
sales_centre_code,
&quot;|&quot;,
sl2.description,
&quot;|&quot;,
sl3.description,
&quot;|&quot;,
sl4.description,
&quot;|&quot;,
postal_add1,
&quot;|&quot;,
postal_add2,
&quot;|&quot;,
postal_add3,
&quot;|&quot;,
postal_add4,
&quot;|&quot;,
isnull(postal_add5, &quot; &quot,
&quot;|&quot;,
isnull(agent_zip,&quot; &quot
from agent a, sl2, sl3, sl4
where status = 1
and sl4.sl4 = a.sl4
and sl4.sl3 = sl3.sl3
and sl3.sl2 = sl2.sl2
go
END
It runs fine - gets all I want, but......
I have the column headings and underlines once at the top - which I don't want. The output is also split into 7 lines per agent - there's NL/CR(0D 0A) around col 80 or earlier - I would like 1 row of 402 chars, per agent.
Sample output :
07949 | B | HSBC BK BRASIL NITEROI CANX 22 |
LATIN AMERICA | SAO PAULO | CLOSED AGENTS-BRAZIL |
HSBC BANK BRASIL S.A. |
RUA GALVAO, 148 - LJ. 112 - BLOCO I |
24421-030 - NITEROI - RJ |
BRAZIL |
| 24421 030


I could tidy up in the script afterwards with some shell commands, but it'd be 'better' to know how to create clean output from the SQL first.
Anyone any thoughts ??
TIA ;-) Dickie Bird
db@dickiebird.freeserve.co.uk

 

[qyllr]

hello,

i can't think of a way you can clean up the header... i also use shell commands to clean this up...

for your lines try the following:

isql -Usa -Psa -w200 <<END

hth,
q.

 

[dickiebird]

Hi Qyllr
Great result on the output of lines - I used -w255 (which
I think is the max)
Since learnt that isql -b will knock off the header columns and the ---------'s
So have a star !
Dickie Bird
db@dickiebird.freeserve.co.uk

 

[JMCraig]

Another way to deal with something that you want to be longer than is allowed by the -w parameter is to put the data into a temp table and then use BCP to pull it out. You can specify the fixed widths for the columns and that'll give you very similar output to what you get from ISQL; since your vertical bar separators will end up in separate columns.

CFN,

JMC J M Craig
Alpha-G Consulting, LLC

http://alphagconsulting.bizhosting.com

nsjmcraig@netscape.net