Microsoft SQL Server forum - Formula problem

Author

Formula problem

NickName

2006-02-17, 8:23 pm

env: sql server 2000

objective:
add a formula for an INT column to the FORMULA field or the DEFAULT
VALUE field in DESIGN VIEW.

all of the following attempts failed
IIF (columnName = 0, "1", columnName + 1)
IIF (columnName = 0, 1, columnName + 1)
IIF (0, "1", columnName + 1)

caveat: cannot use IDENTITY
REASON: I'd like have sets of repeatable values for this row, e.g.
set a
1 -- row 1
2 -- row 2
3
4
5
set b
1 -- row 6
2
3
set c
1 -- row 9
2
3
4

Underlying rationale is to support OO design I understand probably it's
going to be huge headache for lots of people down the road but ...

TIA.

Erland Sommarskog

2006-02-17, 8:23 pm

NickName (dadada@rock.com) writes:
> env: sql server 2000
>
> objective:
> add a formula for an INT column to the FORMULA field or the DEFAULT
> VALUE field in DESIGN VIEW.
>
> all of the following attempts failed
> IIF (columnName = 0, "1", columnName + 1)
> IIF (columnName = 0, 1, columnName + 1)
> IIF (0, "1", columnName + 1)

I will have to admit that I don't understand much of your post.
IIF is Access/VB, but I guess you know that.

In case not, the syntax in SQL server is

CASE WHEN columnName = 0 THEN 1 ELSE columnName + 1 END

Then again, this can be simiplied to:

columnName + 1

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

NickName

2006-02-21, 9:23 am

Oops, haven't touched db for a couple of months, now it seems that I
totally forgot everything or mix everything. Ok, joke aside, case stmt
does not seem to work, strange.

NickName

2006-02-21, 11:23 am

Ahe, because of the default NULL value.

Sponsored Links