ASE Database forum - Problem about Having clause

Author

Problem about Having clause

murphy

2005-03-30, 9:44 am

my sql:
select b.dept,b.caseno,b.rcvcode,c.name
from jc_rwfg b,jc_khd c
where b.tache between 21 and 29 and
b.valid=1 and
b.rcvcode=c.id
group by b.dept,b.caseno
having b.tache=max(b.tache)

in table b,dept, caseno and tache would distinct each record, and c.id is
the primary key in table c.

my problem is:the above sql doesn't return what I want. The condition in
where clause 'b.rcvcode=c.id' seems taking no effect. It simply cross join
the table b and c.
And I found if I move the condition to the having clause, it works! I want
to know why.

Stefan Karlsson

2005-03-30, 7:05 pm

"murphy" <murphy_ysq@sina.com> wrote in message
news:4249f843@forums
-1-dub...
> my sql:
> select b.dept,b.caseno,b.rcvcode,c.name
> from jc_rwfg b,jc_khd c
> where b.tache between 21 and 29 and
> b.valid=1 and
> b.rcvcode=c.id
> group by b.dept,b.caseno
> having b.tache=max(b.tache)
>
> in table b,dept, caseno and tache would distinct each record, and c.id is
> the primary key in table c.
>
> my problem is:the above sql doesn't return what I want. The condition in
> where clause 'b.rcvcode=c.id' seems taking no effect. It simply cross join
> the table b and c.
> And I found if I move the condition to the having clause, it works! I want
> to know why.
>

Grouping requires normally that all columns in the select list are either
part of the GROUP BY-clause or are aggregates. the columns rcvcode and name
in your examples breaks this rule.

Allowing this is a T-SQL extension, and what you have discovered is one side
effect of this, i.e. that after the grouping, the groups are joined to the
base table and since the HAVING-clause is applied after this it removes
extraneous rows.

Believe this is doc'd in the ref man and the t-sql user's gde.

HTH,

/Stefan

murphy

2005-04-01, 7:02 am

Thanks for your help!
But if I've got such a demand that I must use having clause to filter
records, and get some other columns info in select list. The point is some
columns in the select list would not be added in group by clause, or the
group by clause would be useless.
It seems that I have to use subquery for this demand instead of using having
clause :(
"Stefan Karlsson" <Stefan.Karlsson@Sybase.justsaynotospam.com> Ð´ÈëÓÊ¼þ
news:424b138f$1@foru
ms-2-dub...
> Grouping requires normally that all columns in the select list are either
> part of the GROUP BY-clause or are aggregates. the columns rcvcode and
name
> in your examples breaks this rule.
>
> Allowing this is a T-SQL extension, and what you have discovered is one
side
> effect of this, i.e. that after the grouping, the groups are joined to the
> base table and since the HAVING-clause is applied after this it removes
> extraneous rows.
>
> Believe this is doc'd in the ref man and the t-sql user's gde.
>
> HTH,
>
> /Stefan
>
>

Bret Halford

2005-04-01, 8:04 pm

You don't seem to be using any aggregate functions in they query,
so why insist on using GROUP BY? It seems you are just using
it to order the output so certain values are together - ORDER BY
will do that for you quite well.

murphy wrote:
[color=darkred]
> Thanks for your help!
> But if I've got such a demand that I must use having clause to filter
> records, and get some other columns info in select list. The point is some
> columns in the select list would not be added in group by clause, or the
> group by clause would be useless.
> It seems that I have to use subquery for this demand instead of using having
> clause :(
> "Stefan Karlsson" <Stefan.Karlsson@Sybase.justsaynotospam.com> Ð´ÈëÓÊ¼þ
> news:424b138f$1@foru
ms-2-dub...
> name
> side

murphy

2005-04-02, 7:02 am

For example,I want to select the records which are marked by '*'

jc_rwfg:
dept caseno tache ...
1 1 1
1 1 2
*1 1 3
1 2 1
*1 2 4

Now I will use this sql:
select b.dept,b.caseno,b.rcvcode,c.name
from jc_rwfg b,jc_khd c
where b.tache between 21 and 29 and
b.valid=1 and
b.rcvcode=c.id and
b.tache=(select max(d.tache) from jc_rwfg d where b.dept=d.dept and
b.caseno=d.caseno )

I'm not just want to order the output, but also want to use the resultset in
the following process,
so it had better contain all valid records.
I just found if I can use the T-sql extension 'having col=max(col)', it
seems more convenient.
Any way, thanks for all of you, at least I could use one way to get what I
want.
"Bret Halford" <bret@sybase.com> ???? news:424D5046.F11E954A@sybase.com...
> You don't seem to be using any aggregate functions in they query,
> so why insist on using GROUP BY? It seems you are just using
> it to order the output so certain values are together - ORDER BY
> will do that for you quite well.

Sponsored Links