Dynamic Query question

I am using the BOL walkthrough and have come to the Dynamic Query section.
I type in the following into the generic query builder...
="SELECT FirstName, LastName, Title FROM Employee" &
IIf(Parameters!Department.Value = 0,""," WHERE (DepartmentID = " &
Parameters!Department.Value & ")") & " ORDER BY LastName"
But it does not work... Can anyone please assist?If you mean that the fields list is not populated, yes, for query
expressions you'll need to populate the fields list manually. Another
approach is to first type in the static version of the query (select
FirstName, LastName, Title from Employee), run the query, switch to layout
pane, switch back to data pane, and then replace the static version with the
expression-based version you have below. You will then see the fields list.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Bing" <.> wrote in message news:O$HyaHpfEHA.3676@.TK2MSFTNGP12.phx.gbl...
> I am using the BOL walkthrough and have come to the Dynamic Query section.
> I type in the following into the generic query builder...
>
> ="SELECT FirstName, LastName, Title FROM Employee" &
> IIf(Parameters!Department.Value = 0,""," WHERE (DepartmentID = " &
> Parameters!Department.Value & ")") & " ORDER BY LastName"
> But it does not work... Can anyone please assist?
>|||if DepartmentID is numeric try:
="SELECT FirstName, LastName, Title FROM Employee" &
IIf(Parameters!Department.Value = 0,""," WHERE
(DepartmentID = " &
int(Parameters!Department.Value) & ")") & " ORDER BY
LastName"
if DepartmentID is string try:
="SELECT FirstName, LastName, Title FROM Employee" &
IIf(Parameters!Department.Value = 0,""," WHERE
(DepartmentID = '" &
Parameters!Department.Value & "')") & " ORDER BY LastName"
>--Original Message--
>I am using the BOL walkthrough and have come to the
Dynamic Query section.
>I type in the following into the generic query builder...
>
>="SELECT FirstName, LastName, Title FROM Employee" &
>IIf(Parameters!Department.Value = 0,""," WHERE
(DepartmentID = " &
>Parameters!Department.Value & ")") & " ORDER BY LastName"
>But it does not work... Can anyone please assist?
>
>.
>

Dynamic filter using a UDF

In BOL it says that you can use a UDF in a dynamic filter, passing in one of
the system UDFs. However, when I try this I get a syntax error, e.g.
SELECT * FROM [dbo].[SiteData]
WHERE SiteId IN (SELECT Value FROM dbo.Split(HOST_NAME(), ','))
where dbo.Split is a UDF that splits a list of values into a table
It doesn't seem to like the brackets after HOST_NAME, but the example shown
in BOL is MYUDF(HOSTNAME())
Any ideas what I've got wrong with the syntax?
TIA
Paul
Paul,
the only way I've been able to resolve a system function to multiple values
is to use a linking table and replicate it as well. Have a look at the
example in the merge section of www.replicationanswers.com where there is a
related example.
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Paul
Thanks for that, I can make it work in my scenario - BTW you also quote the
MYUDF(HOST_NAME()) solution as well further down the page.
Regards
Paul
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:%236k7vXQxEHA.3612@.tk2msftngp13.phx.gbl...
> Paul,
> the only way I've been able to resolve a system function to multiple
> values
> is to use a linking table and replicate it as well. Have a look at the
> example in the merge section of www.replicationanswers.com where there is
> a
> related example.
> HTH,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Paul,
I'll edit the page to make clearer, but I've only used
the MYUDF(HOST_NAME()) solution for scalars.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||I'll need to check this out in SQL 2005 beta to see if it's fixed or if
anyone can give a reason why ...
SELECT * FROM [dbo].[SiteData] WHERE SiteId IN (SELECT Value FROM
dbo.Split('1, 2', ','))
compile (and runs), but
SELECT * FROM [dbo].[SiteData] WHERE SiteId IN (SELECT Value FROM
dbo.Split(HOST_NAME(), ','))
does not, whereas
SELECT * FROM [dbo].[SiteData] WHERE SiteId = MyUdf(HOST_NAME())
compiles again.
It's nothing to do with replication per se, but how the complier is parsing
the statements.
Thanks
Paul
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:4d0d01c4c575$382daee0$a301280a@.phx.gbl...
> Paul,
> I'll edit the page to make clearer, but I've only used
> the MYUDF(HOST_NAME()) solution for scalars.
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||why don't you put the hostname within your function as opposed to supplying
it as an argument.
"Paul Hatcher" <phatcher@.nospam.cix.co.uk> wrote in message
news:OHJ9%238YxEHA.2624@.TK2MSFTNGP11.phx.gbl...
> I'll need to check this out in SQL 2005 beta to see if it's fixed or if
> anyone can give a reason why ...
> SELECT * FROM [dbo].[SiteData] WHERE SiteId IN (SELECT Value FROM
> dbo.Split('1, 2', ','))
> compile (and runs), but
> SELECT * FROM [dbo].[SiteData] WHERE SiteId IN (SELECT Value FROM
> dbo.Split(HOST_NAME(), ','))
> does not, whereas
> SELECT * FROM [dbo].[SiteData] WHERE SiteId = MyUdf(HOST_NAME())
> compiles again.
> It's nothing to do with replication per se, but how the complier is
> parsing the statements.
> Thanks
> Paul
> "Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
> news:4d0d01c4c575$382daee0$a301280a@.phx.gbl...
>
|||Because it says in BOL that you can't use dynamic snapshots if neither of
HOST_NAME/SUSER_NAME() functions are not specified in the row filter. The
scenario is a largish central database with a bunch of subsidiary offices,
some of which are on dial-up, so I'm trying everything to minimise the
amount of time taken to do replication and the amount of data on the wire.
I'll try it anyway and see what happens; the other point is that I've come
across Transact-SQL oddities like this before, and it's a good idea to get
rid of them if at all possible.
Thanks
Paul
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:OX8lbLaxEHA.1400@.TK2MSFTNGP11.phx.gbl...
> why don't you put the hostname within your function as opposed to
> supplying it as an argument.
> "Paul Hatcher" <phatcher@.nospam.cix.co.uk> wrote in message
> news:OHJ9%238YxEHA.2624@.TK2MSFTNGP11.phx.gbl...
>