[thelist] SQL - Group by peculiarity in Sybase

VOLKAN ÖZÇELİK volkan.ozcelik at gmail.com
Fri Apr 15 01:44:52 CDT 2005


I will post this issue to Sybase forums as well. I will let you know
the results. Googling on the subject did not help much.

Here are the results I get after reading the f*cking manual. 

manual>
: While you can use group by without aggregates, such a construction
has limited
: functionality and may produce confusing results.

me>
bingo!

manual>
: The SQL standards for group by are more restrictive than Sybase's standard. 
: The SQL standard requires that:
: · The columns in a select list must be in the group by expression or
they must be
: arguments of aggregate functions.
: · A group by expression can only contain column names in the select
list, but not
:those used only as arguments for vector aggregates.

:Several Transact-SQL extensions (described in the following sections)
relax these
:restrictions

me>
So Sybase adheres the standards unless I relax the restriction by rtfm further.
I executed a non-standard SQL and it resulted in "confusing results"
as the manual states. By the way converting " to ' did not change
anything.

When I read further I see that 

: set fipsflagger on

will alleviate the restriction.

I do it andddd....

voila!

nothing changes :(

What I conclude is that it is a sybase-specific problem, however the
usage is against standards. So imho, sybase is doing good pushing me
into standards.


Best wishes!
Volkan.


On 4/15/05, Ken Schaefer <Ken at adopenstatic.com> wrote:
> Hi there,
> 
> : When I alter the query to:
> :
> : SELECT taktar,taktut, sum(taktut) as toplam FROM p_taksit
> : WHERE (necessary filters)
> :
> : According to my logic I should get.
> :
> : Apr 14 2005 12:00 AM  -  95.38        -  95.38
> 
> Actually the last column should contain the sum of all the records in the
> table (after a filter has been applied) unless you do a GROUP BY, would be my
> understanding of how this would work.
> 
> Now, as to the filter problem - if you use ' instead of " around your dates,
> does the problem go away? The only thing I can think of is that the database
> is not having the same interpretation of your dates that you think it does.
> But that doesn't explain why the query works in your first example, and not
> your second.
> 
> I'm a little stumped - maybe asking in a specialist SyBase forum?
> 
> Cheers
> Ken
> 
> --
> www.adOpenStatic.com/cs/blogs/ken/
> 
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> : From: thelist-bounces at lists.evolt.org [mailto:thelist-
> : bounces at lists.evolt.org] On Behalf Of VOLKAN ÖZÇELIK
> : Subject: Re: [thelist] SQL - Group by peculiarity in Sybase
> :
> : > What are you doing in "(necessary filters)"? The only thing that comes
> : > to mind is that if you are doing some kind of result limiting (like
> : > MySQL's LIMIT clause), then the database might be incorrectly using
> : > the table's stored sum in the system catelog. But, if you're offering
> : > it a date range in the WHERE clause, etc., it shouldn't be using that
> : > stored value.
> : >
> :
> : nothing special in the where clause no set rowcounts, no limits,
> : (btw sybase does not support limit at all, it has a set rowcount
> : directive to deal with it.)
> :
> : Here is the full query:
> :
> : SELECT taktar,taktut, sum(taktut) as toplam FROM p_taksit
> : WHERE taktar between "2005-04-14" and "2005-04-16"
> : AND taktut > 0
> :
> : nothing really special about the where clause as well.
> : I hadn't given the entire SQL to keep things simple.
> :
> : > Still, aside from serious deviation in Sybase from SQl standard, I
> : > don't see us being able to help without seeing the WHERE clause.
> :
> : The sql is dead simple. That's what makes me conclude that
> :  there exists such a deviation. However sybase is a well known and
> : commercial database, so imho it will not deviate from standards this
> : much.
> :
> : Have anyone coincided a similar situation? Or am I alone?
> : Do you think that while the DB tries to "optimize" things, it screws
> : them up even more.
> :
> : Does it require all aggragete columns be listed in the group by clause
> : (when I use group by, I receive a single row again)
> :
> : What can the reason be?
> : any ideas?
> :
> : I will rtfm carefully (the product is shipped with tons of explanatory
> : pdf's) I'll inform you if I find anything. In the mean time, your
> : assistance will be HIGHLY appreciated.
> : I do not want to lose my brain in this SQL dilemma :)
> :
> : Cheers
> : Volkan
> 
> --
> 
> * * Please support the community that supports you.  * *
> http://evolt.org/help_support_evolt/
> 
> For unsubscribe and other options, including the Tip Harvester
> and archives of thelist go to: http://lists.evolt.org
> Workers of the Web, evolt !
>


More information about the thelist mailing list