Embeded case in where clause - causing problems.

Hi.
I have a sp that allows the user to search based on a variable set of parame
ters for example just a home phone or just a buss. phone however when search
ing by last name the user also has to supply the ZIP
optionally he can filter that search by first name or address. I am trying t
o accomplish that by using embede cases - the procedure compiles - but I do
not get the results I want
For example if i pass in '%s%' for @.Lastname and '%1%' for zip - it does not
work - I am sure that are fields with those values in the db
(note: everying worked as expected before I added the embeded cases (and wil
dcard stuff))
the following is the SP - (sorry for the long snippet)
ALTER procedure GetDupCheckResults
(
@.HomePhone varchar(50),
@.BussPhone varchar(50),
@.Email varchar(50),
@.LastName varchar(50),
@.FirstName varchar(50),
@.Address varchar(50),
@.FirmZip varchar(50),
@.PersonZip varchar(50),
@.FirmName varchar(50)
)
as
begin
SELECT nmfid, nmffirst, nmflast, nafcompany, nafadd1, nafadd2, naftype +
' (' + nafdesc + ') ' AS [Adress Type], npfarea + '-' + npfphone AS Phone
FROM
(
select v_nmf_naf_npf_linked.* from
v_nmf_naf_npf_linked inner join
(
Select distinct npfseq from
(
select
npfseq,'HomePhone' FoundSource
from
v_nmf_naf_npf_Linked
where
npfArea + '-' + NPFPhone = @.HomePhone
and
naftype = 'HOME'
and
(
@.HomePhone <> ''
and
@.HomePhone <> '%%'
)
union
select
npfseq,'BussPhone'
from
v_nmf_naf_npf_Linked
where
npfArea+'-'+NPFPhone = @.BussPhone
and
naftype = 'BF'
and
@.BussPhone <> ''
union
select
npfseq,'NameAddress'
from
V_nmf_naf_npf_Linked
where
@.PersonZIP like nafzip
and
@.LastName like nmflast
and
-- optionally allow filter on address and last name
1 =
Case @.Address
When '' then 1
When '%%' then 1 -- when wild cards are in use
Else
(
Case
when @.Address like nafAdd1 then 1
when @.Address like nafAdd2 then 1
else
0
end
)
end
and
1 =
Case @.LastName
When '' then 1
When '%%' then 1
Else
(
Case
when @.Lastname like nmfLast then 1
else 0
end
)
end
and not
(
(@.LastName = '' or @.PersonZip = '')
or
(@.LastName = '%%' or @.PersonZip = '%%')
)
union
select
npfseq,'Company'
from
V_nmf_naf_npf_Linked
where
nafZip like @.FirmZip
and
nafCompany like @.FirmName
and not @.FirmName = ''
and not @.FirmZip = ''
and not @.FirmName = '%%'
and not @.FirmZip = '%%'
)Temptab
)
unionResult on unionResult.npfseq = v_nmf_naf_npf_Linked.npfseq
) dd
end
thank you for slogging thru it.You are sure you have people with the last name '%s%' and zip '%1%'?
What country is this?
Most likely you don't have such data in your database, but that is exactly
what this code will try to find, since your condition (on last name, for
example) is
@.Lastname like nmfLast
While this stored procedure looks far more complicated than it needs to
be, my guess is that you want
nmfLast LIKE @.Lastname
instead of the other way around.
Steve Kass
Drew University
Madler wrote:

>Hi.
>I have a sp that allows the user to search based on a variable set of param
eters for example just a home phone or just a buss. phone however when searc
hing by last name the user also has to supply the ZIP
>optionally he can filter that search by first name or address. I am trying
to accomplish that by using embede cases - the procedure compiles - but I do
not get the results I want
>For example if i pass in '%s%' for @.Lastname and '%1%' for zip - it does no
t work - I am sure that are fields with those values in the db
>(note: everying worked as expected before I added the embeded cases (and wi
ldcard stuff))
>the following is the SP - (sorry for the long snippet)
>ALTER procedure GetDupCheckResults
>(
> @.HomePhone varchar(50),
> @.BussPhone varchar(50),
> @.Email varchar(50),
> @.LastName varchar(50),
> @.FirstName varchar(50),
> @.Address varchar(50),
> @.FirmZip varchar(50),
> @.PersonZip varchar(50),
> @.FirmName varchar(50)
> )
>as
>begin
>SELECT nmfid, nmffirst, nmflast, nafcompany, nafadd1, nafadd2, naftype
+ ' (' + nafdesc + ') ' AS [Adress Type], npfarea + '-' + npfphone AS Phone
>FROM
>(
>select v_nmf_naf_npf_linked.* from
> v_nmf_naf_npf_linked inner join
> (
> Select distinct npfseq from
> (
> select
> npfseq,'HomePhone' FoundSource
> from
> v_nmf_naf_npf_Linked
> where
> npfArea + '-' + NPFPhone = @.HomePhone
> and
> naftype = 'HOME'
> and
> (
> @.HomePhone <> ''
> and
> @.HomePhone <> '%%'
> )
> union
> select
> npfseq,'BussPhone'
> from
> v_nmf_naf_npf_Linked
> where
> npfArea+'-'+NPFPhone = @.BussPhone
> and
> naftype = 'BF'
> and
> @.BussPhone <> ''
> union
> select
> npfseq,'NameAddress'
> from
> V_nmf_naf_npf_Linked
> where
> @.PersonZIP like nafzip
> and
> @.LastName like nmflast
> and
> -- optionally allow filter on address and last name
> 1 =
> Case @.Address
> When '' then 1
> When '%%' then 1 -- when wild cards are in use
> Else
> (
> Case
> when @.Address like nafAdd1 then 1
> when @.Address like nafAdd2 then 1
> else
> 0
> end
> )
> end
> and
> 1 =
> Case @.LastName
> When '' then 1
> When '%%' then 1
> Else
> (
> Case
> when @.Lastname like nmfLast then 1
> else 0
> end
> )
> end
> and not
> (
> (@.LastName = '' or @.PersonZip = '')
> or
> (@.LastName = '%%' or @.PersonZip = '%%')
> )
>
> union
> select
> npfseq,'Company'
> from
> V_nmf_naf_npf_Linked
> where
> nafZip like @.FirmZip
> and
> nafCompany like @.FirmName
> and not @.FirmName = ''
> and not @.FirmZip = ''
> and not @.FirmName = '%%'
> and not @.FirmZip = '%%'
>
> )Temptab
> )
> unionResult on unionResult.npfseq = v_nmf_naf_npf_Linked.npfseq
> ) dd
>end
>
>thank you for slogging thru it.
>
>

Embedded Reports

hi,
do i have access to subreports '
for example: how many rows were created?
a have a tablerow with a sub report.
i want to set the visibility to hidden, if no rows were created in the
subreport...
regards
PatrickThe parent report doesn't have access to properties of the subreport.
You may want to consider setting the NoRows property on the subreport
control in the master report.
Or make the subreport control in the master report very small and then have
the subreport hide its own contents if there are no rows of data.
As a last resort, you could put a query in the master report that returns a
count of the rows of the subreport (or if you're calling the subreport
inside of a list, you would include the count as a column in your master
query) and then base the visibility on that count.
--
My employer's lawyers require me to say:
"This posting is provided 'AS IS' with no warranties, and confers no
rights."
"Patrick Ruhnow" <pruhnow@.dornbracht.de> wrote in message
news:uOSD0kAaEHA.556@.tk2msftngp13.phx.gbl...
> hi,
> do i have access to subreports '
> for example: how many rows were created?
> a have a tablerow with a sub report.
> i want to set the visibility to hidden, if no rows were created in the
> subreport...
>
> regards
> Patrick
>

Emailing a report from a URL

Hi there,
Is it possible to email a report through the URL access method? For example,
can the URL
http://server/reportserver?reportname&rs:Format=PDF&rs:Command=Render include
a parameter such as rs:EmailTo=someone@.something.com?
Or is trying to achieve this goal a lot more involved than simply including
a parameter in a URL - such as developing a custom rendering or delivery
extension?
Many thanks
Adam AingerNo it is not possible to do this via the url. I guess in theory you could
write a rendering extension that could do this but it would be very
involved.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"Adam Ainger" <AdamAinger@.discussions.microsoft.com> wrote in message
news:7EB4F8A9-CA3F-4FC9-A028-7458C3B51CFB@.microsoft.com...
> Hi there,
> Is it possible to email a report through the URL access method? For
> example,
> can the URL
> http://server/reportserver?reportname&rs:Format=PDF&rs:Command=Render
> include
> a parameter such as rs:EmailTo=someone@.something.com?
> Or is trying to achieve this goal a lot more involved than simply
> including
> a parameter in a URL - such as developing a custom rendering or delivery
> extension?
> Many thanks
> Adam Ainger|||Okay, thanks for your feedback Daniel.
"Daniel Reib (MSFT)" wrote:
> No it is not possible to do this via the url. I guess in theory you could
> write a rendering extension that could do this but it would be very
> involved.
> --
> -Daniel
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Adam Ainger" <AdamAinger@.discussions.microsoft.com> wrote in message
> news:7EB4F8A9-CA3F-4FC9-A028-7458C3B51CFB@.microsoft.com...
> > Hi there,
> >
> > Is it possible to email a report through the URL access method? For
> > example,
> > can the URL
> > http://server/reportserver?reportname&rs:Format=PDF&rs:Command=Render
> > include
> > a parameter such as rs:EmailTo=someone@.something.com?
> >
> > Or is trying to achieve this goal a lot more involved than simply
> > including
> > a parameter in a URL - such as developing a custom rendering or delivery
> > extension?
> >
> > Many thanks
> > Adam Ainger
>
>

Email on demand

Looking for an example of sending a 2005 RS report via a web page on demand.
I would need to fire it when a user clicked a button on the web form.
In the old days, in plain old ASP, we used to build an HTML formatted email
body and then fire it off using CDO. We could still do that with ASPX but
I'd much prefer to build a report in RS (more formatting and doc type options)
and then fire it off.
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200710/1On Oct 3, 1:07 pm, "wnichols via SQLMonster.com" <u3357@.uwe> wrote:
> Looking for an example of sending a 2005 RS report via a web page on demand.
> I would need to fire it when a user clicked a button on the web form.
> In the old days, in plain old ASP, we used to build an HTML formatted email
> body and then fire it off using CDO. We could still do that with ASPX but
> I'd much prefer to build a report in RS (more formatting and doc type options)
> and then fire it off.
> --
> Message posted via SQLMonster.comhttp://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200710/1
This link should help.
http://msdn2.microsoft.com/en-us/library/microsoft.wssux.reportingserviceswebservice.rsexecutionservice2005.reportexecutionservice.render.aspx
You will want to create a Proxy (Web Reference) to
ReportingService2005.asmx. Then attach the newly created file to an
email created via ASP.NET. Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||Enrique,
Thanks for the info.
EMartinez wrote:
>> Looking for an example of sending a 2005 RS report via a web page on demand.
>> I would need to fire it when a user clicked a button on the web form.
>[quoted text clipped - 6 lines]
>> --
>> Message posted via SQLMonster.comhttp://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200710/1
>This link should help.
>http://msdn2.microsoft.com/en-us/library/microsoft.wssux.reportingserviceswebservice.rsexecutionservice2005.reportexecutionservice.render.aspx
>You will want to create a Proxy (Web Reference) to
>ReportingService2005.asmx. Then attach the newly created file to an
>email created via ASP.NET. Hope this helps.
>Regards,
>Enrique Martinez
>Sr. Software Consultant
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200710/1|||On Oct 4, 11:04 am, "wnichols via SQLMonster.com" <u3357@.uwe> wrote:
> Enrique,
> Thanks for the info.
>
> EMartinez wrote:
> >> Looking for an example of sending a 2005 RS report via a web page on demand.
> >> I would need to fire it when a user clicked a button on the web form.
> >[quoted text clipped - 6 lines]
> >> --
> >> Message posted via SQLMonster.comhttp://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200710/1
> >This link should help.
> >http://msdn2.microsoft.com/en-us/library/microsoft.wssux.reportingser...
> >You will want to create a Proxy (Web Reference) to
> >ReportingService2005.asmx. Then attach the newly created file to an
> >email created via ASP.NET. Hope this helps.
> >Regards,
> >Enrique Martinez
> >Sr. Software Consultant
> --
> Message posted via SQLMonster.comhttp://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200710/1
You're welcome. Let me know if I can be of further assistance.
Regards,
Enrique Martinez
Sr. Software Consultant

Eliminate duplicate data

Hi,
I want to eliminate duplicate entries from different tables into a single
new table and keep the reference from the original data.
Example:
Table1: toto, titi
Table2: toto, titi, tutu
Table3: tutu, tata
the new table will contain:
NewTable: toto, titi, tutu, tata
and i want to keep a reference that allows to retrieve that toto comes from
Table1 and Table2
tutu comes from Table2 and Table3...
if someone can help me it would be great.
Thanks.Beter than a single new table, You can make a View
UNION will eliminate duplicate data.
SELECT Field FROM Table1
UNION
SELECT Field FROM Table2
UNION
SELECT Field FROM Table3
To know where 'toto' come from:
SELECT TableName FROM
(
SELECT 'Table1' AS TableName, Field FROM Table1
UNION
SELECT 'Table2' AS TableName, Field FROM Table2
UNION
SELECT 'Table3' AS TableName, Field FROM Table3
) Tmp
WHERE Field='toto'
Laurent.
<Christophe> wrote in message news:OVqTbQGLEHA.1192@.TK2MSFTNGP11.phx.gbl...
> Hi,
> I want to eliminate duplicate entries from different tables into a single
> new table and keep the reference from the original data.
> Example:
> Table1: toto, titi
> Table2: toto, titi, tutu
> Table3: tutu, tata
> the new table will contain:
> NewTable: toto, titi, tutu, tata
> and i want to keep a reference that allows to retrieve that toto comes
from
> Table1 and Table2
> tutu comes from Table2 and Table3...
> if someone can help me it would be great.
> Thanks.
>

Efficiency with INNER JOINs

Hi,
Is there an efficiency issue with INNER JOINs? For example, is there a
difference betweeen tableA INNER JOIN tableB and tableB INNER JOIN tableA?
What's the performance doing tableA INNER JOIN tableB INNER JOIN tableC etc?
Thanks,
Mats-LennartMats
> Is there an efficiency issue with INNER JOINs? For example, is there a
> difference betweeen tableA INNER JOIN tableB and tableB INNER JOIN tableA?
>
Actually there is no difference. The optimizer will choose the order to join
tables.
> What's the performance doing tableA INNER JOIN tableB INNER JOIN tableC
> etc?
>
I have always tried to create an index on foreign key column ( participate
in JOIN) to improve performance
http://www.sql-server-performance.com/tuning_joins.asp
"Mats-Lennart Hansson" <ap_skallen@.hotmail.com> wrote in message
news:%23RygRo2OGHA.1288@.TK2MSFTNGP09.phx.gbl...
> Hi,
> Is there an efficiency issue with INNER JOINs? For example, is there a
> difference betweeen tableA INNER JOIN tableB and tableB INNER JOIN tableA?
> What's the performance doing tableA INNER JOIN tableB INNER JOIN tableC
> etc?
> Thanks,
> Mats-Lennart
>|||Differnent writing of the queries often doesn=B4t interfer the
performance difference in query, though the query pocessor and
optimizer will make a plan on his own which tabe to query first and so
on to get the best performance for your statement.
HTH, Jens Suessmeyer.

Editing the subject line for email subscriptions

Is there a way to get the subject line to report the values of parameters?
For example, "Report Date Range is {start date param} to {end date param}"Hi !
This can easily be achived by adding a reference in the sql string for the
subscription. Example for the parameter Product_Name:
Product_Name AS Subject
In the sample below the YearLastMonth, ForrigeMnd and FileName would be
selectable to the subject field as well..
Sample SQL Select:
SELECT Butikk_id_Parent, Produkt_id, Produkt_navn,
YEAR(DATEADD(m, - 1, GETDATE())) AS YearLastMonth,
ForrigeMnd as Mnd,
'Butikk' + CAST(Butikk_id_Parent AS VARCHAR) + '_' + CAST(Produkt_id AS
VARCHAR) AS FileName
FROM dbo.MAN_RPT_ButikkInfo_V
WHERE (EMail IS NULL)
Regards
Frank
"toolman_2000" wrote:
> Is there a way to get the subject line to report the values of parameters?
> For example, "Report Date Range is {start date param} to {end date param}"|||OK, but how do you get those values into the subject line on the subscription
set up page in Report manager? I want to use a parameter value the same way
the global values are used.
"frankiebody" wrote:
> Hi !
> This can easily be achived by adding a reference in the sql string for the
> subscription. Example for the parameter Product_Name:
> Product_Name AS Subject
> In the sample below the YearLastMonth, ForrigeMnd and FileName would be
> selectable to the subject field as well..
> Sample SQL Select:
> SELECT Butikk_id_Parent, Produkt_id, Produkt_navn,
> YEAR(DATEADD(m, - 1, GETDATE())) AS YearLastMonth,
> ForrigeMnd as Mnd,
> 'Butikk' + CAST(Butikk_id_Parent AS VARCHAR) + '_' + CAST(Produkt_id AS
> VARCHAR) AS FileName
> FROM dbo.MAN_RPT_ButikkInfo_V
> WHERE (EMail IS NULL)
> Regards
> Frank
> "toolman_2000" wrote:
> > Is there a way to get the subject line to report the values of parameters?
> > For example, "Report Date Range is {start date param} to {end date param}"|||What Frank refers to can only be done when using Data Driven Subscriptions.
You don't have access to the actual Parameter values, you would need to have
those values in a database table that you read during the execution of the
Data Driven subscription.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"toolman_2000" <toolman2000@.discussions.microsoft.com> wrote in message
news:D504C83D-1F98-481E-88B8-F9B9CE9FC8F4@.microsoft.com...
> OK, but how do you get those values into the subject line on the
> subscription
> set up page in Report manager? I want to use a parameter value the same
> way
> the global values are used.
> "frankiebody" wrote:
>> Hi !
>> This can easily be achived by adding a reference in the sql string for
>> the
>> subscription. Example for the parameter Product_Name:
>> Product_Name AS Subject
>> In the sample below the YearLastMonth, ForrigeMnd and FileName would be
>> selectable to the subject field as well..
>> Sample SQL Select:
>> SELECT Butikk_id_Parent, Produkt_id, Produkt_navn,
>> YEAR(DATEADD(m, - 1, GETDATE())) AS YearLastMonth,
>> ForrigeMnd as Mnd,
>> 'Butikk' + CAST(Butikk_id_Parent AS VARCHAR) + '_' + CAST(Produkt_id AS
>> VARCHAR) AS FileName
>> FROM dbo.MAN_RPT_ButikkInfo_V
>> WHERE (EMail IS NULL)
>> Regards
>> Frank
>> "toolman_2000" wrote:
>> > Is there a way to get the subject line to report the values of
>> > parameters?
>> > For example, "Report Date Range is {start date param} to {end date
>> > param}"|||it is still not clear how to do so.
let's say i have set a data driven subs. that returns all the email
addresses i want to send to something like:
select email,name,accountName from subscribers
where subscribe=1
how do i insert the field accountName into the subject of this mail?
how do i refer to the recordset field in the subject field?
pls show a simple example.
"Daniel Reib [MSFT]" wrote:
> What Frank refers to can only be done when using Data Driven Subscriptions.
> You don't have access to the actual Parameter values, you would need to have
> those values in a database table that you read during the execution of the
> Data Driven subscription.
> --
> -Daniel
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "toolman_2000" <toolman2000@.discussions.microsoft.com> wrote in message
> news:D504C83D-1F98-481E-88B8-F9B9CE9FC8F4@.microsoft.com...
> > OK, but how do you get those values into the subject line on the
> > subscription
> > set up page in Report manager? I want to use a parameter value the same
> > way
> > the global values are used.
> >
> > "frankiebody" wrote:
> >
> >> Hi !
> >> This can easily be achived by adding a reference in the sql string for
> >> the
> >> subscription. Example for the parameter Product_Name:
> >> Product_Name AS Subject
> >>
> >> In the sample below the YearLastMonth, ForrigeMnd and FileName would be
> >> selectable to the subject field as well..
> >>
> >> Sample SQL Select:
> >> SELECT Butikk_id_Parent, Produkt_id, Produkt_navn,
> >> YEAR(DATEADD(m, - 1, GETDATE())) AS YearLastMonth,
> >> ForrigeMnd as Mnd,
> >> 'Butikk' + CAST(Butikk_id_Parent AS VARCHAR) + '_' + CAST(Produkt_id AS
> >> VARCHAR) AS FileName
> >> FROM dbo.MAN_RPT_ButikkInfo_V
> >> WHERE (EMail IS NULL)
> >>
> >> Regards
> >> Frank
> >>
> >> "toolman_2000" wrote:
> >>
> >> > Is there a way to get the subject line to report the values of
> >> > parameters?
> >> > For example, "Report Date Range is {start date param} to {end date
> >> > param}"
>
>|||The data driven subscription is the way to do it. You will have to include
the field you are selecting from the table in your subscription query if you
want to display it in the subject line. When you edit the page for "special
delivery extension settings" in the data driven subscription you will want to
change the 'subject' to select "Get the value from the dataabase" click on
the dropdown and select the field you want to display.
Hope this hleps!
golf4fun
"Granov" wrote:
> it is still not clear how to do so.
> let's say i have set a data driven subs. that returns all the email
> addresses i want to send to something like:
> select email,name,accountName from subscribers
> where subscribe=1
> how do i insert the field accountName into the subject of this mail?
> how do i refer to the recordset field in the subject field?
> pls show a simple example.
> "Daniel Reib [MSFT]" wrote:
> > What Frank refers to can only be done when using Data Driven Subscriptions.
> > You don't have access to the actual Parameter values, you would need to have
> > those values in a database table that you read during the execution of the
> > Data Driven subscription.
> >
> > --
> > -Daniel
> > This posting is provided "AS IS" with no warranties, and confers no rights.
> >
> >
> > "toolman_2000" <toolman2000@.discussions.microsoft.com> wrote in message
> > news:D504C83D-1F98-481E-88B8-F9B9CE9FC8F4@.microsoft.com...
> > > OK, but how do you get those values into the subject line on the
> > > subscription
> > > set up page in Report manager? I want to use a parameter value the same
> > > way
> > > the global values are used.
> > >
> > > "frankiebody" wrote:
> > >
> > >> Hi !
> > >> This can easily be achived by adding a reference in the sql string for
> > >> the
> > >> subscription. Example for the parameter Product_Name:
> > >> Product_Name AS Subject
> > >>
> > >> In the sample below the YearLastMonth, ForrigeMnd and FileName would be
> > >> selectable to the subject field as well..
> > >>
> > >> Sample SQL Select:
> > >> SELECT Butikk_id_Parent, Produkt_id, Produkt_navn,
> > >> YEAR(DATEADD(m, - 1, GETDATE())) AS YearLastMonth,
> > >> ForrigeMnd as Mnd,
> > >> 'Butikk' + CAST(Butikk_id_Parent AS VARCHAR) + '_' + CAST(Produkt_id AS
> > >> VARCHAR) AS FileName
> > >> FROM dbo.MAN_RPT_ButikkInfo_V
> > >> WHERE (EMail IS NULL)
> > >>
> > >> Regards
> > >> Frank
> > >>
> > >> "toolman_2000" wrote:
> > >>
> > >> > Is there a way to get the subject line to report the values of
> > >> > parameters?
> > >> > For example, "Report Date Range is {start date param} to {end date
> > >> > param}"
> >
> >
> >