Sybase Query – How to Sort Extraction by Well-Formed Columns

performancequery-performancesybaseunix

community
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 desired output would be:

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:

Thanks in advance for your ideas

Best Answer

Try adding-w parameter, this would dictate size of window. You may use -w99999 and output can be copied to a text editor. You could see that in the correct or desired format.

This seems to be working and hence adding it as an answer.

Related Solutions

Sql-server – How compatible are Sybase and SQL Server clients connecting to the alternate back end

It's true that Sybase and the Microsoft SQL Server databases share common roots, but they are not any more compatible.

Yes, both engines support T-SQL, they have very similar syntax, even many functions looks and works the same. But this is not enough, between the two products have appeared too many differences at this moment. If you want to migrate between them you need to review code, it's easier because many similarities, but must be done.

Microsoft recommended to use SQL Server Migration Assistant (SSMA) for Sybase - it can help you migrate tables, views, indexes, triggers, and stored procedures. In most cases you need to perform just a few manual patches.

Microsoft provided many helpful resources within subject of Sybase and SQL Server:

Optimize Sort in Postgres Query – How to

lat and lng are obviously numbers, so you should store them as appropriate numeric data type, not as text. That makes for smaller storage and disallows invalid input, it simplifies your query syntax and is also a bit faster overall. It's not going to do much for your query, though.
For instance:

lat             | numeric
lng             | numeric

So mtb_tenshu has store types. But if we can assume referential integrity and no row is excluded by the join, the table still is just noise in the presented query since you don't use it at all.

So we have this simplified query:

SELECT tm.* -- return only columns that are needed!
       0 as kyori
     , ((139.745069 - lng) / 0.0111 * 1000000)^2
     + (( 35.662978 - lat) / 0.0091 * 1000000)^2 AS kyori2 
FROM   ttb_members tm
WHERE  view <> 0
AND    type IN (2,3)
AND    delete_datetime IS NULL
ORDER  BY kyori2
OFFSET 20
LIMIT  20;

And since you commented:

All of the "AND" statements are constant.

You can support this very efficiently with a partial, functional index:

CREATE INDEX ttb_members_foo_idx ON ttb_members
 ((((139.745069 - lng) / 0.0111 * 1000000)^2
 + (( 35.662978 - lat) / 0.0091 * 1000000)^2));
WHERE  view <> 0
AND    type IN (2,3)
AND    delete_datetime IS NULL;

The expression and the WHERE conditions must be matches in the query to use this index.

Now Postgres can just skip the first 20 index entries and fetch the next 20 readily sorted in an index scan. Should be very fast.

Best Answer

Related Solutions

Sql-server – How compatible are Sybase and SQL Server clients connecting to the alternate back end

Optimize Sort in Postgres Query – How to

Related Question