Can not access the Deleted System Table

I have a problem where I can not perform "Select * from Deleted" within
a trigger, but the problem occurs when I insert/update data from
Enterprise Manager ONLY. The trigger operates fine if I modify data in
Query Analyzer or through VBCode.
I finally found a workaround, modifying the select statement to "Select
* Into #deleted from Deleted", but am worried that will impact
performance on larger databases. I'm 99% certain it is just an access
issue somewhere, I just can't figure out where.
Any help y'all can suggest is appreciated.
Here is a snippet of the trigger:
CREATE trigger InsertUpdate_Table1
On Table1
For Update, Insert As
Declare @.Temp Int
Select * from Deleted
Select @.Temp = @.@.Rowcount
If @.Temp > 0
{rest of the code has been confirmed to work just fine}> Select * from Deleted
> Select @.Temp = @.@.Rowcount
So your trigger returns 2 resultsets? I can see how this would confuse EM
because special application code is needed to handle this situation.
This technique to get the number of updated rows is a bad practice for a
number of reasons. The proper way to get the number of updated rows is with
@.@.ROWCOUNT after the UPDATE statement rather in the trigger itself:
UPDATE dbo.Table1
SET MyColumn = 1
SELECT @.@.ROWCOUNT
Hope this helps.
Dan Guzman
SQL Server MVP
"Fayven" <FayvenWren@.gmail.com> wrote in message
news:1154833842.671883.224220@.b28g2000cwb.googlegroups.com...
>I have a problem where I can not perform "Select * from Deleted" within
> a trigger, but the problem occurs when I insert/update data from
> Enterprise Manager ONLY. The trigger operates fine if I modify data in
> Query Analyzer or through VBCode.
> I finally found a workaround, modifying the select statement to "Select
> * Into #deleted from Deleted", but am worried that will impact
> performance on larger databases. I'm 99% certain it is just an access
> issue somewhere, I just can't figure out where.
> Any help y'all can suggest is appreciated.
> Here is a snippet of the trigger:
> CREATE trigger InsertUpdate_Table1
> On Table1
> For Update, Insert As
> Declare @.Temp Int
> Select * from Deleted
> Select @.Temp = @.@.Rowcount
> If @.Temp > 0
> {rest of the code has been confirmed to work just fine}
>|||FYI - SQL EM is using RPC to run the SQL Statements
--
THANKS & PLEASE RATE THE POSTING.
--RAVI--
"Fayven" wrote:

> I have a problem where I can not perform "Select * from Deleted" within
> a trigger, but the problem occurs when I insert/update data from
> Enterprise Manager ONLY. The trigger operates fine if I modify data in
> Query Analyzer or through VBCode.
> I finally found a workaround, modifying the select statement to "Select
> * Into #deleted from Deleted", but am worried that will impact
> performance on larger databases. I'm 99% certain it is just an access
> issue somewhere, I just can't figure out where.
> Any help y'all can suggest is appreciated.
> Here is a snippet of the trigger:
> CREATE trigger InsertUpdate_Table1
> On Table1
> For Update, Insert As
> Declare @.Temp Int
> Select * from Deleted
> Select @.Temp = @.@.Rowcount
> If @.Temp > 0
> {rest of the code has been confirmed to work just fine}
>sql

Can IIF() function be used in SELECT statment?

I try to use IIF() function in SELECT statmenmt in a store procedure
difination.
SELECT iif( month(ref_date)=1 , 1 , 0 )
FROM MyTable
But the system always show me
Incorrect syntax near '='
What's the corect syntax of iif function?
Thank you.Hi!
There is no IIF function in T-SQL, but you can use CASE expression instead.
Do please look for the syntax in Books OnLine.
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"Xiaotao, Lu" <luxiaotao@.hotmail.com> wrote in message
news:eLm9ieCAEHA.1608@.TK2MSFTNGP11.phx.gbl...
> I try to use IIF() function in SELECT statmenmt in a store procedure
> difination.
> SELECT iif( month(ref_date)=1 , 1 , 0 )
> FROM MyTable
> But the system always show me
> Incorrect syntax near '='
> What's the corect syntax of iif function?
> Thank you.
>|||Use CASE:
SELECT CASE WHEN MONTH(ref_date)=1 THEN 1 ELSE 0 END
FROM MyTable
David Portas
SQL Server MVP
--

Can IIF() function be used in SELECT statment?

I try to use IIF() function in SELECT statmenmt in a store procedure
difination.
SELECT iif( month(ref_date)=1 , 1 , 0 )
FROM MyTable
But the system always show me
Incorrect syntax near '='
What's the corect syntax of iif function?
Thank you.Hi!
There is no IIF function in T-SQL, but you can use CASE expression instead.
Do please look for the syntax in Books OnLine.
--
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"Xiaotao, Lu" <luxiaotao@.hotmail.com> wrote in message
news:eLm9ieCAEHA.1608@.TK2MSFTNGP11.phx.gbl...
> I try to use IIF() function in SELECT statmenmt in a store procedure
> difination.
> SELECT iif( month(ref_date)=1 , 1 , 0 )
> FROM MyTable
> But the system always show me
> Incorrect syntax near '='
> What's the corect syntax of iif function?
> Thank you.
>|||Use CASE:
SELECT CASE WHEN MONTH(ref_date)=1 THEN 1 ELSE 0 END
FROM MyTable
--
David Portas
SQL Server MVP
--

Can I write query like this?

hi, guys

Can I write query like this:
I need to get the amount of stores that have @.PID inventory less than 2
select count(*) from
(
select StoreID, sum(Inventory) as Inv from products where productID = @.PID group by storeID
) StoreInv where StoreInv.Inv <= 2

thanks.

Yes, you can. Your query is valid so long as you use an alias name for the resultset generated by a nested query. Following is a very simple example to get the point across quickly:

Select Count(*) From (Select State from Authors) A

Can I write query like this

hi, guys
Can I write query like this:
I need to get the amount of stores that have @.PID inventory less than 2
select count(*) from
(
select StoreID, sum(Inventory) as Inv from products where
productID = @.PID group by storeID
) StoreInv where StoreInv.Inv <= 2
thanks.select StoreID, sum(Inventory) as Inv from products
where productID = @.PID
group by storeID
HAVING sum(Inventory) < 2
--
"Nick" wrote:

> hi, guys
> Can I write query like this:
> I need to get the amount of stores that have @.PID inventory less than 2
> select count(*) from
> (
> select StoreID, sum(Inventory) as Inv from products where
> productID = @.PID group by storeID
> ) StoreInv where StoreInv.Inv <= 2
> thanks.
>|||Select count(*) from
(
select StoreID, sum(Inventory) as Inv from products
where productID = @.PID
group by storeID
HAVING sum(Inventory) < 2
)
"Mark Williams" <MarkWilliams@.discussions.microsoft.com> wrote in message
news:711CF005-0EEF-4A2A-895C-5DFBAA3DC031@.microsoft.com...
> select StoreID, sum(Inventory) as Inv from products
> where productID = @.PID
> group by storeID
> HAVING sum(Inventory) < 2
> --
>
> "Nick" wrote:
>|||Oops! Misread the original posting. Thanks for the correction.
--
If you posted to this forum through TechNet, and you found my answers
helpful, please mark them as answers.
"Jim Underwood" wrote:

> Select count(*) from
> (
> select StoreID, sum(Inventory) as Inv from products
> where productID = @.PID
> group by storeID
> HAVING sum(Inventory) < 2
> )
> "Mark Williams" <MarkWilliams@.discussions.microsoft.com> wrote in message
> news:711CF005-0EEF-4A2A-895C-5DFBAA3DC031@.microsoft.com...
>
>

Can I use if statement in a table valued function?

hi,

I am using a function in sql server 2005 like this:

...... myfunction(... @.FlagOn int)

.......

begin

return

(

if(@.FlagOn = 1)

select * from.......

else

select * form....

)

end

But it keeps complaining there is some syntax error around if. What is it?

Thanks.

No. Table valued function won’t allow this. Because it might cause more than one schema definition for the table valued function, which is not possible in the database.

If it is a filter then you can attach the additional condition on the where clause. If the flag is used to fetch different columns or different table create new function for each flag.

If the final schema is same (same number of columns and identical datatype for both the flags), use table valued function (not inline table valued function).

Code Snippet

Create function getvalues(@.flag as int)

Returns @.result table (id int, name varchar(100))

As

Begin

If @.flag=1

Insert into @.result

Select Top 10 id, name from sysobjects

Else

Insert into @.result

Select Top 10 id,name from syscolumns

return;

End

Go

Select * from getvalues(1)

go

Select * from getvalues(0)

|||

Thanks lot, it works great.

Another thing is, can I create index on table @.result?

Coz I am thinking something like this:

select * from getvalues(1) as s where s.ID > 100

If it has index on ID, it might be faster.

|||

Maximum you can add Primary Key/Unique key on the table variable. If your values are unique then you can go for it.

@.result table (id int primary key, name varchar(100))

Can I set DrillThrough Details column order?

AS2005 ... is there a way to set the order for drillthrough column details?

as far as I can see I can only select which attributes will be included ... is there a way to set the order?

would i have to redesign the dimension?

In the dimension desgin, in the tab attributes, select the field you want to sort, and right-click and Properties... there is the property orderBY and OrderByAttribute! change it!

regards!

Can I run a select statement w/o specifing the owners table name?

Can I run a select statement w/o specifing the owners table name?

How is it possible to run a select query without specifing the owners table name?
For example:

To use:

Query1: Select * from customers;

instead of

Query 2: Select * from user1.customers;

Now, when user1 or even user2 tries to open the Query1 I get the error that object 'customers' does not exist.You can run it with out the owner name, if you are logged in as the object owner. Otherwise, you have to specify it.|||...unless the owner is dbo. If all your tables are dbo owned you don't need to specify the owner as dbo is assumed as default.

blindman

Can I release temp SQL Tables?

In Query Analyzer I am creating a Select statement using temp tables. For a
simple example:
Select abc.def into #tmptable from ABCTable.
If I make changes to the query and then rerun it, it tells me that the
object (which is the temp table) already exist in the database. I have to
close my Query Analyzer window and then reopen it.
So my question is this: Is there a command that I can include in the Query
Analyzer window that will release the Temp Table without having to close the
window?
DROP TABLE #TABLENAME
"Preacher Man" <nospam> wrote in message
news:OaUixWGRGHA.4952@.TK2MSFTNGP09.phx.gbl...
> In Query Analyzer I am creating a Select statement using temp tables. For
> a simple example:
> Select abc.def into #tmptable from ABCTable.
> If I make changes to the query and then rerun it, it tells me that the
> object (which is the temp table) already exist in the database. I have to
> close my Query Analyzer window and then reopen it.
> So my question is this: Is there a command that I can include in the
> Query Analyzer window that will release the Temp Table without having to
> close the window?
>
|||drop table #tmptable
"Preacher Man" <nospam> wrote in message
news:OaUixWGRGHA.4952@.TK2MSFTNGP09.phx.gbl...
> In Query Analyzer I am creating a Select statement using temp tables. For
> a simple example:
> Select abc.def into #tmptable from ABCTable.
> If I make changes to the query and then rerun it, it tells me that the
> object (which is the temp table) already exist in the database. I have to
> close my Query Analyzer window and then reopen it.
> So my question is this: Is there a command that I can include in the
> Query Analyzer window that will release the Temp Table without having to
> close the window?
>
|||Thanks everyone for the quick response.
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:ejKaNaGRGHA.4920@.tk2msftngp13.phx.gbl...
> drop table #tmptable
>
> "Preacher Man" <nospam> wrote in message
> news:OaUixWGRGHA.4952@.TK2MSFTNGP09.phx.gbl...
>
|||and u can put a check in too :
IF OBJECT_ID ('tempdb..#testtemp') IS NOT NULL
BEGIN
DROP TABLE #testtemp
END
CREATE TABLE #testtemp (numb int)
INSERT #testtemp VALUES (1)
INSERT #testtemp VALUES (1)
"Preacher Man" <nospam> wrote in message
news:OaUixWGRGHA.4952@.TK2MSFTNGP09.phx.gbl...
> In Query Analyzer I am creating a Select statement using temp tables. For
> a simple example:
> Select abc.def into #tmptable from ABCTable.
> If I make changes to the query and then rerun it, it tells me that the
> object (which is the temp table) already exist in the database. I have to
> close my Query Analyzer window and then reopen it.
> So my question is this: Is there a command that I can include in the
> Query Analyzer window that will release the Temp Table without having to
> close the window?
>

Can I release temp SQL Tables?

In Query Analyzer I am creating a Select statement using temp tables. For a
simple example:
Select abc.def into #tmptable from ABCTable.
If I make changes to the query and then rerun it, it tells me that the
object (which is the temp table) already exist in the database. I have to
close my Query Analyzer window and then reopen it.
So my question is this: Is there a command that I can include in the Query
Analyzer window that will release the Temp Table without having to close the
window?DROP TABLE #TABLENAME
"Preacher Man" <nospam> wrote in message
news:OaUixWGRGHA.4952@.TK2MSFTNGP09.phx.gbl...
> In Query Analyzer I am creating a Select statement using temp tables. For
> a simple example:
> Select abc.def into #tmptable from ABCTable.
> If I make changes to the query and then rerun it, it tells me that the
> object (which is the temp table) already exist in the database. I have to
> close my Query Analyzer window and then reopen it.
> So my question is this: Is there a command that I can include in the
> Query Analyzer window that will release the Temp Table without having to
> close the window?
>|||drop table #tmptable
"Preacher Man" <nospam> wrote in message
news:OaUixWGRGHA.4952@.TK2MSFTNGP09.phx.gbl...
> In Query Analyzer I am creating a Select statement using temp tables. For
> a simple example:
> Select abc.def into #tmptable from ABCTable.
> If I make changes to the query and then rerun it, it tells me that the
> object (which is the temp table) already exist in the database. I have to
> close my Query Analyzer window and then reopen it.
> So my question is this: Is there a command that I can include in the
> Query Analyzer window that will release the Temp Table without having to
> close the window?
>|||Thanks everyone for the quick response.
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:ejKaNaGRGHA.4920@.tk2msftngp13.phx.gbl...
> drop table #tmptable
>
> "Preacher Man" <nospam> wrote in message
> news:OaUixWGRGHA.4952@.TK2MSFTNGP09.phx.gbl...
>|||and u can put a check in too :
IF OBJECT_ID ('tempdb..#testtemp') IS NOT NULL
BEGIN
DROP TABLE #testtemp
END
CREATE TABLE #testtemp (numb int)
INSERT #testtemp VALUES (1)
INSERT #testtemp VALUES (1)
"Preacher Man" <nospam> wrote in message
news:OaUixWGRGHA.4952@.TK2MSFTNGP09.phx.gbl...
> In Query Analyzer I am creating a Select statement using temp tables. For
> a simple example:
> Select abc.def into #tmptable from ABCTable.
> If I make changes to the query and then rerun it, it tells me that the
> object (which is the temp table) already exist in the database. I have to
> close my Query Analyzer window and then reopen it.
> So my question is this: Is there a command that I can include in the
> Query Analyzer window that will release the Temp Table without having to
> close the window?
>

Can I release temp SQL Tables?

In Query Analyzer I am creating a Select statement using temp tables. For a
simple example:
Select abc.def into #tmptable from ABCTable.
If I make changes to the query and then rerun it, it tells me that the
object (which is the temp table) already exist in the database. I have to
close my Query Analyzer window and then reopen it.
So my question is this: Is there a command that I can include in the Query
Analyzer window that will release the Temp Table without having to close the
window?DROP TABLE #TABLENAME
"Preacher Man" <nospam> wrote in message
news:OaUixWGRGHA.4952@.TK2MSFTNGP09.phx.gbl...
> In Query Analyzer I am creating a Select statement using temp tables. For
> a simple example:
> Select abc.def into #tmptable from ABCTable.
> If I make changes to the query and then rerun it, it tells me that the
> object (which is the temp table) already exist in the database. I have to
> close my Query Analyzer window and then reopen it.
> So my question is this: Is there a command that I can include in the
> Query Analyzer window that will release the Temp Table without having to
> close the window?
>|||drop table #tmptable
"Preacher Man" <nospam> wrote in message
news:OaUixWGRGHA.4952@.TK2MSFTNGP09.phx.gbl...
> In Query Analyzer I am creating a Select statement using temp tables. For
> a simple example:
> Select abc.def into #tmptable from ABCTable.
> If I make changes to the query and then rerun it, it tells me that the
> object (which is the temp table) already exist in the database. I have to
> close my Query Analyzer window and then reopen it.
> So my question is this: Is there a command that I can include in the
> Query Analyzer window that will release the Temp Table without having to
> close the window?
>|||Thanks everyone for the quick response.
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in mess
age
news:ejKaNaGRGHA.4920@.tk2msftngp13.phx.gbl...
> drop table #tmptable
>
> "Preacher Man" <nospam> wrote in message
> news:OaUixWGRGHA.4952@.TK2MSFTNGP09.phx.gbl...
>|||and u can put a check in too :
IF OBJECT_ID ('tempdb..#testtemp') IS NOT NULL
BEGIN
DROP TABLE #testtemp
END
CREATE TABLE #testtemp (numb int)
INSERT #testtemp VALUES (1)
INSERT #testtemp VALUES (1)
"Preacher Man" <nospam> wrote in message
news:OaUixWGRGHA.4952@.TK2MSFTNGP09.phx.gbl...
> In Query Analyzer I am creating a Select statement using temp tables. For
> a simple example:
> Select abc.def into #tmptable from ABCTable.
> If I make changes to the query and then rerun it, it tells me that the
> object (which is the temp table) already exist in the database. I have to
> close my Query Analyzer window and then reopen it.
> So my question is this: Is there a command that I can include in the
> Query Analyzer window that will release the Temp Table without having to
> close the window?
>

Can I query my report dataset to populate a textbox?

My report is a series of textbox fields that all will show data from an
Activity table. The report dataset is SELECT * FROM Activity and is named
"Activities".
Textbox1 = count of all activities
Textbox2 = count of all activities where type = prospect
Textbox3 = count of all activities where type = demo
I know I can create separate datasets for each subsequent query but since
they are all queries from the Activity table, I was wondering if I could set
the value for Textbox2 by querying the dataset named "Activities" instead of
creating a new dataset. For example, SELECT Count(*) FROM Activities Where
Type = 'prospect'
Thank you,
MarkOn Apr 6, 11:01=A0am, Markw911 <Markw...@.discussions.microsoft.com>
wrote:
> My report is a series of textbox fields that all will show data from an
> Activity table. =A0The report dataset is SELECT * FROM Activity and is nam=ed
> "Activities".
> Textbox1 =3D count of all activities
> Textbox2 =3D count of all activities where type =3D prospect
> Textbox3 =3D count of all activities where type =3D demo
> I know I can create separate datasets for each subsequent query but since
> they are all queries from the Activity table, I was wondering if I could s=et
> the value for Textbox2 by querying the dataset named "Activities" instead =of
> creating a new dataset. =A0For example, SELECT Count(*) FROM Activities Wh=ere
> Type =3D 'prospect'
> Thank you,
> Mark
Is there a particular reason you're using a series of textboxes
instead of a table? With a table you could just draw what you need
directly from your dataset.|||I am trying to mimic the layout of an existing report. I had a table but did
not think it could handle some of the more detailed results like:
Count column meetingcanceled if meetingcanceled = 'yes'
I still am not sure how I would handle this in a table:
> > Textbox2 = count of all activities where type = prospect
> > Textbox3 = count of all activities where type = demo
since I am trying to return multiple totals from the same column.
From your comments, it sounds like I could have multiple sums per column
with different criteria. If that is true, how do I go about doing it? Use
the activities type column above to give an example if possible.
Thanks.
"toolman" wrote:
> On Apr 6, 11:01 am, Markw911 <Markw...@.discussions.microsoft.com>
> wrote:
> > My report is a series of textbox fields that all will show data from an
> > Activity table. The report dataset is SELECT * FROM Activity and is named
> > "Activities".
> > Textbox1 = count of all activities
> > Textbox2 = count of all activities where type = prospect
> > Textbox3 = count of all activities where type = demo
> >
> > I know I can create separate datasets for each subsequent query but since
> > they are all queries from the Activity table, I was wondering if I could set
> > the value for Textbox2 by querying the dataset named "Activities" instead of
> > creating a new dataset. For example, SELECT Count(*) FROM Activities Where
> > Type = 'prospect'
> > Thank you,
> > Mark
> Is there a particular reason you're using a series of textboxes
> instead of a table? With a table you could just draw what you need
> directly from your dataset.
>|||On Apr 8, 7:54=A0pm, Markw911 <Markw...@.discussions.microsoft.com>
wrote:
> I am trying to mimic the layout of an existing report. =A0I had a table bu=t did
> not think it could handle some of the more detailed results like:
> Count column meetingcanceled if meetingcanceled =3D 'yes'
> I still am not sure how I would handle this in a table:> > Textbox2 =3D co=unt of all activities where type =3D prospect
> > > Textbox3 =3D count of all activities where type =3D demo
> since I am trying to return multiple totals from the same column.
> From your comments, it sounds like I could have multiple sums per column
> with different criteria. =A0If that is true, how do I go about doing it? ==A0Use
> the activities type column above to give an example if possible.
> Thanks.
>
> "toolman" wrote:
> > On Apr 6, 11:01 am, Markw911 <Markw...@.discussions.microsoft.com>
> > wrote:
> > > My report is a series of textbox fields that all will show data from a=n
> > > Activity table. =A0The report dataset is SELECT * FROM Activity and is= named
> > > "Activities".
> > > Textbox1 =3D count of all activities
> > > Textbox2 =3D count of all activities where type =3D prospect
> > > Textbox3 =3D count of all activities where type =3D demo
> > > I know I can create separate datasets for each subsequent query but si=nce
> > > they are all queries from the Activity table, I was wondering if I cou=ld set
> > > the value for Textbox2 by querying the dataset named "Activities" inst=ead of
> > > creating a new dataset. =A0For example, SELECT Count(*) FROM Activitie=s Where
> > > Type =3D 'prospect'
> > > Thank you,
> > > Mark
> > Is there a particular reason you're using a series of textboxes
> > instead of a table? =A0With a table you could just =A0draw what you need=
> > directly from your dataset.- Hide quoted text -
> - Show quoted text -
You could insert the following expressions into your table cells in
the table footer row. Hide or remove the detail row.
Table Header Row: All Activities_____________/
Prospects______________________________/Demos
Table Footer Row: =3DCount(Fields!Type.Value) =3DSUM(IIF(Fields!
Type.Value =3D "Prospect",1,0)) =3DSUM(IIF(Fields!Type.Value =3D "Demo",
1,0))
OR
Substitute =3DCOUNT(IIF(Fields!Type.Value =3D "Prospect",Fields!
Type.Value,Nothing)) for =3DSUM(IIF(Fields!Type.Value =3D "Prospect",1,0))
Either expression works
Good luck|||On Apr 9, 3:31=A0pm, toolman <t...@.infocision.com> wrote:
> On Apr 8, 7:54=A0pm, Markw911 <Markw...@.discussions.microsoft.com>
> wrote:
>
> > I am trying to mimic the layout of an existing report. =A0I had a table =but did
> > not think it could handle some of the more detailed results like:
> > Count column meetingcanceled if meetingcanceled =3D 'yes'
> > I still am not sure how I would handle this in a table:> > Textbox2 =3D =count of all activities where type =3D prospect
> > > > Textbox3 =3D count of all activities where type =3D demo
> > since I am trying to return multiple totals from the same column.
> > From your comments, it sounds like I could have multiple sums per column=
> > with different criteria. =A0If that is true, how do I go about doing it?= =A0Use
> > the activities type column above to give an example if possible.
> > Thanks.
> > "toolman" wrote:
> > > On Apr 6, 11:01 am, Markw911 <Markw...@.discussions.microsoft.com>
> > > wrote:
> > > > My report is a series of textbox fields that all will show data from= an
> > > > Activity table. =A0The report dataset is SELECT * FROM Activity and =is named
> > > > "Activities".
> > > > Textbox1 =3D count of all activities
> > > > Textbox2 =3D count of all activities where type =3D prospect
> > > > Textbox3 =3D count of all activities where type =3D demo
> > > > I know I can create separate datasets for each subsequent query but =since
> > > > they are all queries from the Activity table, I was wondering if I c=ould set
> > > > the value for Textbox2 by querying the dataset named "Activities" in=stead of
> > > > creating a new dataset. =A0For example, SELECT Count(*) FROM Activit=ies Where
> > > > Type =3D 'prospect'
> > > > Thank you,
> > > > Mark
> > > Is there a particular reason you're using a series of textboxes
> > > instead of a table? =A0With a table you could just =A0draw what you ne=ed
> > > directly from your dataset.- Hide quoted text -
> > - Show quoted text -
> You could insert the following expressions into your table cells in
> the table footer row. =A0Hide or remove the detail row.
> Table Header Row: All Activities_____________/
> Prospects______________________________/Demos
> Table Footer =A0Row: =3DCount(Fields!Type.Value) =A0 =3DSUM(IIF(Fields!
> Type.Value =3D "Prospect",1,0)) =3DSUM(IIF(Fields!Type.Value =3D "Demo",
> 1,0))
> OR
> Substitute =3DCOUNT(IIF(Fields!Type.Value =3D "Prospect",Fields!
> Type.Value,Nothing)) for =3DSUM(IIF(Fields!Type.Value =3D "Prospect",1,0))=
> Either expression works
> Good luck- Hide quoted text -
> - Show quoted text -
That's really ugly...
Hopefully, this is more clear
Table Header Row:
Column 1: All Activities Column 2: Prospects Column 3: Demos
Table Footer Row:
Column 1: =3DCount(Fields!Type.Value)
Column 2: =3DSUM(IIF(Fields!
Type.Value =3D "Prospect",1,0))
Column 3: =3DSUM(IIF(Fields!Type.Value =3D "Demo",1,0))

Can I Merge a table with itself ?

Bishman,
select
a.c1,
case b.c1
when 1 then a.c2
when 2 then a.c3
when 3 then a.c4
end as new_c2
from
dbo.t1 as a cross join (select 1 as c1 union all select 2 union all
select 3) as b
go
-- or
select c1, c2 as new_c2
from dbo.t1
union all
select c1, c3
from dbo.t1
union all
select c1, c4
from dbo.t1
go
AMB
"Bishman" wrote:

> Hi,
> I have a table which contains several integer columns that represent
> similiar date related information. ie
> RenewalMonthDay,HomeMonthDay,MotorMonthDay etc.
> I need a view of the table where these different columns have been merged
> into a single column, so where there was one row with x number of integers,
> I would have X number of rows with a single integer. Is this possible ?
> What I am actually wanting to do is sort the rows based on the value that is
> highest / lowest from the values in the row.
> Can this be done ?
> Thanks,
> Jon.
>
>
Bishman,
If you are working with 2005, then you can also use new operator "unpivot".
create table dbo.t1 (
a int not null identity unique,
b int,
c int,
d int
)
go
insert into dbo.t1(b, c, d) values(10, 20, 30)
insert into dbo.t1(b, c, d) values(40, 50, 60)
go
select
a, val
from
(
select a, b as c1, c as c2, d as c3
from dbo.t1
) as pvt
unpivot
(val for c in (c1, c2, c3)) as unpvt
go
drop table dbo.t1
go
AMB
"Alejandro Mesa" wrote:
[vbcol=seagreen]
> Bishman,
> select
> a.c1,
> case b.c1
> when 1 then a.c2
> when 2 then a.c3
> when 3 then a.c4
> end as new_c2
> from
> dbo.t1 as a cross join (select 1 as c1 union all select 2 union all
> select 3) as b
> go
> -- or
> select c1, c2 as new_c2
> from dbo.t1
> union all
> select c1, c3
> from dbo.t1
> union all
> select c1, c4
> from dbo.t1
> go
>
> AMB
> "Bishman" wrote: