Help me with this query

Hi,

Below is the query

Alter Proc Spr_FadDataPull
As
SET NOCOUNT ON
Begin
select distinct tbs.sessionid,
(Case
when PageDataTitle='Find A Doctor-Results' then '1'
else '0'
end) as Flag, tbPD.PageDataPath ,
convert(varchar(10),SessionCD,101) as Effective_Date,
(select tbS.SessionPageViewCount from (select count(S.SessionPageViewCount) from tbSession S
inner join tbPageAction as PA
on S.sessionid=PA.sessionid
inner join tbPageData as PD
on PD.PageDataid=PA.PageDataid
where PD.PageDateTitle='Find A Doctor-Results'
Group by S.SessionPageViewCount )) as PageViewCount
from tbSession as tbS
inner join tbPageAction as tbPA
on tbS.sessionid=tbPA.sessionid
inner join tbPageData as tbPD
on tbPD.PageDataid=tbPA.PageDataid
where convert(varchar(10),SessionCD,101) between '05/15/2007' and '06/18/2007'
where tbS.SessionCD between '05/15/2007' and '06/18/2007'
End

SET NOCOUNT OFF

the error is

Line 17: Incorrect syntax near ')'. near the Group by clause

can anyone pls help me to fix this error.

Thanks in Advance,

sg

you need an alias on your derived table within the PageViewCount item

Code Snippet

Begin

selectdistinct tbs.sessionid,

(Case

when PageDataTitle='Find A Doctor-Results'then'1'

else'0'

end)as Flag, tbPD.PageDataPath ,

convert(varchar(10),SessionCD,101)as Effective_Date,

(select tbS.SessionPageViewCount from(selectcount(S.SessionPageViewCount)from tbSession S

innerjoin tbPageAction as PA

on S.sessionid=PA.sessionid

innerjoin tbPageData as PD

on PD.PageDataid=PA.PageDataid

where PD.PageDateTitle='Find A Doctor-Results'

Groupby S.SessionPageViewCount )as x

)as PageViewCount

from tbSession as tbS

innerjoin tbPageAction as tbPA

on tbS.sessionid=tbPA.sessionid

innerjoin tbPageData as tbPD

on tbPD.PageDataid=tbPA.PageDataid

where convert(varchar(10),SessionCD,101) between '05/15/2007' and '06/18/2007'

where tbS.SessionCD between'05/15/2007'and'06/18/2007'

End

|||

Hi Dane,

thanks a lot,

i named the derived column as you Said its givinf this error

error:

No column was specified for column 1 of 'x'.

Please help.

Regards,

Vani.

|||

Your count(...) needs a valid name to be referenced.

Code Snippet

selectdistinct tbs.sessionid,

(Case

when PageDataTitle='Find A Doctor-Results'then'1'

else'0'

end)as Flag, tbPD.PageDataPath ,

convert(varchar(10),SessionCD,101)as Effective_Date,

(select tbS.SessionPageViewCount from(selectcount(S.SessionPageViewCount)as SessionPageViewCount

from tbSession S

innerjoin tbPageAction as PA

on S.sessionid=PA.sessionid

innerjoin tbPageData as PD

on PD.PageDataid=PA.PageDataid

where PD.PageDateTitle='Find A Doctor-Results'

Groupby S.SessionPageViewCount )as x

)as PageViewCount

from tbSession as tbS

innerjoin tbPageAction as tbPA

on tbS.sessionid=tbPA.sessionid

innerjoin tbPageData as tbPD

on tbPD.PageDataid=tbPA.PageDataid

where convert(varchar(10),SessionCD,101) between '05/15/2007' and '06/18/2007'

where tbS.SessionCD between'05/15/2007'and'06/18/2007'