[freetds] compute rows
matthew.green at datamartcomputing.com
matthew.green at datamartcomputing.com
Mon Feb 8 03:53:14 EST 2016
In case it helps:
Sybase 15.7 client with sqsh produces "predictable" results, with the correct headings.
On the other hand isql itself produced results more like fisql:
1> select * from tempdb..tab compute sum(col1), avg(col1), min(col1), count(col1), avg(col2), max(col3), min(col3)
2> go
col1 col2 col3
----------- ----------- -----------
1 1 1
2 2 2
3 3 3
Compute Result:
----------- ----------- ----------- ----------- ----------- -----------
-----------
6 2 1 3 2 3
1
Sybase on Windows (isql GUI) gives:
col1 col2 col3
----------- ----------- -----------
1 1 1
2 2 2
3 3 3
(3 rows)
Sum(col1) Avg(col1) Min(col1) Count(col1) Avg(col2) Max(col3) Min(col3)
----------- ----------- ----------- ----------- ----------- ----------- -----------
6 2 1 3 2 3 1
but isql from a DOS box gives the same output as Solaris.
All a bit of a mess, even when using the vendor version!
I tried to track the TDS stream with Ribo and I couldn't see any obvious difference in communication flow in regard to the results returned in each case. I'd therefore guess that the presentation must be a client side decision.
Cheers,
Matthew.
February 7 2016 1:50 PM, "Frediano Ziglio" <freddy77 at gmail.com> wrote:
> 2016-02-06 9:33 GMT+00:00 John Kendall <john at capps.com>:
>
>> I am having trouble with the output of compute rows. I found the following exchange from 2007
>> between jkl and Michael Peppler that summarizes what I'm experiencing in 2016:
>>
>> http://lists.ibiblio.org/pipermail/freetds/2007q1/020954.html
>>
>> To summarize, sqsh (and other tools linked to freetds) do not produce usable output for compute
>> rows. Michael Peppler says that sqsh acts correctly when linked to sybase libs. I have an old
> copy
>> of isql that handles compute statements in a very predictable way, for example:
>>
>> select col1=1, col2=1, col3=1 into tempdb..tab
>> insert tempdb..tab select 2,2,2
>> insert tempdb..tab select 3,3,3
>> go
>> select * from tempdb..tab compute sum(col1), avg(col1), min(col1), count(col1), avg(col2),
>> max(col3), min(col3)
>> go
>> col1 col2 col3
>> ----------- ----------- -----------
>> 1 1 1
>> 2 2 2
>> 3 3 3
>> sum
>> ===========
>> 6
>> avg avg
>> =========== ===========
>> 2 2
>> min min
>> =========== ===========
>> 1 1
>> count
>> ===========
>> 3
>> max
>> ===========
>> 3
>>
>> (4 rows affected)
>>
>> sqsh, linked to freetds, produces:
>>
>> col1 col2 col3
>> ----------- ----------- -----------
>> 1 1 1
>> 2 2 2
>> 3 3 3
>>
>> ===========
>> 6
>>
>> ===========
>> 2
>>
>> ===========
>> 1
>>
>> =========== =========== ===========
>> 3 2 3
>>
>> ===========
>> 1
>>
>> (4 rows affected)
>>
>> Note how sqsh doesn't label the aggregate columns, and it combined count, avg and max into one
> row,
>> while not combining the two min rows. Pretty useless.
>>
>> I no longer have access to sybase libs, so I cannot test sqsh linked to them.
>> Just for comparison, fisql has a different set of problems:
>>
>> col1 col2 col3
>> ----------- ----------- -----------
>> 1 1 1
>> 2 2 2
>> 3 3 3
>> sum avg min count avg max min
>> ----------- ----------- ----------- ----------- ----------- ----------- -----------
>> 6 2 1 3 2 3 1
>>
>> (4 rows affected)
>>
>> Micheal's theory was that an API call in CTlib wasn't quite right, such as ct_describe() or
>> ct_compute_info().
>> Any ideas anyone?
>> Thanks,
>> John
>
> Weird request. MS is trying to remove (if not already) support for COMPUTE.
> Probably the better way is to looks at the dumps (TDSDUMPs), and see
> what functions
> return. Then looks at documentation and callers (fisql, sqsh) and try
> to understand what's
> wrong.
>
> Frediano
> _______________________________________________
> FreeTDS mailing list
> FreeTDS at lists.ibiblio.org
> http://lists.ibiblio.org/mailman/listinfo/freetds
More information about the FreeTDS
mailing list