check previous row in the table

Hi...

Is there any way to check previous row in SQL Query?

I have a table with these column :
Name1
Name2
Audit_Time (datetime)
Changes

I want to delete record from database in which the Audit_time is <'01/05/2004'.
However before deletion, I want to check, if the Changes value is 'OLD' And the previous value is 'NEW', I will check the Audit_time of the NEW instead of OLD.

Table :
Row Name1 Name2 Audit_Time(mm/dd/yyyy) Changes
1 ABCD EFGH '01/01/2004' ADD
2 ABCD EFGHIJ '01/04/2004' NEW
3 ABCD EFGH '01/04/2004' OLD
4 Klarinda Rahmat '02/08/2004' NEW
5 Klarinda Rahmat '01/04/2004' OLD

In this case, I want to delete row 1,2,3 Where the audit_time are < '01/05/2004'.
Row 5 the audit_time also < '01/05/2004', however the changes='OLD' and the previous value changes='NEW', so I will check the Audit_Time of row 4 which is not < '01/05/2004'.
So I can't delete row5.

Is there any way to check previous row or the row before a specific row in SQL.
Any suggestion is welcomed.
Thank you in advanced.You can use a Cursor in a Stored Procedure. It would be very complex though...|||Check out the EXISTS keyword (assuming SQL server)...sql

Check if exist

Hi guys help please..is there a function in MS SQL that check if a particular value exist in a row and would return a boolean value base from what found, Return True if it found something and False if it does not found one. I've try the EXISTS function but I cant get the rigth syntax..Any help will be greatly appreciated!

OR Maybe you can help me directly with my problem. I want to check first in my Table 1 with 3 columns if value X exists in column 1 and if X exists UPDATE that column with value Y and if value X does not exists INSERT something in the Table 1. Any suggestion or Comments will be greatly appreciated!Hi

Post what you've got for your exists syntax. It should merely require some tweaking.|||Here it is.
EXISTS(select Sales_Date from CFREE_Sales where Sales_Date = '8/31/2007 12:00:00 AM')|||Try:
IF EXISTS(select Sales_Date from CFREE_Sales where Sales_Date = '20070831') BEGIN
PRINT 'It exists'
END
ELSE BEGIN
PRINT 'It does not exist'
END What is the result|||Yah...Thats what I need..Thanks a lot!|||An alternative to if exists (select * from #t1 where c1='x') begin
update #t1 set c2=c2+100 where c1='x'
end
else begin
insert into #t1 values ('x',100)
endisupdate #t1 set c2=c2+100 where c1='x'
if @.@.rowcount=0 begin
insert into #t1 values ('x',100)
end|||update #t1 set c2=c2+100 where c1='x'
if @.@.rowcount=0 begin
insert into #t1 values ('x',100)
endbingo! :beer:

CHECK DOUBLE RECORD

hi,
I've problem to check row which having duplicate value
for fieldA and fieldC.
Eg.
rec 1
fieldA fieldB fieldC
50000A 123456 A
rec 2
50000A 654321 A
Thanks.
see following example:
create table test(c1 varchar(10), c2 varchar(10), c3 char(1))
insert into test values('50000A','123456','A')
insert into test values('50000A','654321','A')
insert into test values('50000X','654321','A')
query:
select a.*
from test a join (Select c1,c3 from test group by c1,c3 having count(*) >
1) b
on a.c1 = b.c1 and a.c3=b.c3
Vishal Parkar
vgparkar@.yahoo.co.in | vgparkar@.hotmail.com

Check constraints

Hi,
If I have three columns called 'userid' 'event' and 'result' and only allow
one result row per event and userID. I.e. each user can only have one result
per event.
How would a constraint expression look like for this ?
Nicalter table <yourtable> add constraint uk_<yourtable>_event_userid unique
( event, userid )
That will only allow one row to have the same event and userid.
Tony.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Niclas" <lindblom_niclas@.hotmail.com> wrote in message
news:edSyoJV3FHA.3292@.tk2msftngp13.phx.gbl...
> Hi,
> If I have three columns called 'userid' 'event' and 'result' and only
> allow one result row per event and userID. I.e. each user can only have
> one result per event.
> How would a constraint expression look like for this ?
> Nic
>

Check Constraint UDF: Update vs. Insert

I am using a check constraint on one of my tables. It appears to me
that the constraint processes an insert AFTER the row is inserted on
an insert, but BEFORE the data is updated on an update. here's my
example:
Table:
CREATE TABLE [test1] (
[test] [int] NOT NULL )
Function:
CREATE FUNCTION dbo.fn_RI_Test (@.test int)
RETURNS INT
AS
BEGIN
declare @.cnt int
select @.cnt = count(*) from test1 where test=@.test
return @.cnt
END
here's my constraint added to the table test1:
alter table test1 add constraint ck_test1 check
(dbo.fn_RI_test([test])=1)
Now, if I insert new rows:
insert into test1 values (1) - works fine
insert into test1 values (2) - works fine
insert into test1 values (1) - error:
Server: Msg 547, Level 16, State 1, Line 1
INSERT statement conflicted with COLUMN CHECK constraint 'ck_test1'.
The conflict occurred in database 'JHRA_Test', table 'test1', column
'test'.
The statement has been terminated.
BUT, if I perform an update:
update test1 set test=1 where test=2
IT WORKS and now there are 2 rows with test=1
Am I crazy? Is there any way for me to know in the function whether
this is an update or an insert? Any other options? BTW, I don't need
to make a single column unique in this manner, my needs are much more
complex. this is just an illustration which demonstrates my issue.
Thanks!rich,
Can you tell us more about your needs?
This is a known behavior with a udf referencing same table in a constraint
and It has been fixed in 2005 version.
AMB
"rich" wrote:

> I am using a check constraint on one of my tables. It appears to me
> that the constraint processes an insert AFTER the row is inserted on
> an insert, but BEFORE the data is updated on an update. here's my
> example:
> Table:
> CREATE TABLE [test1] (
> [test] [int] NOT NULL )
> Function:
> CREATE FUNCTION dbo.fn_RI_Test (@.test int)
> RETURNS INT
> AS
> BEGIN
> declare @.cnt int
> select @.cnt = count(*) from test1 where test=@.test
> return @.cnt
> END
> here's my constraint added to the table test1:
> alter table test1 add constraint ck_test1 check
> (dbo.fn_RI_test([test])=1)
> Now, if I insert new rows:
> insert into test1 values (1) - works fine
> insert into test1 values (2) - works fine
> insert into test1 values (1) - error:
> Server: Msg 547, Level 16, State 1, Line 1
> INSERT statement conflicted with COLUMN CHECK constraint 'ck_test1'.
> The conflict occurred in database 'JHRA_Test', table 'test1', column
> 'test'.
> The statement has been terminated.
> BUT, if I perform an update:
> update test1 set test=1 where test=2
> IT WORKS and now there are 2 rows with test=1
> Am I crazy? Is there any way for me to know in the function whether
> this is an update or an insert? Any other options? BTW, I don't need
> to make a single column unique in this manner, my needs are much more
> complex. this is just an illustration which demonstrates my issue.
> Thanks!
>|||Alejandro, thanks for the reply - I was trying to find some resource
to confirm or deny the workings so I appreciate it.
I will share the true source of my problem in case you, or others,
would like to weigh in on the best method.
Basically, all of our data tables have a delete_flag bit field.
At the same time, I am trying to disallow duplicate combination of 3
fields. (sounds like a primary key, huh?)
Each table has an Identity field as the primary key.
I can't create a unique index on the three field combo because I allow
logical deletes using the delete_flag field.
I tried including the delete_flag in a four field index, but then you
can only delete each unique combination once.
I need unlimited logically deleted rows where my three fields are not
necessarily unique,
but only one non-deleted one row for each unique combination.
make sense?
On Feb 22, 3:26 pm, Alejandro Mesa
<AlejandroM...@.discussions.microsoft.com> wrote:
> rich,
> Can you tell us more about your needs?
> This is a known behavior with a udf referencing same table in a constraint
> and It has been fixed in 2005 version.
> AMB
>
> "rich" wrote:
>
>
>
>
>
>
>
>
> - Show quoted text -|||rich wrote:
> Alejandro, thanks for the reply - I was trying to find some resource
> to confirm or deny the workings so I appreciate it.
> I will share the true source of my problem in case you, or others,
> would like to weigh in on the best method.
> Basically, all of our data tables have a delete_flag bit field.
> At the same time, I am trying to disallow duplicate combination of 3
> fields. (sounds like a primary key, huh?)
> Each table has an Identity field as the primary key.
> I can't create a unique index on the three field combo because I allow
> logical deletes using the delete_flag field.
> I tried including the delete_flag in a four field index, but then you
> can only delete each unique combination once.
> I need unlimited logically deleted rows where my three fields are not
> necessarily unique,
> but only one non-deleted one row for each unique combination.
> make sense?
The solution(s) to the problem "unique constraint with multiple NULLs"
also applies to your case, only in your case the delete flag acts as a
NULL.
So for example, something like the script below could be a solution:
ALTER TABLE YourTable
ADD ExtraColumn AS CASE WHEN delete_flag = 1 THEN IdentityColumn END
ALTER TABLE YourTable
ADD CONSTRAINT YourUnique UNIQUE (UniqueColumn, ExtraColumn)
HTH,
Gert-Jan|||Gert-Jan, thanks for the tip!
I get it and it makes sense.
I also just tried a trigger (based on the above "test1" table) and it
works too.
Not sure what I'd rather do - add a new trigger to a bunch of tables
or a column and constraint.
here's the trigger in case anyone is interested:
CREATE TRIGGER dbo.tr_Test
ON [dbo].[test1]
FOR INSERT, UPDATE
AS
declare @.cnt int
BEGIN
select @.cnt=count(*) from test1 where test in (select test from
inserted)
if @.cnt>1 rollback tran --it's always 1 because the newly inserted or
updated row is included in the test1 table
END
Thanks for everyone's help!
On Feb 22, 4:01 pm, Gert-Jan Strik <s...@.toomuchspamalready.nl> wrote:
> rich wrote:
>
>
>
>
>
> The solution(s) to the problem "unique constraint with multiple NULLs"
> also applies to your case, only in your case the delete flag acts as a
> NULL.
> So for example, something like the script below could be a solution:
> ALTER TABLE YourTable
> ADD ExtraColumn AS CASE WHEN delete_flag = 1 THEN IdentityColumn END
> ALTER TABLE YourTable
> ADD CONSTRAINT YourUnique UNIQUE (UniqueColumn, ExtraColumn)
> HTH,
> Gert-Jan- Hide quoted text -
> - Show quoted text -|||On Feb 22, 2:26 pm, Alejandro Mesa
<AlejandroM...@.discussions.microsoft.com> wrote:

> This is a known behavior with a udf referencing same table in aconstraint
> and It has been fixed in 2005 version.
> AMB
>
I'm having trouble with this as well. One of our tables stores SSN
which should be unique but may also be null if the SSN isn't available
when entering the person's information. So I put a check constraint on
my column using a UDF that checks to see if any records already exist
with that SSN. I'm getting errors inserting data, because it inserts
the record before the check constraint fires.
This apparently hasn't been fixed in 2005, because that is what
version of SQL server I'm using. and my database is at that
compatibility level.
Is there a way to do this with a check constraint or do I have to use
a trigger?
Thanks.
-- Rayne|||Well, for one thing, using a constraint will avoid unnecessary page
splits, because it will prevent duplicate rows. A trigger will roll back
duplicate rows.
Also, I think that in this case, writing a trigger is more error prone
than defining the extra column and unique constraint.
Of course, the unique index will use slightly more space. But then
again, you also benefit from the fact that there now is a highly
selective index on the natural key.
So in this case, IMO the constraint wins from the trigger hands down...
Gert-Jan
rich wrote:[vbcol=seagreen]
> Gert-Jan, thanks for the tip!
> I get it and it makes sense.
> I also just tried a trigger (based on the above "test1" table) and it
> works too.
> Not sure what I'd rather do - add a new trigger to a bunch of tables
> or a column and constraint.
> here's the trigger in case anyone is interested:
> CREATE TRIGGER dbo.tr_Test
> ON [dbo].[test1]
> FOR INSERT, UPDATE
> AS
> declare @.cnt int
> BEGIN
> select @.cnt=count(*) from test1 where test in (select test from
> inserted)
> if @.cnt>1 rollback tran --it's always 1 because the newly inserted
or
> updated row is included in the test1 table
> END
> Thanks for everyone's help!
> On Feb 22, 4:01 pm, Gert-Jan Strik <s...@.toomuchspamalready.nl> wrote:|||Thanks again Gert-Jan
Rayne, you may or may not have seen the same issue.
You state
"I'm getting errors inserting data, because it inserts
the record before the check constraint fires. "
but it does that in SQL 2000 as well and thus your comparison in the
constraint has to be >1 as opposed to >0.
In other words, you have to assume that the newly inserted row is
included in the result query.
The problem I was having is that the above is NOT TRUE for updates. So
perhaps you could update your constraint and test it for inserts and
updates to see if it works as expected.
On Feb 22, 6:50 pm, Gert-Jan Strik <s...@.toomuchspamalready.nl> wrote:
> Well, for one thing, using a constraint will avoid unnecessary page
> splits, because it will prevent duplicate rows. A trigger will roll back
> duplicate rows.
> Also, I think that in this case, writing a trigger is more error prone
> than defining the extra column and unique constraint.
> Of course, the unique index will use slightly more space. But then
> again, you also benefit from the fact that there now is a highly
> selective index on the natural key.
> So in this case, IMO the constraint wins from the trigger hands down...
> Gert-Jan
>
> rich wrote:
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> - Show quoted text -|||On Feb 23, 9:24 am, "rich" <rwal...@.integratec.biz> wrote:
> Rayne, you may or may not have seen the same issue.
> You state
> "I'm getting errors inserting data, because it inserts
> the record before the check constraint fires. "
> but it does that in SQL 2000 as well and thus your comparison in the
> constraint has to be >1 as opposed to >0.
> In other words, you have to assume that the newly inserted row is
> included in the result query.
I realized this after the fact. My constraint was checking only to see
if a record already existed with that field value...not counting how
many. So when it did the check, of course it existed since it had
inserted the record first.
I created a trigger for insert, update that checks the count instead
of just existance and it's working correctly now.

Check Constraint UDF: Update vs. Insert

I am using a check constraint on one of my tables. It appears to me
that the constraint processes an insert AFTER the row is inserted on
an insert, but BEFORE the data is updated on an update. here's my
example:
Table:
CREATE TABLE [test1] (
[test] [int] NOT NULL )
Function:
CREATE FUNCTION dbo.fn_RI_Test (@.test int)
RETURNS INT
AS
BEGIN
declare @.cnt int
select @.cnt = count(*) from test1 where test=@.test
return @.cnt
END
here's my constraint added to the table test1:
alter table test1 add constraint ck_test1 check
(dbo.fn_RI_test([test])=1)
Now, if I insert new rows:
insert into test1 values (1) - works fine
insert into test1 values (2) - works fine
insert into test1 values (1) - error:
Server: Msg 547, Level 16, State 1, Line 1
INSERT statement conflicted with COLUMN CHECK constraint 'ck_test1'.
The conflict occurred in database 'JHRA_Test', table 'test1', column
'test'.
The statement has been terminated.
BUT, if I perform an update:
update test1 set test=1 where test=2
IT WORKS and now there are 2 rows with test=1
Am I crazy? Is there any way for me to know in the function whether
this is an update or an insert? Any other options? BTW, I don't need
to make a single column unique in this manner, my needs are much more
complex. this is just an illustration which demonstrates my issue.
Thanks!
rich,
Can you tell us more about your needs?
This is a known behavior with a udf referencing same table in a constraint
and It has been fixed in 2005 version.
AMB
"rich" wrote:

> I am using a check constraint on one of my tables. It appears to me
> that the constraint processes an insert AFTER the row is inserted on
> an insert, but BEFORE the data is updated on an update. here's my
> example:
> Table:
> CREATE TABLE [test1] (
> [test] [int] NOT NULL )
> Function:
> CREATE FUNCTION dbo.fn_RI_Test (@.test int)
> RETURNS INT
> AS
> BEGIN
> declare @.cnt int
> select @.cnt = count(*) from test1 where test=@.test
> return @.cnt
> END
> here's my constraint added to the table test1:
> alter table test1 add constraint ck_test1 check
> (dbo.fn_RI_test([test])=1)
> Now, if I insert new rows:
> insert into test1 values (1) - works fine
> insert into test1 values (2) - works fine
> insert into test1 values (1) - error:
> Server: Msg 547, Level 16, State 1, Line 1
> INSERT statement conflicted with COLUMN CHECK constraint 'ck_test1'.
> The conflict occurred in database 'JHRA_Test', table 'test1', column
> 'test'.
> The statement has been terminated.
> BUT, if I perform an update:
> update test1 set test=1 where test=2
> IT WORKS and now there are 2 rows with test=1
> Am I crazy? Is there any way for me to know in the function whether
> this is an update or an insert? Any other options? BTW, I don't need
> to make a single column unique in this manner, my needs are much more
> complex. this is just an illustration which demonstrates my issue.
> Thanks!
>
|||Alejandro, thanks for the reply - I was trying to find some resource
to confirm or deny the workings so I appreciate it.
I will share the true source of my problem in case you, or others,
would like to weigh in on the best method.
Basically, all of our data tables have a delete_flag bit field.
At the same time, I am trying to disallow duplicate combination of 3
fields. (sounds like a primary key, huh?)
Each table has an Identity field as the primary key.
I can't create a unique index on the three field combo because I allow
logical deletes using the delete_flag field.
I tried including the delete_flag in a four field index, but then you
can only delete each unique combination once.
I need unlimited logically deleted rows where my three fields are not
necessarily unique,
but only one non-deleted one row for each unique combination.
make sense?
On Feb 22, 3:26 pm, Alejandro Mesa
<AlejandroM...@.discussions.microsoft.com> wrote:
> rich,
> Can you tell us more about your needs?
> This is a known behavior with a udf referencing same table in a constraint
> and It has been fixed in 2005 version.
> AMB
>
> "rich" wrote:
>
>
>
>
> - Show quoted text -
|||Gert-Jan, thanks for the tip!
I get it and it makes sense.
I also just tried a trigger (based on the above "test1" table) and it
works too.
Not sure what I'd rather do - add a new trigger to a bunch of tables
or a column and constraint.
here's the trigger in case anyone is interested:
CREATE TRIGGER dbo.tr_Test
ON [dbo].[test1]
FOR INSERT, UPDATE
AS
declare @.cnt int
BEGIN
select @.cnt=count(*) from test1 where test in (select test from
inserted)
if @.cnt>1 rollback tran --it's always 1 because the newly inserted or
updated row is included in the test1 table
END
Thanks for everyone's help!
On Feb 22, 4:01 pm, Gert-Jan Strik <s...@.toomuchspamalready.nl> wrote:
> rich wrote:
>
>
>
> The solution(s) to the problem "unique constraint with multiple NULLs"
> also applies to your case, only in your case the delete flag acts as a
> NULL.
> So for example, something like the script below could be a solution:
> ALTER TABLE YourTable
> ADD ExtraColumn AS CASE WHEN delete_flag = 1 THEN IdentityColumn END
> ALTER TABLE YourTable
> ADD CONSTRAINT YourUnique UNIQUE (UniqueColumn, ExtraColumn)
> HTH,
> Gert-Jan- Hide quoted text -
> - Show quoted text -
|||On Feb 22, 2:26 pm, Alejandro Mesa
<AlejandroM...@.discussions.microsoft.com> wrote:

> This is a known behavior with a udf referencing same table in aconstraint
> and It has been fixed in 2005 version.
> AMB
>
I'm having trouble with this as well. One of our tables stores SSN
which should be unique but may also be null if the SSN isn't available
when entering the person's information. So I put a check constraint on
my column using a UDF that checks to see if any records already exist
with that SSN. I'm getting errors inserting data, because it inserts
the record before the check constraint fires.
This apparently hasn't been fixed in 2005, because that is what
version of SQL server I'm using. and my database is at that
compatibility level.
Is there a way to do this with a check constraint or do I have to use
a trigger?
Thanks.
-- Rayne
|||Thanks again Gert-Jan
Rayne, you may or may not have seen the same issue.
You state
"I'm getting errors inserting data, because it inserts
the record before the check constraint fires. "
but it does that in SQL 2000 as well and thus your comparison in the
constraint has to be >1 as opposed to >0.
In other words, you have to assume that the newly inserted row is
included in the result query.
The problem I was having is that the above is NOT TRUE for updates. So
perhaps you could update your constraint and test it for inserts and
updates to see if it works as expected.
On Feb 22, 6:50 pm, Gert-Jan Strik <s...@.toomuchspamalready.nl> wrote:
> Well, for one thing, using a constraint will avoid unnecessary page
> splits, because it will prevent duplicate rows. A trigger will roll back
> duplicate rows.
> Also, I think that in this case, writing a trigger is more error prone
> than defining the extra column and unique constraint.
> Of course, the unique index will use slightly more space. But then
> again, you also benefit from the fact that there now is a highly
> selective index on the natural key.
> So in this case, IMO the constraint wins from the trigger hands down...
> Gert-Jan
>
> rich wrote:
>
>
>
>
>
>
>
>
>
> - Show quoted text -
|||On Feb 23, 9:24 am, "rich" <rwal...@.integratec.biz> wrote:
> Rayne, you may or may not have seen the same issue.
> You state
> "I'm getting errors inserting data, because it inserts
> the record before the check constraint fires. "
> but it does that in SQL 2000 as well and thus your comparison in the
> constraint has to be >1 as opposed to >0.
> In other words, you have to assume that the newly inserted row is
> included in the result query.
I realized this after the fact. My constraint was checking only to see
if a record already existed with that field value...not counting how
many. So when it did the check, of course it existed since it had
inserted the record first.
I created a trigger for insert, update that checks the count instead
of just existance and it's working correctly now.

Check Constraint UDF: Update vs. Insert

I am using a check constraint on one of my tables. It appears to me
that the constraint processes an insert AFTER the row is inserted on
an insert, but BEFORE the data is updated on an update. here's my
example:
Table:
CREATE TABLE [test1] (
[test] [int] NOT NULL )
Function:
CREATE FUNCTION dbo.fn_RI_Test (@.test int)
RETURNS INT
AS
BEGIN
declare @.cnt int
select @.cnt = count(*) from test1 where test=@.test
return @.cnt
END
here's my constraint added to the table test1:
alter table test1 add constraint ck_test1 check
(dbo.fn_RI_test([test])=1)
Now, if I insert new rows:
insert into test1 values (1) - works fine
insert into test1 values (2) - works fine
insert into test1 values (1) - error:
Server: Msg 547, Level 16, State 1, Line 1
INSERT statement conflicted with COLUMN CHECK constraint 'ck_test1'.
The conflict occurred in database 'JHRA_Test', table 'test1', column
'test'.
The statement has been terminated.
BUT, if I perform an update:
update test1 set test=1 where test=2
IT WORKS and now there are 2 rows with test=1
Am I crazy? Is there any way for me to know in the function whether
this is an update or an insert? Any other options? BTW, I don't need
to make a single column unique in this manner, my needs are much more
complex. this is just an illustration which demonstrates my issue.
Thanks!rich,
Can you tell us more about your needs?
This is a known behavior with a udf referencing same table in a constraint
and It has been fixed in 2005 version.
AMB
"rich" wrote:
> I am using a check constraint on one of my tables. It appears to me
> that the constraint processes an insert AFTER the row is inserted on
> an insert, but BEFORE the data is updated on an update. here's my
> example:
> Table:
> CREATE TABLE [test1] (
> [test] [int] NOT NULL )
> Function:
> CREATE FUNCTION dbo.fn_RI_Test (@.test int)
> RETURNS INT
> AS
> BEGIN
> declare @.cnt int
> select @.cnt = count(*) from test1 where test=@.test
> return @.cnt
> END
> here's my constraint added to the table test1:
> alter table test1 add constraint ck_test1 check
> (dbo.fn_RI_test([test])=1)
> Now, if I insert new rows:
> insert into test1 values (1) - works fine
> insert into test1 values (2) - works fine
> insert into test1 values (1) - error:
> Server: Msg 547, Level 16, State 1, Line 1
> INSERT statement conflicted with COLUMN CHECK constraint 'ck_test1'.
> The conflict occurred in database 'JHRA_Test', table 'test1', column
> 'test'.
> The statement has been terminated.
> BUT, if I perform an update:
> update test1 set test=1 where test=2
> IT WORKS and now there are 2 rows with test=1
> Am I crazy? Is there any way for me to know in the function whether
> this is an update or an insert? Any other options? BTW, I don't need
> to make a single column unique in this manner, my needs are much more
> complex. this is just an illustration which demonstrates my issue.
> Thanks!
>|||Alejandro, thanks for the reply - I was trying to find some resource
to confirm or deny the workings so I appreciate it.
I will share the true source of my problem in case you, or others,
would like to weigh in on the best method.
Basically, all of our data tables have a delete_flag bit field.
At the same time, I am trying to disallow duplicate combination of 3
fields. (sounds like a primary key, huh?)
Each table has an Identity field as the primary key.
I can't create a unique index on the three field combo because I allow
logical deletes using the delete_flag field.
I tried including the delete_flag in a four field index, but then you
can only delete each unique combination once.
I need unlimited logically deleted rows where my three fields are not
necessarily unique,
but only one non-deleted one row for each unique combination.
make sense?
On Feb 22, 3:26 pm, Alejandro Mesa
<AlejandroM...@.discussions.microsoft.com> wrote:
> rich,
> Can you tell us more about your needs?
> This is a known behavior with a udf referencing same table in a constraint
> and It has been fixed in 2005 version.
> AMB
>
> "rich" wrote:
> > I am using a check constraint on one of my tables. It appears to me
> > that the constraint processes an insert AFTER the row is inserted on
> > an insert, but BEFORE the data is updated on an update. here's my
> > example:
> > Table:
> > CREATE TABLE [test1] (
> > [test] [int] NOT NULL )
> > Function:
> > CREATE FUNCTION dbo.fn_RI_Test (@.test int)
> > RETURNS INT
> > AS
> > BEGIN
> > declare @.cnt int
> > select @.cnt = count(*) from test1 where test=@.test
> > return @.cnt
> > END
> > here's my constraint added to the table test1:
> > alter table test1 add constraint ck_test1 check
> > (dbo.fn_RI_test([test])=1)
> > Now, if I insert new rows:
> > insert into test1 values (1) - works fine
> > insert into test1 values (2) - works fine
> > insert into test1 values (1) - error:
> > Server: Msg 547, Level 16, State 1, Line 1
> > INSERT statement conflicted with COLUMN CHECK constraint 'ck_test1'.
> > The conflict occurred in database 'JHRA_Test', table 'test1', column
> > 'test'.
> > The statement has been terminated.
> > BUT, if I perform an update:
> > update test1 set test=1 where test=2
> > IT WORKS and now there are 2 rows with test=1
> > Am I crazy? Is there any way for me to know in the function whether
> > this is an update or an insert? Any other options? BTW, I don't need
> > to make a single column unique in this manner, my needs are much more
> > complex. this is just an illustration which demonstrates my issue.
> > Thanks!- Hide quoted text -
> - Show quoted text -|||rich wrote:
> Alejandro, thanks for the reply - I was trying to find some resource
> to confirm or deny the workings so I appreciate it.
> I will share the true source of my problem in case you, or others,
> would like to weigh in on the best method.
> Basically, all of our data tables have a delete_flag bit field.
> At the same time, I am trying to disallow duplicate combination of 3
> fields. (sounds like a primary key, huh?)
> Each table has an Identity field as the primary key.
> I can't create a unique index on the three field combo because I allow
> logical deletes using the delete_flag field.
> I tried including the delete_flag in a four field index, but then you
> can only delete each unique combination once.
> I need unlimited logically deleted rows where my three fields are not
> necessarily unique,
> but only one non-deleted one row for each unique combination.
> make sense?
The solution(s) to the problem "unique constraint with multiple NULLs"
also applies to your case, only in your case the delete flag acts as a
NULL.
So for example, something like the script below could be a solution:
ALTER TABLE YourTable
ADD ExtraColumn AS CASE WHEN delete_flag = 1 THEN IdentityColumn END
ALTER TABLE YourTable
ADD CONSTRAINT YourUnique UNIQUE (UniqueColumn, ExtraColumn)
HTH,
Gert-Jan|||Gert-Jan, thanks for the tip!
I get it and it makes sense.
I also just tried a trigger (based on the above "test1" table) and it
works too.
Not sure what I'd rather do - add a new trigger to a bunch of tables
or a column and constraint.
here's the trigger in case anyone is interested:
CREATE TRIGGER dbo.tr_Test
ON [dbo].[test1]
FOR INSERT, UPDATE
AS
declare @.cnt int
BEGIN
select @.cnt=count(*) from test1 where test in (select test from
inserted)
if @.cnt>1 rollback tran --it's always 1 because the newly inserted or
updated row is included in the test1 table
END
Thanks for everyone's help!
On Feb 22, 4:01 pm, Gert-Jan Strik <s...@.toomuchspamalready.nl> wrote:
> rich wrote:
> > Alejandro, thanks for the reply - I was trying to find some resource
> > to confirm or deny the workings so I appreciate it.
> > I will share the true source of my problem in case you, or others,
> > would like to weigh in on the best method.
> > Basically, all of our data tables have a delete_flag bit field.
> > At the same time, I am trying to disallow duplicate combination of 3
> > fields. (sounds like a primary key, huh?)
> > Each table has an Identity field as the primary key.
> > I can't create a unique index on the three field combo because I allow
> > logical deletes using the delete_flag field.
> > I tried including the delete_flag in a four field index, but then you
> > can only delete each unique combination once.
> > I need unlimited logically deleted rows where my three fields are not
> > necessarily unique,
> > but only one non-deleted one row for each unique combination.
> > make sense?
> The solution(s) to the problem "unique constraint with multiple NULLs"
> also applies to your case, only in your case the delete flag acts as a
> NULL.
> So for example, something like the script below could be a solution:
> ALTER TABLE YourTable
> ADD ExtraColumn AS CASE WHEN delete_flag = 1 THEN IdentityColumn END
> ALTER TABLE YourTable
> ADD CONSTRAINT YourUnique UNIQUE (UniqueColumn, ExtraColumn)
> HTH,
> Gert-Jan- Hide quoted text -
> - Show quoted text -|||On Feb 22, 2:26 pm, Alejandro Mesa
<AlejandroM...@.discussions.microsoft.com> wrote:
> This is a known behavior with a udf referencing same table in aconstraint
> and It has been fixed in 2005 version.
> AMB
>
I'm having trouble with this as well. One of our tables stores SSN
which should be unique but may also be null if the SSN isn't available
when entering the person's information. So I put a check constraint on
my column using a UDF that checks to see if any records already exist
with that SSN. I'm getting errors inserting data, because it inserts
the record before the check constraint fires.
This apparently hasn't been fixed in 2005, because that is what
version of SQL server I'm using. and my database is at that
compatibility level.
Is there a way to do this with a check constraint or do I have to use
a trigger?
Thanks.
-- Rayne|||Well, for one thing, using a constraint will avoid unnecessary page
splits, because it will prevent duplicate rows. A trigger will roll back
duplicate rows.
Also, I think that in this case, writing a trigger is more error prone
than defining the extra column and unique constraint.
Of course, the unique index will use slightly more space. But then
again, you also benefit from the fact that there now is a highly
selective index on the natural key.
So in this case, IMO the constraint wins from the trigger hands down...
Gert-Jan
rich wrote:
> Gert-Jan, thanks for the tip!
> I get it and it makes sense.
> I also just tried a trigger (based on the above "test1" table) and it
> works too.
> Not sure what I'd rather do - add a new trigger to a bunch of tables
> or a column and constraint.
> here's the trigger in case anyone is interested:
> CREATE TRIGGER dbo.tr_Test
> ON [dbo].[test1]
> FOR INSERT, UPDATE
> AS
> declare @.cnt int
> BEGIN
> select @.cnt=count(*) from test1 where test in (select test from
> inserted)
> if @.cnt>1 rollback tran --it's always 1 because the newly inserted or
> updated row is included in the test1 table
> END
> Thanks for everyone's help!
> On Feb 22, 4:01 pm, Gert-Jan Strik <s...@.toomuchspamalready.nl> wrote:
> > rich wrote:
> >
> > > Alejandro, thanks for the reply - I was trying to find some resource
> > > to confirm or deny the workings so I appreciate it.
> >
> > > I will share the true source of my problem in case you, or others,
> > > would like to weigh in on the best method.
> >
> > > Basically, all of our data tables have a delete_flag bit field.
> > > At the same time, I am trying to disallow duplicate combination of 3
> > > fields. (sounds like a primary key, huh?)
> > > Each table has an Identity field as the primary key.
> > > I can't create a unique index on the three field combo because I allow
> > > logical deletes using the delete_flag field.
> > > I tried including the delete_flag in a four field index, but then you
> > > can only delete each unique combination once.
> >
> > > I need unlimited logically deleted rows where my three fields are not
> > > necessarily unique,
> > > but only one non-deleted one row for each unique combination.
> >
> > > make sense?
> >
> > The solution(s) to the problem "unique constraint with multiple NULLs"
> > also applies to your case, only in your case the delete flag acts as a
> > NULL.
> >
> > So for example, something like the script below could be a solution:
> >
> > ALTER TABLE YourTable
> > ADD ExtraColumn AS CASE WHEN delete_flag = 1 THEN IdentityColumn END
> >
> > ALTER TABLE YourTable
> > ADD CONSTRAINT YourUnique UNIQUE (UniqueColumn, ExtraColumn)
> >
> > HTH,
> > Gert-Jan- Hide quoted text -
> >
> > - Show quoted text -|||Thanks again Gert-Jan
Rayne, you may or may not have seen the same issue.
You state
"I'm getting errors inserting data, because it inserts
the record before the check constraint fires. "
but it does that in SQL 2000 as well and thus your comparison in the
constraint has to be >1 as opposed to >0.
In other words, you have to assume that the newly inserted row is
included in the result query.
The problem I was having is that the above is NOT TRUE for updates. So
perhaps you could update your constraint and test it for inserts and
updates to see if it works as expected.
On Feb 22, 6:50 pm, Gert-Jan Strik <s...@.toomuchspamalready.nl> wrote:
> Well, for one thing, using a constraint will avoid unnecessary page
> splits, because it will prevent duplicate rows. A trigger will roll back
> duplicate rows.
> Also, I think that in this case, writing a trigger is more error prone
> than defining the extra column and unique constraint.
> Of course, the unique index will use slightly more space. But then
> again, you also benefit from the fact that there now is a highly
> selective index on the natural key.
> So in this case, IMO the constraint wins from the trigger hands down...
> Gert-Jan
>
> rich wrote:
> > Gert-Jan, thanks for the tip!
> > I get it and it makes sense.
> > I also just tried a trigger (based on the above "test1" table) and it
> > works too.
> > Not sure what I'd rather do - add a new trigger to a bunch of tables
> > or a column and constraint.
> > here's the trigger in case anyone is interested:
> > CREATE TRIGGER dbo.tr_Test
> > ON [dbo].[test1]
> > FOR INSERT, UPDATE
> > AS
> > declare @.cnt int
> > BEGIN
> > select @.cnt=count(*) from test1 where test in (select test from
> > inserted)
> > if @.cnt>1 rollback tran --it's always 1 because the newly inserted or
> > updated row is included in the test1 table
> > END
> > Thanks for everyone's help!
> > On Feb 22, 4:01 pm, Gert-Jan Strik <s...@.toomuchspamalready.nl> wrote:
> > > rich wrote:
> > > > Alejandro, thanks for the reply - I was trying to find some resource
> > > > to confirm or deny the workings so I appreciate it.
> > > > I will share the true source of my problem in case you, or others,
> > > > would like to weigh in on the best method.
> > > > Basically, all of our data tables have a delete_flag bit field.
> > > > At the same time, I am trying to disallow duplicate combination of 3
> > > > fields. (sounds like a primary key, huh?)
> > > > Each table has an Identity field as the primary key.
> > > > I can't create a unique index on the three field combo because I allow
> > > > logical deletes using the delete_flag field.
> > > > I tried including the delete_flag in a four field index, but then you
> > > > can only delete each unique combination once.
> > > > I need unlimited logically deleted rows where my three fields are not
> > > > necessarily unique,
> > > > but only one non-deleted one row for each unique combination.
> > > > make sense?
> > > The solution(s) to the problem "unique constraint with multiple NULLs"
> > > also applies to your case, only in your case the delete flag acts as a
> > > NULL.
> > > So for example, something like the script below could be a solution:
> > > ALTER TABLE YourTable
> > > ADD ExtraColumn AS CASE WHEN delete_flag = 1 THEN IdentityColumn END
> > > ALTER TABLE YourTable
> > > ADD CONSTRAINT YourUnique UNIQUE (UniqueColumn, ExtraColumn)
> > > HTH,
> > > Gert-Jan- Hide quoted text -
> > > - Show quoted text -- Hide quoted text -
> - Show quoted text -|||On Feb 23, 9:24 am, "rich" <rwal...@.integratec.biz> wrote:
> Rayne, you may or may not have seen the same issue.
> You state
> "I'm getting errors inserting data, because it inserts
> the record before the check constraint fires. "
> but it does that in SQL 2000 as well and thus your comparison in the
> constraint has to be >1 as opposed to >0.
> In other words, you have to assume that the newly inserted row is
> included in the result query.
I realized this after the fact. My constraint was checking only to see
if a record already existed with that field value...not counting how
many. So when it did the check, of course it existed since it had
inserted the record first.
I created a trigger for insert, update that checks the count instead
of just existance and it's working correctly now.

Charting question

I have a report where each page presents some data in tabular form, the
bottom row (numerically) representing a histogram; i.e., % of values falling
into the "bucket" corresponding to that column. Below this row I'd like to
have a bar chart that graphically displays this histogram with each bar
aligned with the column above. Although I've figured out how to constuct
the chart object there doesn't seem to be any way to actually get things to
line up properly.
The portion of the last row containing the data consists of 7 cells. There
are a couple of padding columns so the first data item is in column 3. What
I tried was to insert a row below (actually a footer row), then merge the 7
cells below the data along with the left adjacent pad cell. I included the
extra cell to allow space for the y axis labels.
So, I can get the chart to appear, but due to the formatting of the chart;
i.e., white space, things don't line up properly. I'm trying to duplicate
(as much as possible) a Crystal generated report in order to determine the
feasibility of using Reporting Services instead. I'm afraid this might be a
deal breaker -- so if anyone has any suggestions I'm anxious to hear back!
Thanks in advance for any help.
BillSo, anybody got any ideas on this one? I'd hoped to get some help before
this scrolls off the edge of the earth...
Bill
"Bill Cohagan" <bill@.teraXNOSPAMXquest.com> wrote in message
news:eDoS$jukEHA.1936@.TK2MSFTNGP12.phx.gbl...
>I have a report where each page presents some data in tabular form, the
> bottom row (numerically) representing a histogram; i.e., % of values
> falling
> into the "bucket" corresponding to that column. Below this row I'd like to
> have a bar chart that graphically displays this histogram with each bar
> aligned with the column above. Although I've figured out how to constuct
> the chart object there doesn't seem to be any way to actually get things
> to
> line up properly.
> The portion of the last row containing the data consists of 7 cells.
> There
> are a couple of padding columns so the first data item is in column 3.
> What
> I tried was to insert a row below (actually a footer row), then merge the
> 7
> cells below the data along with the left adjacent pad cell. I included the
> extra cell to allow space for the y axis labels.
> So, I can get the chart to appear, but due to the formatting of the chart;
> i.e., white space, things don't line up properly. I'm trying to duplicate
> (as much as possible) a Crystal generated report in order to determine the
> feasibility of using Reporting Services instead. I'm afraid this might be
> a
> deal breaker -- so if anyone has any suggestions I'm anxious to hear back!
> Thanks in advance for any help.
> Bill
>

Chart, how do I use format code for a label?

How do I refer to the actual value for the label inside my expression?
Say you have datetime data across the x-axis for the data. I get one row per
month in the dataset (every row is same date and time for each row/month). I
understand how I can format this, for instance "MMM" to get month name in
short format.
But if I want to do further or a bit more complex manipulation? In this
case, I want to show only first letter of the month. I fail to connect how
to put the actual data value inside my VB.NET expression (substring, left or
similar function in this case).
(I can retrieve the first letter of the month along with the data, as an
extra column, doing this in my stored procedure. If above is difficult, I'd
appreciate tips on how to refer to this column for the chart label. I.e., I
want to show some other column as the label, not the one used to derive the
actual value.)
TIA
Tibor Karaszi
SQL Server MVPExpressions for formatting labels are not directly supported in the current
release. You could do a bar chart and use an expression
(=Left(Format(Fields!OrderDate.Value, "MMM"), 1)) for the corresponding
category group. The sample report attached at the end of this post (which
runs against local Northwind database) demonstrates this.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OEwX0qaiEHA.2848@.TK2MSFTNGP10.phx.gbl...
> How do I refer to the actual value for the label inside my expression?
> Say you have datetime data across the x-axis for the data. I get one row
per
> month in the dataset (every row is same date and time for each row/month).
I
> understand how I can format this, for instance "MMM" to get month name in
> short format.
> But if I want to do further or a bit more complex manipulation? In this
> case, I want to show only first letter of the month. I fail to connect how
> to put the actual data value inside my VB.NET expression (substring, left
or
> similar function in this case).
> (I can retrieve the first letter of the month along with the data, as an
> extra column, doing this in my stored procedure. If above is difficult,
I'd
> appreciate tips on how to refer to this column for the chart label. I.e.,
I
> want to show some other column as the label, not the one used to derive
the
> actual value.)
> TIA
> Tibor Karaszi
> SQL Server MVP
>
+++++++++++++ Sample report +++++++++++++
<?xml version="1.0" encoding="utf-8"?>
<Report
xmlns="http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefini
tion"
xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<RightMargin>1in</RightMargin>
<Body>
<ReportItems>
<Chart Name="chart1">
<ThreeDProperties>
<Rotation>30</Rotation>
<Inclination>30</Inclination>
<Shading>Simple</Shading>
<WallThickness>50</WallThickness>
</ThreeDProperties>
<Style>
<BackgroundColor>White</BackgroundColor>
</Style>
<Legend>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
<Position>RightCenter</Position>
</Legend>
<Palette>Default</Palette>
<ChartData>
<ChartSeries>
<DataPoints>
<DataPoint>
<DataValues>
<DataValue>
<Value>=Sum(Fields!Freight.Value)</Value>
</DataValue>
</DataValues>
<DataLabel />
<Marker />
</DataPoint>
</DataPoints>
</ChartSeries>
</ChartData>
<CategoryAxis>
<Axis>
<Title />
<Style>
<FontSize>8pt</FontSize>
</Style>
<MajorGridLines>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</MajorGridLines>
<MinorGridLines>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</MinorGridLines>
<MajorTickMarks>Outside</MajorTickMarks>
<Min>0</Min>
<Visible>true</Visible>
</Axis>
</CategoryAxis>
<DataSetName>Northwind</DataSetName>
<PointWidth>0</PointWidth>
<Type>Bar</Type>
<Title />
<Width>5.25in</Width>
<CategoryGroupings>
<CategoryGrouping>
<DynamicCategories>
<Grouping Name="chart1_CategoryGroup1">
<GroupExpressions>
<GroupExpression>=Fields!OrderDate.Value</GroupExpression>
</GroupExpressions>
</Grouping>
<Label>=Left(Format(Fields!OrderDate.Value, "MMM"), 1) & "
[" & Format(Fields!OrderDate.Value, "MMM yyyy") & "]"</Label>
</DynamicCategories>
</CategoryGrouping>
</CategoryGroupings>
<Subtype>Plain</Subtype>
<PlotArea>
<Style>
<BackgroundColor>LightGrey</BackgroundColor>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</PlotArea>
<ValueAxis>
<Axis>
<Title />
<Style>
<Format>c</Format>
<FontSize>8pt</FontSize>
</Style>
<MajorGridLines>
<ShowGridLines>true</ShowGridLines>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</MajorGridLines>
<MinorGridLines>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</MinorGridLines>
<MajorTickMarks>Outside</MajorTickMarks>
<Min>0</Min>
<Margin>true</Margin>
<Visible>true</Visible>
<Scalar>true</Scalar>
</Axis>
</ValueAxis>
</Chart>
</ReportItems>
<Style />
<Height>3in</Height>
</Body>
<TopMargin>1in</TopMargin>
<DataSources>
<DataSource Name="Northwind">
<rd:DataSourceID>14b06457-afff-49a5-9624-2ecc74ef5643</rd:DataSourceID>
<ConnectionProperties>
<DataProvider>SQL</DataProvider>
<ConnectString>initial catalog=Northwind</ConnectString>
<IntegratedSecurity>true</IntegratedSecurity>
</ConnectionProperties>
</DataSource>
</DataSources>
<Width>6.25in</Width>
<DataSets>
<DataSet Name="Northwind">
<Fields>
<Field Name="OrderDate">
<DataField>OrderDate</DataField>
<rd:TypeName>System.DateTime</rd:TypeName>
</Field>
<Field Name="Freight">
<DataField>Freight</DataField>
<rd:TypeName>System.Decimal</rd:TypeName>
</Field>
</Fields>
<Query>
<DataSourceName>Northwind</DataSourceName>
<CommandText>SELECT TOP 10 OrderDate, Freight
FROM Orders
ORDER BY ShipCity</CommandText>
</Query>
</DataSet>
</DataSets>
<LeftMargin>1in</LeftMargin>
<rd:SnapToGrid>true</rd:SnapToGrid>
<rd:DrawGrid>true</rd:DrawGrid>
<rd:ReportID>b8405333-c29c-4c57-8ba3-7915ae7bf5eb</rd:ReportID>
<BottomMargin>1in</BottomMargin>
<Language>en-US</Language>
</Report>

Chart Filters

HI,
Does anybody know how to write a chart filter that will return only the last row in the record set and conversely return all rows except for the last in the data set (for a separate chart)
My Report consists of a dataset that has the total in the last row. I want to create a chart with only the total row and another chart on the same page using the same data set but excluding the total row.
Cheers
KevinIf there is anything special about the row it would be straight forward as a
chart filter and a table filter to separate the rows. If the only
distinguishing feature of the row is that it is the last row then I can not
think of a way in RS 2000 to do this. We are considering allowing
aggregates of aggregates, which would allow you to filter on the max
rownumber.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Kevin Wilson" <KevinWilson@.discussions.microsoft.com> wrote in message
news:A23DB1DA-C4EC-4789-A46A-C236B070F5A4@.microsoft.com...
> HI,
> Does anybody know how to write a chart filter that will return only the
last row in the record set and conversely return all rows except for the
last in the data set (for a separate chart)
> My Report consists of a dataset that has the total in the last row. I want
to create a chart with only the total row and another chart on the same
page using the same data set but excluding the total row.
> Cheers
> Kevin|||Jason,
There is nothing special about the row except that it's the last. However I've managed to created a chart filter that returns the last row by using the Bottom N operator.
expression operator value
=Fields!RH0_Product.Value BottomN =1
However do you know how to express a filter that defines where the rows is NOT = to the BottomN 1
If I can get this then I've solved my problem...
Much appreciated.
Kevin
"Jason Carlson [MSFT]" wrote:
> If there is anything special about the row it would be straight forward as a
> chart filter and a table filter to separate the rows. If the only
> distinguishing feature of the row is that it is the last row then I can not
> think of a way in RS 2000 to do this. We are considering allowing
> aggregates of aggregates, which would allow you to filter on the max
> rownumber.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Kevin Wilson" <KevinWilson@.discussions.microsoft.com> wrote in message
> news:A23DB1DA-C4EC-4789-A46A-C236B070F5A4@.microsoft.com...
> > HI,
> > Does anybody know how to write a chart filter that will return only the
> last row in the record set and conversely return all rows except for the
> last in the data set (for a separate chart)
> >
> > My Report consists of a dataset that has the total in the last row. I want
> to create a chart with only the total row and another chart on the same
> page using the same data set but excluding the total row.
> >
> > Cheers
> > Kevin
>
>|||Please see my response to your "NOT BottomN" thread started on 06/29.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Kevin Wilson" <KevinWilson@.discussions.microsoft.com> wrote in message
news:A6A1CBD8-F987-472C-B507-E0AA21FD3A4C@.microsoft.com...
> Jason,
> There is nothing special about the row except that it's the last. However
I've managed to created a chart filter that returns the last row by using
the Bottom N operator.
> expression operator value
> =Fields!RH0_Product.Value BottomN =1
> However do you know how to express a filter that defines where the rows
is NOT = to the BottomN 1
> If I can get this then I've solved my problem...
> Much appreciated.
> Kevin
>
> "Jason Carlson [MSFT]" wrote:
> > If there is anything special about the row it would be straight forward
as a
> > chart filter and a table filter to separate the rows. If the only
> > distinguishing feature of the row is that it is the last row then I can
not
> > think of a way in RS 2000 to do this. We are considering allowing
> > aggregates of aggregates, which would allow you to filter on the max
> > rownumber.
> >
> > --
> >
> > This posting is provided "AS IS" with no warranties, and confers no
rights.
> >
> >
> > "Kevin Wilson" <KevinWilson@.discussions.microsoft.com> wrote in message
> > news:A23DB1DA-C4EC-4789-A46A-C236B070F5A4@.microsoft.com...
> > > HI,
> > > Does anybody know how to write a chart filter that will return only
the
> > last row in the record set and conversely return all rows except for the
> > last in the data set (for a separate chart)
> > >
> > > My Report consists of a dataset that has the total in the last row. I
want
> > to create a chart with only the total row and another chart on the same
> > page using the same data set but excluding the total row.
> > >
> > > Cheers
> > > Kevin
> >
> >
> >

Changing values in a record

I have the problem of dynamically changing a field value in a row depending on the value of the same field in the previous row, assuming the table is sorted on that field.
:(
You may consider that field as a key of same sort as other values in the table do not help in selecting them uniquely.
Ex.
Before After
130 -> 130
130 -> 130A
140 -> 140
140 -> 140AIs the order of rows with "ties" arbitrary, or is there some hidden criteria that allows you to order them too? This is important, because it significantly changes the nature of the problem.

-PatP|||Is this a one-time data fix, or will this process need to be run regularly?

I assume, too, that data values such as 130B, 130C, etc... may also be required?

I have to say, it is rarely a good idea to create ID values this way (though I understand that sometimes the business model requires it), but it is also rare that a good application design requires stored data to be ordered in a specific manner, either logically or physically. Records should be independent from one-another.

I suspect that this is a case of either:
1) Poor application design.
2) A data-import issue.
3) A homework problem for a class.|||Yessss,
It is a problem of moving data from a legacy system (a whole lot of rubbish) into a new ERP. Business logics and data models are way different. Under this circumstances, a non-key field in the old system is now a key in the new model, from here the need of updating the values with a trailer character (or what have you). :)

Blindman, the values you mentioned are allowed, altough the repetition is often limited to two rows only. :o

Pat, there are no possible sorting orders because the other fields may vary 'randomly'.|||For a one-time shot, and given data that has no natural order beyond the field you are dealing with, a cursor may be a valid option.

Create a cursor that loops through the dataset order by your field, and that updates the field value if the prior field value had the same ID.|||If you don't have any other way to impose order on the rows, then a cursor is the only option that you've got left. Its messy and a "last resort", but it will get the job done!

-PatP