August 16, 2007 at 10:16 pm
All, I have a sql sentence using the user-defined function in group by clause .
E.g.
TABLE: tblHierarchy(lvl_id varchar(50))
INSERT tblHierarchy(lvl_id) SELECT '1.1.2'
dbo.GetDescription(lvl_id,6) means the area .
dbo.GetDescription(lvl_id,8) means the city
select dbo.GetDescription(lvl_id,6),dbo.GetDescription(lvl_id,8)
from tblHierarchy
group by dbo.GetDescription(lvl_id,6),dbo.GetDescription(lvl_id,8)
So, the result i want to get is:
AREA,CITY
But the actual result is:
AREA,AREA.
Why this happened?
C.G
August 16, 2007 at 11:27 pm
Check your function.
Probably there is a bug.
What you are getting if you are removing GROUP BY?
select dbo.GetDescription(lvl_id,6),dbo.GetDescription(lvl_id,8)
from tblHierarchy
_____________
Code for TallyGenerator
August 17, 2007 at 12:59 am
case 1:
select dbo.GetDescription(lvl_id,6),dbo.GetDescription(lvl_id,8)
from tblHierarchy
the result is ok:Area City.
case 2:
select dbo.GetDescription(lvl_id,6),dbo.GetDescription(lvl_id,8)
from tblHierarchy
group by dbo.GetDescription(lvl_id,6),dbo.GetDescription(lvl_id,8)
the result is wrong:Area Area
case 3:
select dbo.GetDescription(lvl_id,6),dbo.GetDescription(lvl_id,8)
from tblHierarchy
group by dbo.GetDescription(lvl_id,8),dbo.GetDescription(lvl_id,6)
the result is wrong:City City
P.S: The code of the function
CREATE FUNCTION dbo.GetDescription(@str varchar(50),@count int)
RETURNs VARCHAR(50) AS
BEGIN
DECLARE @result varchar(50)
set @result=''
WHILE (@count>0)
BEGIN
SET @result=@result+left(@str,charindex('.',@str))
SELECT @STR=substring(@str,charindex('.',@str)+1,len(@str))
IF @STR='' and @count<>1
BEGIN
RETURN ''
END
SET @count=@count-1
END
SELECT @result=lvl_desc FROM tblHierarchy WHERE lvl_id=@result
RETURN @result
END
C.G
August 17, 2007 at 3:58 am
Server: Msg 164, Level 15, State 1, Line 4
GROUP BY expressions must refer to column names that appear in the select list.
August 17, 2007 at 11:07 am
so, does case 1 solve your problem? and, if it does, do you want to discuss how the group by does not?
August 17, 2007 at 11:35 am
August 21, 2007 at 4:44 am
http://support.microsoft.com/kb/883415
Yes, This is my issue.
Are you on SP4 ?
Yes, on sp4.Sp4 has some problems???
C.G
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply