Search code examples
unixoutputsybasecolumnheader

Sybase query: How can I sort the extraction (by well formed columns)

I built a Sybase query statement in a script (using the isql command/line ) below is the code:

My script contains: 

isql -U databasename_dba -P password  <<EOF!
select  customerid, notional, counterparty2 from table_1  inner join  table_2
on id = id2 
go
quit
EOF!

I'm getting this:

My output

My desired output would be :

Desired output

Thinking that extracting just the data (excluding the headers) might fix the output. I added in my code -b 

isql -U databasename_dba -P password  -b <<EOF!

but the output doesn't change that much enter image description here

Thanks in advance

Solution

  • Firstly you can pass in the -w parameter to give the width of the row so you don't get it overlapping across lines (e.g. -w2000)

    The isql tool will return the column width dependent on the width of the actual table column so if for example you defined it as a numeric 18 but only use 12 digits you'll still get 18 characters in the output. You can use a rtrim(customerid) depending upon datatype or you can use convert(varchar(12), customerid). I suspect someone may have defined it as 50 chars or more possibly. You can repeat this as required for each column.

    You can also consider using sp_autoformat and see if this helps - http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc36273.1570/html/sprocs/BABHCBIG.htm