default value = "WR" + [column 1]

[russland]

hi,

I've got a simple table.

ID (NUMERIC, PRIMARY KEY)
OrderID (NVARCHCHAR(50))

The OrderID should store a concatenation of the autoincremented ID and a string. how do i apply this change?

thanks heaps for any hint.

 

[AlexCuse]

I don't think you can reference another column in your column's default value. Maybe a calculated column would be better for you?

What is your DBMS?

[small]----signature below----[/small]
I can't compete with you physically, and you're no match for my brains.
You're that smart?
Let me put it this way. Have you ever heard of Plato, Aristotle, Socrates?
Yes.
Morons!

 

[russland]

a calculated column? you mean one i do on the fly (by DML instead of DDL)?

it's ms sql server 2000

thanks already for your effort

 

[AlexCuse]

You could do it either way. Here's an example of how you can do it in your DDL:

[COLOR=blue]create[/color] [COLOR=blue]table[/color] #t (ID [COLOR=blue]int[/color] [COLOR=blue]identity[/color] (1,1)
	[COLOR=green]--OrderID is calculated column (no data type)
[/color]	, OrderID [COLOR=blue]as[/color] [COLOR=red]'WR'[/color] + [COLOR=#FF00FF]right[/color]([COLOR=red]'00000000'[/color] + [COLOR=#FF00FF]cast[/color](ID [COLOR=blue]as[/color] [COLOR=blue]varchar[/color](8)), 8)
	, CustName [COLOR=blue]varchar[/color](50)
)

[COLOR=blue]insert[/color] #t (custname)
[COLOR=blue]select[/color] [COLOR=red]'alex'[/color]
union all [COLOR=blue]select[/color] [COLOR=red]'russ'[/color]
union all [COLOR=blue]select[/color] [COLOR=red]'someone else'[/color]

[COLOR=blue]select[/color] * [COLOR=blue]from[/color] #t

[COLOR=blue]drop[/color] [COLOR=blue]table[/color] #t

Of course if you wanted to do it on the fly just:

select OrderID = 'WR' + right('00000000' + cast(ID as varchar(8)), 8)

or something like that. I thought you were after DDL though.

Hope this helps,

Alex

[small]----signature below----[/small]
I can't compete with you physically, and you're no match for my brains.
You're that smart?
Let me put it this way. Have you ever heard of Plato, Aristotle, Socrates?
Yes.
Morons!

 

[russland]

yes, that did it. thanks a lot, buddy!

however, in sql you can save the statement below in the formula field of the attribute:

('WR-RG-' + convert(varchar(8),[ID]))

 

[AlexCuse]

No problem, I'm glad it helped

What do you mean by 'formula field of the attribute'? I am a little confused. Do you mean where you enter the default value in Enterprise Mangler when you edit a table? Or are you talking about the query builder?

[small]----signature below----[/small]
I can't compete with you physically, and you're no match for my brains.
You're that smart?
Let me put it this way. Have you ever heard of Plato, Aristotle, Socrates?
Yes.
Morons!

 

[russland]

nope. that's when using the sql server enterprise manager selecting a table in design mode. then focus the attribute (column) you want. in the lower bottom there are attribute values to set. e.g description, default value, precision, itendity, etc. almost at the very bottom there's "formula" just paste the computing value in there. That will do it. Do I make sense?

 

[AlexCuse]

Ah, I see. I don't use Enterprise Manager that much (it actually doesn't work on my PC since I installed 2005 client tools). I suppose that is how you set up a computed column though. I bet if you script out the table it looks the same.

Thanks for taking the time to follow up.

Alex

[small]----signature below----[/small]
I can't compete with you physically, and you're no match for my brains.
You're that smart?
Let me put it this way. Have you ever heard of Plato, Aristotle, Socrates?
Yes.
Morons!

 

[russland]

Yes, When you script. It will populate the field formula with that computed signature. So it makes no difference whether you script or click.

Tell me about it. I face the same problem running sql2000 und sql200+ in paralell.

Best wishes,

 

[SQLSister]

Actaully it makes a huge difference if you script or click for most structural changes. When you make any change to an existing table through Enterprise Manager, it will not run alter table but will create a new table with the columns change, move the records, drop the old table and rename the newtable to the same name as the old table. It is not a process you should use on anything but a development system with a small table size. Never use Enterprise Manager to make structure changes on Production.

"NOTHING is more important in a database than integrity." ESquared

 

[russland]

Yes, I would concur. Development Environment only.