GetEnvironmentVariable

Hello,
I could not use this in the criteria filed of my query.
= System.Environment.GetEnvironmentVariable(MYID)
It gives invalid object name
System.Environment.GetEnvironmentVariable
Couldn't I use it in where clause?
Thanks,
Jim.You'll need to use it in a query expression as follows:
="select * From sysservers where srvname = '" &
System.Environment.GetEnvironmentVariable("COMPUTERNAME") & "'"
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"JIM.H." <anonymous@.discussions.microsoft.com> wrote in message
news:2cb3a01c46905$6b8d1ea0$a301280a@.phx.gbl...
> Hello,
> I could not use this in the criteria filed of my query.
> = System.Environment.GetEnvironmentVariable(MYID)
> It gives invalid object name
> System.Environment.GetEnvironmentVariable
> Couldn't I use it in where clause?
> Thanks,
> Jim.
>|||Thanks Ravi,
I am developening the query in the Data tab of report
design. Can you tell me exactly where I type this kind of
expression? I am quite new in this.
Thanks,
Jim.
>--Original Message--
>You'll need to use it in a query expression as follows:
>="select * From sysservers where srvname = '" &
>System.Environment.GetEnvironmentVariable("COMPUTERNAME")
& "'"
>--
>Ravi Mumulla (Microsoft)
>SQL Server Reporting Services
>This posting is provided "AS IS" with no warranties, and
confers no rights.
>"JIM.H." <anonymous@.discussions.microsoft.com> wrote in
message
>news:2cb3a01c46905$6b8d1ea0$a301280a@.phx.gbl...
>> Hello,
>> I could not use this in the criteria filed of my query.
>> = System.Environment.GetEnvironmentVariable(MYID)
>> It gives invalid object name
>> System.Environment.GetEnvironmentVariable
>> Couldn't I use it in where clause?
>> Thanks,
>> Jim.
>
>.
>|||Sorry I do not get picture in here, if you are talking
abouy typing it in SQL pan in Data tab, I am doing that
but it does not seem it is working.
>--Original Message--
>You'll need to type this expression in the Data pane in
Report Designer UI.
>Please see the attached image.
>--
>Ravi Mumulla (Microsoft)
>SQL Server Reporting Services
>This posting is provided "AS IS" with no warranties, and
confers no rights.
>"JIM.H." <anonymous@.discussions.microsoft.com> wrote in
message
>news:2cd0601c4691f$3cccbd90$a301280a@.phx.gbl...
>> Thanks Ravi,
>> I am developening the query in the Data tab of report
>> design. Can you tell me exactly where I type this kind
of
>> expression? I am quite new in this.
>> Thanks,
>> Jim.
>> >--Original Message--
>> >You'll need to use it in a query expression as follows:
>> >
>> >="select * From sysservers where srvname = '" &
>> >System.Environment.GetEnvironmentVariable
("COMPUTERNAME")
>> & "'"
>> >
>> >--
>> >Ravi Mumulla (Microsoft)
>> >SQL Server Reporting Services
>> >
>> >This posting is provided "AS IS" with no warranties,
and
>> confers no rights.
>> >"JIM.H." <anonymous@.discussions.microsoft.com> wrote in
>> message
>> >news:2cb3a01c46905$6b8d1ea0$a301280a@.phx.gbl...
>> >> Hello,
>> >> I could not use this in the criteria filed of my
query.
>> >> = System.Environment.GetEnvironmentVariable(MYID)
>> >> It gives invalid object name
>> >> System.Environment.GetEnvironmentVariable
>> >> Couldn't I use it in where clause?
>> >> Thanks,
>> >> Jim.
>> >>
>> >
>> >
>> >.
>> >
>
>|||You will not get the fields in the fields window automatically. So you'd
first have to type in static SQL and run it (which will generate the fields
list in the fields window) and then replace the static SQL with the query
expression.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"JIM.H." <anonymous@.discussions.microsoft.com> wrote in message
news:2c4e501c4692f$36241410$a401280a@.phx.gbl...
> Sorry I do not get picture in here, if you are talking
> abouy typing it in SQL pan in Data tab, I am doing that
> but it does not seem it is working.
> >--Original Message--
> >You'll need to type this expression in the Data pane in
> Report Designer UI.
> >Please see the attached image.
> >
> >--
> >Ravi Mumulla (Microsoft)
> >SQL Server Reporting Services
> >
> >This posting is provided "AS IS" with no warranties, and
> confers no rights.
> >"JIM.H." <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:2cd0601c4691f$3cccbd90$a301280a@.phx.gbl...
> >> Thanks Ravi,
> >> I am developening the query in the Data tab of report
> >> design. Can you tell me exactly where I type this kind
> of
> >> expression? I am quite new in this.
> >> Thanks,
> >> Jim.
> >>
> >> >--Original Message--
> >> >You'll need to use it in a query expression as follows:
> >> >
> >> >="select * From sysservers where srvname = '" &
> >> >System.Environment.GetEnvironmentVariable
> ("COMPUTERNAME")
> >> & "'"
> >> >
> >> >--
> >> >Ravi Mumulla (Microsoft)
> >> >SQL Server Reporting Services
> >> >
> >> >This posting is provided "AS IS" with no warranties,
> and
> >> confers no rights.
> >> >"JIM.H." <anonymous@.discussions.microsoft.com> wrote in
> >> message
> >> >news:2cb3a01c46905$6b8d1ea0$a301280a@.phx.gbl...
> >> >> Hello,
> >> >> I could not use this in the criteria filed of my
> query.
> >> >> = System.Environment.GetEnvironmentVariable(MYID)
> >> >> It gives invalid object name
> >> >> System.Environment.GetEnvironmentVariable
> >> >> Couldn't I use it in where clause?
> >> >> Thanks,
> >> >> Jim.
> >> >>
> >> >
> >> >
> >> >.
> >> >
> >
> >
> >sql

Get top 3 records for each ...

to relate my question to the pubs DB...

I want to return up to 3 titles for each publisher. The criteria

So if a publisher only has
1 title = return 1
2 titles = return 2
3 titles = return 3
4 titles = return only 3
>4 titles = return only 3

To make it more interesting, lets return the first 3 alphabetically as well...

ThanksTry this:

select * from titles a
where title_id in
(select top 3 title_id
from titles b
where a.pub_id = b.pub_id)
order by pub_id|||/*
JUST A LITTLE ADJUSTED
*/

select
case when t.title_id=(
select min( t3.title_id)
from titles t3
where t3.pub_id = t.pub_id and t3.title=(select min(t4.title) from titles t4 where t4.pub_id = t3.pub_id)
)
then p.pub_name else '' end
,t.title
from titles t
join publishers p on t.pub_id=p.pub_id
where title_id in
(
select top 3 t2.title_id
from titles t2
where t2.pub_id = t.pub_id
order by t2.title
)
order by p.pub_name,t.title