Easy query problem

Hello Experts-
I would like a sproc to return a 1 row select with results based on the
results of what it has found. For example say the following table were
created by the sp:
ID Name Dept
23 A 4
38 B 4
117 C 4
if the sproc could tell me which of these columns contained unique values
that would be great:
ID Name Dept
null null 4
In other words if all values in a column are the same, return that value,
otherwise return null.Select (Select Case When Count(*) = 1
Then Min(ID) Else Null End
From Table T
Group By ID) As ID,
(Select Case When Count(*) = 1
Then Min(Name) Else Null End
From Table T
Group By Name) As Name,
(Select Case When Count(*) = 1
Then Min(Dept) Else Null End
From Table T
Group By Dept) As Dept
"Coffee guy" wrote:

> Hello Experts-
> I would like a sproc to return a 1 row select with results based on the
> results of what it has found. For example say the following table were
> created by the sp:
> ID Name Dept
> 23 A 4
> 38 B 4
> 117 C 4
> if the sproc could tell me which of these columns contained unique values
> that would be great:
> ID Name Dept
> null null 4
> In other words if all values in a column are the same, return that value,
> otherwise return null.|||Sorry - messed that u.. Here's the right one...
Select (Select Case When Count(Distinct ID) = 1
Then Min(ID) Else Null End
From Table T) As ID,
(Select Case When Count(Distinct Name) = 1
Then Min(Name) Else Null End
From Table T) As Name,
(Select Case When Count(Distinct Dept) = 1
Then Min(Dept) Else Null End
From Table T) As Dept
"CBretana" wrote:
> Select (Select Case When Count(*) = 1
> Then Min(ID) Else Null End
> From Table T
> Group By ID) As ID,
> (Select Case When Count(*) = 1
> Then Min(Name) Else Null End
> From Table T
> Group By Name) As Name,
> (Select Case When Count(*) = 1
> Then Min(Dept) Else Null End
> From Table T
> Group By Dept) As Dept
>
> "Coffee guy" wrote:
>|||Coffee guy wrote:
> Hello Experts-
> I would like a sproc to return a 1 row select with results based on
> the results of what it has found. For example say the following
> table were created by the sp:
> ID Name Dept
> 23 A 4
> 38 B 4
> 117 C 4
> if the sproc could tell me which of these columns contained unique
> values that would be great:
> ID Name Dept
> null null 4
> In other words if all values in a column are the same, return that
> value, otherwise return null.
<snort>
What makes you think this query is "Easy"?
Try this:
CREATE TABLE #temp (
ID int,
Name varchar(10),
Dept int)
insert into #temp
select 23,'A',4
union all select 38,'B',4
union all select 117,'C',4
SELECT
(SELECT TOP 1 CASE WHEN
(SELECT COUNT(DISTINCT ID) FROM #temp)=1 THEN
ID
END FROM #temp) ID
,(SELECT TOP 1 CASE WHEN
(SELECT COUNT(DISTINCT Name) FROM #temp)=1 THEN
Name
END FROM #temp) Name
,(SELECT TOP 1 CASE WHEN
(SELECT COUNT(DISTINCT Dept) FROM #temp)=1 THEN
Dept
END FROM #temp) Dept
drop table #temp
Bob Barrows
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||Thanks to both, harder than I thought ;)
"Bob Barrows [MVP]" wrote:

> Coffee guy wrote:
> <snort>
> What makes you think this query is "Easy"?
> Try this:
> CREATE TABLE #temp (
> ID int,
> Name varchar(10),
> Dept int)
> insert into #temp
> select 23,'A',4
> union all select 38,'B',4
> union all select 117,'C',4
> SELECT
> (SELECT TOP 1 CASE WHEN
> (SELECT COUNT(DISTINCT ID) FROM #temp)=1 THEN
> ID
> END FROM #temp) ID
> ,(SELECT TOP 1 CASE WHEN
> (SELECT COUNT(DISTINCT Name) FROM #temp)=1 THEN
> Name
> END FROM #temp) Name
> ,(SELECT TOP 1 CASE WHEN
> (SELECT COUNT(DISTINCT Dept) FROM #temp)=1 THEN
> Dept
> END FROM #temp) Dept
> drop table #temp
> Bob Barrows
>
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>
>|||I think this is a bit simpler than what's been posted so far. Assuming
no NULLs in any of the columns,
select
case when min(ID) = max(ID) then min(ID) else null end as ID,
case when min(Name) = max(Name) then min(Name) else null end as Name,
case when min(Dept) = max(Dept) then min(Dept) else null end as Dept
from #temp
Steve Kass
Drew University
Coffee guy wrote:
>Thanks to both, harder than I thought ;)
>"Bob Barrows [MVP]" wrote:
>
>|||Duh! I definitely did not give this enough thought.
Thanks,
Bob
Steve Kass wrote:
> I think this is a bit simpler than what's been posted so far. Assuming no
> NULLs in any of the columns,
> select
> case when min(ID) = max(ID) then min(ID) else null end as ID,
> case when min(Name) = max(Name) then min(Name) else null end as Name,
> case when min(Dept) = max(Dept) then min(Dept) else null end as Dept
> from #temp
>
> Steve Kass
> Drew University
> Coffee guy wrote:
>
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"|||Steve,
Yes, Elegant !
"Steve Kass" wrote:

> I think this is a bit simpler than what's been posted so far. Assuming
> no NULLs in any of the columns,
> select
> case when min(ID) = max(ID) then min(ID) else null end as ID,
> case when min(Name) = max(Name) then min(Name) else null end as Name,
> case when min(Dept) = max(Dept) then min(Dept) else null end as Dept
> from #temp
>
> Steve Kass
> Drew University
> Coffee guy wrote:
>
>|||And if your fingers are tired, these are a tiny bit shorter,
but they're basically the same thing:
select
nullif(min(ID),nullif(min(ID), max(ID))) as ID,
nullif(min(Name),nullif(min(Name), max(Name))) as Name,
nullif(min(Dept),nullif(min(Dept), max(Dept))) as Dept
from #temp
select
case min(ID) when max(ID) then min(ID) end as ID,
case min(Name) when max(Name) then min(Name) end as Name,
case min(Dept) when max(Dept) then min(Dept) end as Dept
from #temp
SK
CBretana wrote:
>Steve,
>Yes, Elegant !
>
>"Steve Kass" wrote:
>
>sql

easy (hopefully) matrix question

I'm working with a matrix in the designer and noticed I need to add a new row in between two existing rows. When I right click and choose add row a row is added at the very bottom of the matrix. What's the easiest way to add a matrix row to the middle of a matrix, one that doesn't include editing the rdl directly.

thanks

I had this exact problem!

What I found out is that if you carefully (yes, carefully) drag your field between 2 existing data fields, there's a thin white bracket line that shows up on the outside of the nearest existing textbox. This is to alert you where the field will be placed. It will either be on the left, right, top or bottom. If none of these areas are white then keep looking because otherwise you'll replace the destination cells - (bad).

|||Thanks for the response! I can insert a row below the first row but every other row is missing that white line that indicates I can insert.

Can you insert between every row or just between the first and second rows?|||You should be able to do it anywhere.. try moving the mouse around more.
The only time I see that you don't get the white bar is when you don't even have an existing data field in the nearest textbox.|||
You're right. After expanding the box and some very careful mouse work I got it to insert. Hopefully the MSFT guys will make this very basic function easier to use. Thanks again for the help.

Dynamically selecting a row from a table

I have a function which i want to return where an id in a table exists somethign like
if exists (select id from @.tablename where id = @.id)
You cannot use dynamic SQL within TSQL UDFs. You should also use dynamic SQL with care. It has security and performance implications if used improperly. Why would you want to write a UDF that does if exists() check on any table? Isn't it easy just to write the query wherever you need it because that will be optimized better. You could consider writing this as a SP instead. And for the dynamic SQL to work you need to grant SELECT permissions for all users on the tables that you would check.

Dynamically removing table rows

How can I dynamically remove a table row if a value is blank?
(i.e. Address 2 is blank)If you select the entire table row in report designer, you will notice that
there is a Visibility.Hidden property for the table row. You can use an
expression that evaluates to a boolean value to dynamically hide a table row
then. E.g. =IsNothing(Fields!Address2.Value)
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Terry" <Terry@.discussions.microsoft.com> wrote in message
news:7439822C-5022-43CA-ABC4-1A970D55AA7D@.microsoft.com...
> How can I dynamically remove a table row if a value is blank?
> (i.e. Address 2 is blank)|||Thank you.
However, I tried on several occasions to get help with columns and no one is
able to assist.
Could someone provide me with a basic understanding how to setup columns?
The columns will be used for mailing labels.
Your assistance is greatly appreciated.
"Robert Bruckner [MSFT]" wrote:
> If you select the entire table row in report designer, you will notice that
> there is a Visibility.Hidden property for the table row. You can use an
> expression that evaluates to a boolean value to dynamically hide a table row
> then. E.g. =IsNothing(Fields!Address2.Value)
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Terry" <Terry@.discussions.microsoft.com> wrote in message
> news:7439822C-5022-43CA-ABC4-1A970D55AA7D@.microsoft.com...
> > How can I dynamically remove a table row if a value is blank?
> >
> > (i.e. Address 2 is blank)
>
>|||Not sure I understand your follow up question correctly. Are you asking
about dynamically hiding table columns in a report? Table columns have a
visibility property like table rows.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Terry" <Terry@.discussions.microsoft.com> wrote in message
news:5E4E4127-A82C-487C-8AB1-8B1D1B7D4C46@.microsoft.com...
> Thank you.
> However, I tried on several occasions to get help with columns and no one
> is
> able to assist.
> Could someone provide me with a basic understanding how to setup columns?
> The columns will be used for mailing labels.
> Your assistance is greatly appreciated.
> "Robert Bruckner [MSFT]" wrote:
>> If you select the entire table row in report designer, you will notice
>> that
>> there is a Visibility.Hidden property for the table row. You can use an
>> expression that evaluates to a boolean value to dynamically hide a table
>> row
>> then. E.g. =IsNothing(Fields!Address2.Value)
>> -- Robert
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>> "Terry" <Terry@.discussions.microsoft.com> wrote in message
>> news:7439822C-5022-43CA-ABC4-1A970D55AA7D@.microsoft.com...
>> > How can I dynamically remove a table row if a value is blank?
>> >
>> > (i.e. Address 2 is blank)
>>|||Dynamically removing table rows have been resolved.
Thank you for your assistance.
"Robert Bruckner [MSFT]" wrote:
> Not sure I understand your follow up question correctly. Are you asking
> about dynamically hiding table columns in a report? Table columns have a
> visibility property like table rows.
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Terry" <Terry@.discussions.microsoft.com> wrote in message
> news:5E4E4127-A82C-487C-8AB1-8B1D1B7D4C46@.microsoft.com...
> > Thank you.
> >
> > However, I tried on several occasions to get help with columns and no one
> > is
> > able to assist.
> >
> > Could someone provide me with a basic understanding how to setup columns?
> >
> > The columns will be used for mailing labels.
> >
> > Your assistance is greatly appreciated.
> >
> > "Robert Bruckner [MSFT]" wrote:
> >
> >> If you select the entire table row in report designer, you will notice
> >> that
> >> there is a Visibility.Hidden property for the table row. You can use an
> >> expression that evaluates to a boolean value to dynamically hide a table
> >> row
> >> then. E.g. =IsNothing(Fields!Address2.Value)
> >>
> >> -- Robert
> >> This posting is provided "AS IS" with no warranties, and confers no
> >> rights.
> >>
> >>
> >> "Terry" <Terry@.discussions.microsoft.com> wrote in message
> >> news:7439822C-5022-43CA-ABC4-1A970D55AA7D@.microsoft.com...
> >> > How can I dynamically remove a table row if a value is blank?
> >> >
> >> > (i.e. Address 2 is blank)
> >>
> >>
> >>
>
>

Dynamically execute a string as an expression

Hi,

Is it possible to execute a string which is entered in the value of a texbox.

For example:
I have a table with 4 groups. The detail row of the table is filled with a dynamic query like:
="SELECT FactSales.CustomerNr, " & Parameters!SalesFigure.Value & " AS SelectedFigure FROM DWHSales". The "SelectedFigure" comes from a Parameter Combobox. Because the SelectedFigure could not allways be sumed (sometimes I have to do some special math's), i will put in the group header rows a string like

=Code.GenerateSumString(......)

which returns a string like "Sum(Fields!Fieldx.Value)" and this string should be executed, Is there a mechanism like =Execute(Code.GenerateSumString(...)) available or how can I do such things?

Thanks
Hans

SSRS reports can call external .NET code. In your case, the external code can execute the SQL string against the database.

Dynamically delete a row from report

Hi,
I have a report containing some values. Surprise? :)
I want to delete rows containing values 0 and null from the report. I need
to include those rows to the sql query, but don't wan't them to be seen in
that particular table. Can the dynamic deletion of a row be done?
Thanks for helping the noobie!No problem anymore, I think.
I don't have to delete the row, instead I just use the filter in the table
to exclude the values I don't want to be shown...

Dynamic Sql to update a table row

Hi All MSSQL Experts-

I am facing a typical problem in one of my store procedure.

I am trying to update a table thru a dynamically generated SQL statement.

In my stored procedure I do all kinds of looping and manipultion to get this statement and execute like this:

mSQL = 'UPDATE TblSomeTable SET FldSomeFile=200 WHERE FldPK=100'
EXEC (mSQL)

This stuff has always worked for me, for Temporary tables, but some how does not seem to work on physical table. Am I doing something wrong? Missing something?

Any help will be greatly appreciated.

Best Wishes,Hi All-

Please never mind, the problem was with my field being int and the value I was trying to set it to was somethnig like 0.233, which eventually kept putting in there...

Regards

Dynamic Sql Intermittenetly Slow - Losing Hair Fast

Hi guys,
I've been staring at this stored proc for hours and I just can't see
what wrong with it. It will run fine 40+ times in a row and then one
time it it will take up-to 15 seconds to complete. Am baffled.
Anyway here's the query. It basically says, select all the hotel rates
where the option is in the OptionList parameter and the agentid = x and
the AgentPassword is equal to x. There is an added complication in that
I have to filter out any HotelRate that is zero for the selected room
types (single, double etc)
Any help appreciated!
Cheers, Pete (lad4bear)
CREATE PROCEDURE [dbo].[SelectMatchingHotelRates]
@.AgentId nvarchar(50),
@.AgentPassword nvarchar(50),
@.OptionList nvarchar(3900),
@.Single int,
@.Double int,
@.Twin int,
@.Triple int
AS
BEGIN
DECLARE @.dynamicSql nvarchar(4000)
SET @.dynamicSql =
'SELECT *
FROM
HotelsOptionRates
WHERE
[HotelOptionRates_OptionCode] IN ( ' + @.OptionList + ')
AND [HotelOptionRates_AgentId] = ''' + @.AgentId + '''
AND [HotelOptionRates_AgentPassword] = ''' + @.AgentPassword + ''''
IF (@.Single > 0)
SET @.DynamicSql = @.DynamicSql + ' AND [HotelOptionRates_SingleRate]
<> 0'
IF (@.Double > 0)
SET @.DynamicSql = @.DynamicSql + ' AND [HotelOptionRates_DoubleRate]
<> 0'
IF (@.Twin > 0)
SET @.DynamicSql = @.DynamicSql + ' AND [HotelOptionRates_TwinRate]
<> 0'
IF (@.Triple > 0)
SET @.DynamicSql = @.DynamicSql + ' AND [HotelOptionRates_TripleRate]
<> 0'
EXECUTE(@.dynamicSql)
END
GO<lad4bear@.gmail.com> wrote in message
news:1132944029.175652.67830@.f14g2000cwb.googlegroups.com...
> Hi guys,
> I've been staring at this stored proc for hours and I just can't see
> what wrong with it. It will run fine 40+ times in a row and then one
> time it it will take up-to 15 seconds to complete. Am baffled.
>
What is the execution plan when it takes 15 seconds to complete?
David|||I'm pretty sure that you can write the query without using dynamic SQL.
A lot of information at this site:
http://www.sommarskog.se/
Read "Dynamic Seach Conditions" first.
"Arrays and Lists in SQL server" will help you with this part:
> [HotelOptionRates_OptionCode] IN ( ' + @.OptionList + ')
<lad4bear@.gmail.com> wrote in message
news:1132944029.175652.67830@.f14g2000cwb.googlegroups.com...
> Hi guys,
> I've been staring at this stored proc for hours and I just can't see
> what wrong with it. It will run fine 40+ times in a row and then one
> time it it will take up-to 15 seconds to complete. Am baffled.
> Anyway here's the query. It basically says, select all the hotel rates
> where the option is in the OptionList parameter and the agentid = x and
> the AgentPassword is equal to x. There is an added complication in that
> I have to filter out any HotelRate that is zero for the selected room
> types (single, double etc)
> Any help appreciated!
> Cheers, Pete (lad4bear)
>
> CREATE PROCEDURE [dbo].[SelectMatchingHotelRates]
> @.AgentId nvarchar(50),
> @.AgentPassword nvarchar(50),
> @.OptionList nvarchar(3900),
> @.Single int,
> @.Double int,
> @.Twin int,
> @.Triple int
> AS
> BEGIN
> DECLARE @.dynamicSql nvarchar(4000)
> SET @.dynamicSql =
> 'SELECT *
> FROM
> HotelsOptionRates
> WHERE
> [HotelOptionRates_OptionCode] IN ( ' + @.OptionList + ')
> AND [HotelOptionRates_AgentId] = ''' + @.AgentId + '''
> AND [HotelOptionRates_AgentPassword] = ''' + @.AgentPassword + ''''
> IF (@.Single > 0)
> SET @.DynamicSql = @.DynamicSql + ' AND [HotelOptionRates_SingleRate]
> <> 0'
> IF (@.Double > 0)
> SET @.DynamicSql = @.DynamicSql + ' AND [HotelOptionRates_DoubleRate]
> <> 0'
> IF (@.Twin > 0)
> SET @.DynamicSql = @.DynamicSql + ' AND [HotelOptionRates_TwinRate]
> <> 0'
> IF (@.Triple > 0)
> SET @.DynamicSql = @.DynamicSql + ' AND [HotelOptionRates_TripleRate]
> <> 0'
> EXECUTE(@.dynamicSql)
> END
> GO
>|||To test a hypothesis I replaced the dynamic sql with similar (although not
the same) non-dynamic sql. Every forty or so attempts it takes 15 seconds to
complete.
Now currently I don't know if the stored proc itself is taking 15 seconds or
if the line of code I use to call the stored proc is taking 15 seconds. It's
one line of thoroughly tested code so I see no reason to that it should be
causing this problem.
As the database is remote I cannot use sql profiler to see how long the
stored proc is actually taking. Is there another way to get timing
information?
Thanks for your help
Pete (aka lad4bear)
"David Browne" wrote:

> <lad4bear@.gmail.com> wrote in message
> news:1132944029.175652.67830@.f14g2000cwb.googlegroups.com...
> What is the execution plan when it takes 15 seconds to complete?
> David
>
>|||Figured it out. I forgot I had scheduled batch update set to run every 3
minutes which was updating the table. Looks like I was running into some
locking issues. Thanks for the advice and the links
Cheers Pete (aka lad4bear)
"lad4bear" wrote:
> To test a hypothesis I replaced the dynamic sql with similar (although not
> the same) non-dynamic sql. Every forty or so attempts it takes 15 seconds
to
> complete.
> Now currently I don't know if the stored proc itself is taking 15 seconds
or
> if the line of code I use to call the stored proc is taking 15 seconds. It
's
> one line of thoroughly tested code so I see no reason to that it should be
> causing this problem.
> As the database is remote I cannot use sql profiler to see how long the
> stored proc is actually taking. Is there another way to get timing
> information?
> Thanks for your help
> Pete (aka lad4bear)
>
> "David Browne" wrote:
>