August 11, 2006 at 11:10 pm
Hi, I would appreciate your help on this:
I am trying to generate a new Field2 from Field1 so that e.g.
Field1 is
1
3
4
2
then Field2 is
<null>
2
1
-2
I think there is a simple way of doing this e.g. SELECT Field1-Field1~-1 AS Field2 FROM...
but none of the variations i've tried have worked.
August 12, 2006 at 4:37 pm
If the table has a uniqe value on which the result set is being ordered, you can do something like:
create table junktable (p_key int not null,
fielda int not null)
go
insert junktable (p_key, fielda) values (1,1)
insert junktable (p_key, fielda) values (2,3)
insert junktable (p_key, fielda) values (3,4)
insert junktable (p_key, fielda) values (4,2)
go
select
a.fielda, (a.fielda - b.fielda)
as
'Field2' from
dbo
.junktable a
left
join dbo.junktable b
on
b.p_key = (select max(p_key) from dbo.junktable b where b.p_key < a.p_key)
go
If there is no unique ordering, you will probably have to use a cursor to step through the rows sequentially
August 14, 2006 at 3:16 pm
As you'll see in david's example above , a calculated field must explicitly name the columns to be calculated; you cannot reference fields by column order, because the column order can be changed.;
typically this would happen if you drop a column and then add another column back in with the same name,
or if you used enterprise manager in design view to insert a column(which basically creates a new table, moves the data, then renames the table to the old value, then restores constraints)
Yuo can do what you want, but you must use the column names:
ALTER TABLE WHATEVER ADD CALCULATEDFIELD AS ISNULL(WHATEVER.FIELDA,0) - ISNULL(WHATEVER.FIELDB,0)
Lowell
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply