Derived table and adding another column problem

Hi
I have this query below, which I'm trying to add and group data on the w
number (ISO w, taken from Books online) fn_getISOW returns an integer
and works independently of this query. This query also works as I want when
there is no wno invloved (the 3 places)
The error I get is "Invalid column name 'wno'", I think what I'm doing is
a "copy" of what has been done in the count(case.....) as xx
Can anyone see what I'm doing wrong and how to fix it?
regards
Henry
declare @.ext char(4)
declare @.site int
declare @.calltype char(1)
SET DATEFORMAT mdy
set @.site = 1
set @.calltype = 'E'
set @.ext = '6810'
SELECT wno***, network, centre, center, team, total, besvaret, optaget,
opgivet, ubesvaret
FROM (
SELECT network, centre, center, team,
COUNT(*) AS total,
COUNT(CASE WHEN durationofconversation > 0 THEN 1
END) AS besvaret,
COUNT(CASE WHEN durationofconversation = 0 AND
releasecause = 'OC' THEN 1 END) AS optaget,
COUNT(CASE WHEN durationofconversation = 0 AND
releasecause = 'RL' AND durationofcall < 40 THEN 1 END) AS opgivet,
COUNT(CASE WHEN durationofconversation = 0 AND
durationofcall >= 40 THEN 1 END) AS ubesvaret,
tcv2.dbo.fn_getISOw(DATECALLINITIATED) as wno
***
FROM V2tickets
INNER JOIN
(SELECT s.serie, a.nodename as network, cs.nodename as
centre, c.nodename as center, t.nodename as team
FROM areas a, centers cs, center c, teams t, series s
WHERE (t.nodeid = 1 or t.nodeid=8 or t.nodeid=2 or
t.nodeid=72 or t.nodeid=73) and a.nodeid=cs.parentid and
cs.nodeid=c.parentid and c.nodeid=t.parentid and t.nodeid=s.parentid) AS H
ON V2tickets.digits = H.serie
WHERE siteid = @.site
AND calltype = @.calltype
AND LEN(digits) = 4
GROUP BY wno***, network, centre, center, team) AS D
ORDER BY 1Try this
SELECT wno***, network, centre, center, team, total, besvaret,
optaget,
opgivet, ubesvaret
FROM (
SELECT network, centre, center, team,
COUNT(*) AS total,
COUNT(CASE WHEN durationofconversation > 0
THEN 1
END) AS besvaret,
COUNT(CASE WHEN durationofconversation = 0 AND
releasecause = 'OC' THEN 1 END) AS optaget,
COUNT(CASE WHEN durationofconversation = 0 AND
releasecause = 'RL' AND durationofcall < 40 THEN 1 END) AS opgivet,
COUNT(CASE WHEN durationofconversation = 0 AND
durationofcall >= 40 THEN 1 END) AS ubesvaret,
tcv2.dbo.fn_getISOw(DATECALLINITIATED) as
wno
***
FROM V2tickets
INNER JOIN
(SELECT s.serie, a.nodename as network, cs.nodename as
centre, c.nodename as center, t.nodename as team
FROM areas a, centers cs, center c, teams t, series s
WHERE (t.nodeid = 1 or t.nodeid=8 or t.nodeid=2 or
t.nodeid=72 or t.nodeid=73) and a.nodeid=cs.parentid and
cs.nodeid=c.parentid and c.nodeid=t.parentid and t.nodeid=s.parentid)
AS H
ON V2tickets.digits = H.serie
WHERE siteid = @.site
AND calltype = @.calltype
AND LEN(digits) = 4
) AS D
GROUP BY wno***, network, centre, center, team ORDER BY 1
Madhivanan|||> Try this
> Madhivanan
>
Hi
No I'm sorry that doesn't do the trick either :(
regards
Henry|||> > Try this
> Hi
> No I'm sorry that doesn't do the trick either :(
> regards
> Henry
Solved... I had to use the same syntax in the group by part since wno is
not know at that time.
regards
Henrik