computed value

I've added a column to my table as a computed value, pretty much only to
populate it for the existing data. I want to do this, then drop the compute
d
value, make it's new column NOT NULL and lay over it a unique constraint.
Can I drop the computed value w/out dropping the column?
-- Lynn"Lynn" <Lynn@.discussions.microsoft.com> wrote in message
news:4052E967-8301-4F0D-A2FF-C721068FC0B3@.microsoft.com...
> I've added a column to my table as a computed value, pretty much only to
> populate it for the existing data. I want to do this, then drop the
> computed
> value, make it's new column NOT NULL and lay over it a unique constraint.
> Can I drop the computed value w/out dropping the column?
> -- Lynn
Why not add it as NOT NULL and default it to zero, or x or whatever.
Then run and update on the column and compute your new values.
Better yet, why are you storing a computed value anyhow? Why not just use a
SELECT statement or a view and compute the value on the fly?
Rick Sawtell|||A computed column or one being used in a computed column cannot be altered,
nor can a computed column be updated.
What are you traing to do? If you need to change the behaviour of a computed
column, you need to drop it first. You cannot add a non-nullable column
without specifying a default value.
However, you can create a temporary table to store the values of the
computed column, drop the column, add a new column (it needs to be nullable)
,
then fill it with previous values, and alter it to make it non-nullable.
And if you post DDL and sample data, we can help you build a script to
achieve all this.
ML|||Thank you both. I think I realized I was overthinking this one a bit. It
doesn't have to be computed. I put MsgID on as varchar(64) NOT NULL, update
d
it for existing data w/this:
UPDATE tableA...
SET MsgID = endpoint+(convert(varchar(8),[exectime],
112) + [ordernumber])
GO
Then I changed it to NOT NULL and created the unique constraint. All
w/existing data in the table. What do you guys think?
-- Lynn
"ML" wrote:

> A computed column or one being used in a computed column cannot be altered
,
> nor can a computed column be updated.
> What are you traing to do? If you need to change the behaviour of a comput
ed
> column, you need to drop it first. You cannot add a non-nullable column
> without specifying a default value.
> However, you can create a temporary table to store the values of the
> computed column, drop the column, add a new column (it needs to be nullabl
e),
> then fill it with previous values, and alter it to make it non-nullable.
> And if you post DDL and sample data, we can help you build a script to
> achieve all this.
>
> ML|||Does it work as it is supposed to work? :)
Looks like you've nailed it.
ML|||I don't know yet, it's still running now. I am sure hoping we're good on
this one...
-- Lynn
"ML" wrote:

> Does it work as it is supposed to work? :)
> Looks like you've nailed it.
>
> ML|||Worked beautifully. Thank you guys for looking into this w/me.
-- Lynn
"ML" wrote:

> Does it work as it is supposed to work? :)
> Looks like you've nailed it.
>
> ML|||On Wed, 7 Sep 2005 12:01:02 -0700, Lynn wrote:

>Thank you both. I think I realized I was overthinking this one a bit. It
>doesn't have to be computed. I put MsgID on as varchar(64) NOT NULL, updat
ed
>it for existing data w/this:
>UPDATE tableA...
>SET MsgID = endpoint+(convert(varchar(8),[exectime],
112) + [ordernumber])
>GO
>Then I changed it to NOT NULL and created the unique constraint. All
>w/existing data in the table. What do you guys think?
Hi Lynn,
If the MsgID column will always be the concatenation of these three
other columns, than I wouldn't store it like this. Create a view that
does the concatenation if you prefer the ease of use.
For enforcing uniqueness, just do
ALTER TABLE tableA
ADD CONSTRAINT MyUniq UNIQUE (endpoint, exectime, ordernumber)
No need to add an extra column for this.
Of course, if the MsgID for NEW rows in the database will be filled with
other values, and this concatenation is just the "starting" value for
existing rows, then the above doesn't apply.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Indexing that view might also be of help - that way the concatenated values
are stored as if the view were a table. Views aren't cached permanently.
However, as Hugo already stated, this only applies if the values need to be
generated by concatenation *every time*.
MLsqlsql

complicated sql query

Hello all,
I have a pretty complicated query (at least for me) I'd need some help
on.
Here's the thing.
I have an occupancy sensor that logs a timestamp in a log table every
10 seconds when a person is detected.
If no one is detected, no log is written.
Now I need to count the number of visit and the duration on that room
so I built that query:
SELECT TOP (100) PERCENT ActivityID, LocationName, Timestamp,
DATEDIFF(ss,
(SELECT MAX(Timestamp)
FROM dbo.tbl_ActivityLog AS
Previous
WHERE (LocationName =
[Current].LocationName) AND (Timestamp < [Current].Timestamp) AND
(DATEDIFF(ss,
Timestamp, [Current].Timestamp) <= 15)), Timestamp) AS Duration
FROM dbo.tbl_ActivityLog AS [Current]
That query works well, retrieving a row for every 10 seconds when
someone is in the room and with a Duration=NULL when the difference
between 2 detection is > 15 s to count that case as a different visit
(15 because sometimes is the sensor is too busy, it takes more than 10
seconds to notify)
Here's a simple example of data retrieved with that query:
ActivityID Location Timestamp Duration
1 loc1 8/13/2007 1:18:03 AM NULL
2 loc1 8/13/2007 1:18:13 AM 10
3 loc1 8/13/2007 1:18:23 AM 10
4 loc1 8/13/2007 3:34:50 AM NULL
When the duration is null, I know than between the last visit and the
current visit, more than 15 seconds passed so I have to count it as a
new visit.
Now my question:
I would like to have a query that will give me:
- the number of visits for a location,
- the duration for each visit.
Thanks for your help, and sorry to be so verbose
Patrice
On Tue, 14 Aug 2007 11:40:20 -0700, plevexier@.gmail.com wrote:

>Hello all,
>I have a pretty complicated query (at least for me) I'd need some help
>on.
>Here's the thing.
(snip)
>Now my question:
>I would like to have a query that will give me:
>- the number of visits for a location,
>- the duration for each visit.
>
>Thanks for your help, and sorry to be so verbose
Hi Patrice,
Could you please post the table structure (as a CREATE TABLE statement,
including all constraints, properties, and indexes - though you may omit
irrelevant columns), some well-chosen rows of sample data (as INSERT
statemtents), and expected results? See www.aspfaq.com/5006 as well.
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

complicated sql query

Hello all,
I have a pretty complicated query (at least for me) I'd need some help
on.
Here's the thing.
I have an occupancy sensor that logs a timestamp in a log table every
10 seconds when a person is detected.
If no one is detected, no log is written.
Now I need to count the number of visit and the duration on that room
so I built that query:
SELECT TOP (100) PERCENT ActivityID, LocationName, Timestamp,
DATEDIFF(ss,
(SELECT MAX(Timestamp)
FROM dbo.tbl_ActivityLog AS
Previous
WHERE (LocationName =
[Current].LocationName) AND (Timestamp < [Current].Timestamp) AND
(DATEDIFF(ss,
Timestamp, [Current].Timestamp) <= 15)), Timestamp) AS Duration
FROM dbo.tbl_ActivityLog AS [Current]
That query works well, retrieving a row for every 10 seconds when
someone is in the room and with a Duration=NULL when the difference
between 2 detection is > 15 s to count that case as a different visit
(15 because sometimes is the sensor is too busy, it takes more than 10
seconds to notify)
Here's a simple example of data retrieved with that query:
ActivityID Location Timestamp Duration
1 loc1 8/13/2007 1:18:03 AM NULL
2 loc1 8/13/2007 1:18:13 AM 10
3 loc1 8/13/2007 1:18:23 AM 10
4 loc1 8/13/2007 3:34:50 AM NULL
When the duration is null, I know than between the last visit and the
current visit, more than 15 seconds passed so I have to count it as a
new visit.
Now my question:
I would like to have a query that will give me:
- the number of visits for a location,
- the duration for each visit.
Thanks for your help, and sorry to be so verbose
PatriceOn Tue, 14 Aug 2007 11:40:20 -0700, plevexier@.gmail.com wrote:

>Hello all,
>I have a pretty complicated query (at least for me) I'd need some help
>on.
>Here's the thing.
(snip)
>Now my question:
>I would like to have a query that will give me:
>- the number of visits for a location,
>- the duration for each visit.
>
>Thanks for your help, and sorry to be so verbose
Hi Patrice,
Could you please post the table structure (as a CREATE TABLE statement,
including all constraints, properties, and indexes - though you may omit
irrelevant columns), some well-chosen rows of sample data (as INSERT
statemtents), and expected results? See www.aspfaq.com/5006 as well.
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

complicated sql query

Hello all,
I have a pretty complicated query (at least for me) I'd need some help
on.
Here's the thing.
I have an occupancy sensor that logs a timestamp in a log table every
10 seconds when a person is detected.
If no one is detected, no log is written.
Now I need to count the number of visit and the duration on that room
so I built that query:
SELECT TOP (100) PERCENT ActivityID, LocationName, Timestamp,
DATEDIFF(ss,
(SELECT MAX(Timestamp)
FROM dbo.tbl_ActivityLog AS
Previous
WHERE (LocationName = [Current].LocationName) AND (Timestamp < [Current].Timestamp) AND
(DATEDIFF(ss,
Timestamp, [Current].Timestamp) <= 15)), Timestamp) AS Duration
FROM dbo.tbl_ActivityLog AS [Current]
That query works well, retrieving a row for every 10 seconds when
someone is in the room and with a Duration=NULL when the difference
between 2 detection is > 15 s to count that case as a different visit
(15 because sometimes is the sensor is too busy, it takes more than 10
seconds to notify)
Here's a simple example of data retrieved with that query:
ActivityID Location Timestamp Duration
1 loc1 8/13/2007 1:18:03 AM NULL
2 loc1 8/13/2007 1:18:13 AM 10
3 loc1 8/13/2007 1:18:23 AM 10
4 loc1 8/13/2007 3:34:50 AM NULL
When the duration is null, I know than between the last visit and the
current visit, more than 15 seconds passed so I have to count it as a
new visit.
Now my question:
I would like to have a query that will give me:
- the number of visits for a location,
- the duration for each visit.
Thanks for your help, and sorry to be so verbose :)
PatriceOn Tue, 14 Aug 2007 11:40:20 -0700, plevexier@.gmail.com wrote:
>Hello all,
>I have a pretty complicated query (at least for me) I'd need some help
>on.
>Here's the thing.
(snip)
>Now my question:
>I would like to have a query that will give me:
>- the number of visits for a location,
>- the duration for each visit.
>
>Thanks for your help, and sorry to be so verbose :)
Hi Patrice,
Could you please post the table structure (as a CREATE TABLE statement,
including all constraints, properties, and indexes - though you may omit
irrelevant columns), some well-chosen rows of sample data (as INSERT
statemtents), and expected results? See www.aspfaq.com/5006 as well.
--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

Complex SQL select statement that works but...

I have a pretty complex SQL statement that looks like this:

SELECT aspnet_Employers.active, aspnet_Employers.accountexecutiveusername, aspnet_Employers.created, aspnet_Employers.Title AS Contact,
SUM(aspnet_Employers.EmployeeCount) AS [# Emps], COUNT(aspnet_Signups.account) AS [# Email Addresses], COUNT(aspnet_ContactMe.username)
AS [# Contact Me], COUNT(aspnet_AppsSubmitted.account) AS [# Apply Now]
FROM aspnet_Employers LEFT OUTER JOIN
aspnet_AppsSubmitted ON aspnet_Employers.UserName = aspnet_AppsSubmitted.account LEFT OUTER JOIN
aspnet_ContactMe ON aspnet_Employers.UserName = aspnet_ContactMe.username LEFT OUTER JOIN
aspnet_Signups ON aspnet_Employers.UserName = aspnet_Signups.account
GROUP BY aspnet_Employers.accountexecutiveusername, aspnet_Employers.created, aspnet_Employers.Title, aspnet_Employers.active

It does work the way i want it, but the problem is, on my Gridview when i change the Employers accounts "Active" status either way, it changes the username field from the username of the account, to "null".

Why does it do this?

What would i change to prevent this from happening?

Thanks!

Maybe it is related to Page.IsPostBack?|||Sorry, i should have mentioned this. This is on my aspx page, not in the code behind file.|||

Nevermind, i figured it out.

I was missing username boundfield for my update statement. When it's missing from the <fields> section, but it's in the update statement, it makes it null. Makes sense. Now i know.

Thanks

Complex Data Forms

I could really use some suggestions on the best way to get the results of a pretty complex aspx form. There are a whole bunch of search criteria. Most of the criteria are ranges of numbers such as a starting number and ending number but other criteria need to search text fields for keywords. Most fields are optional which means that many values will not be submitted. Not that there is a right and wrong way to do it, but what typically works out better for these things, an aspx procedure that puts together a highly complicated select statement or writing a highly complicated stored procedure? Either way I see a ton of if statements, or am I making this a lot harder then it needs it needs to be?

Thanks.In this type of case, I pass all the parameters to a stored procedure and then based on which ones are filled in, I create a dynamic SQL string that includes all the parameters in the where clause. Then execute the string. If you have a lot of parameters, you'd have to build a lot of statements to cover all the possibilitites. A dynamic SQL string is much easier to maintain, change, etc.|||Previously I've passed the params in as a single text column ecoded as XML and use OPENXML in the stored proc. It doesn't save on the IFs in the proc though :( Although depending upon your query (i.e is it ANDs and ORs on the params?) you can create a "criteria table" from the XML and simply join on the criteria data.