How do I use a column name in the DEFAULT field?

[sgursahaney]

I would like to define a default value for a column using a computation of the value from another column. However, SQL Server complains when I specify another column in the default clause. For example, I would like to use the following formula as the default for column EXT:

SUBSTRING(CUST_PHONE,9,4)

where CUST_PHONE is another column in the table that has a phone number in it.

Does anybody know how to do this?

Thanks in advance.

 

[tlbroadbent]

You cannot set the default value equal to another column. You can create a computed column if you are using SQL Server 7 or 2000. The computed column is not physically stored and is resolved each time it is selected.

CREATE TABLE (..., CUST_PHONE varchar(18), CUST_EXT As SUBSTRING(CUST_PHONE,9,4), ...)

Another method available for setting a column based on another column is TRIGGERS. You could create an INSERT and UPDATE TRIGGER on the table that would update the extension column when the phone column is inserted or updated. Terry

http://members.home.net/tlbroadbent/sqlarticles.htm

"I shall try to correct errors when shown to be errors, and I shall adopt new views so fast as they shall appear to be true views." - Abraham Lincoln

 

[sgursahaney]

Thanks. I considered the trigger approach, but I was concerned that my INSERT would fail because the EXT column is setup not to accept nulls. Isn't the trigger executed after the INSERT is performed?

 

[tlbroadbent]

Why not just insert the extension, when you insert the phone?

INSERT tbl (col1, col2, phone, ext, colA, ...)
VALUES (val1, val2, '000000001234', substring('000000001234',9,4), valA, ...)

NOTE: Generally, derived or calculated values are not stored in a relational database as that violates normalization rules. Sometimes, databases are denormalized for specific purposes, generally realted to performance. In your case, would it make sense to derive the extension when querying the database rather than storing the extension physically on the table? Terry

http://members.home.net/tlbroadbent/sqlarticles.htm

"The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge." - Daniel J Boorstin

 

[sgursahaney]

Actually the field contains a rather complex calculation that uses the last 4 digits of the phone number field. I just simplified it for the purpose of this posting. My problem is that other people inserting records into the table are populating the field incorrectly. I wanted to find a way where I could populate the field automatically when records are inserting. Thanks.

 

[phil1976]

Just a note on selecting the last values in a string.

In Oracle SQL, the substr function allows the use of negative numbers, thus
substr(string, -4);
would return the last 4 chars in the string.

 

[tlbroadbent]

phil1976,

I appreciate your willingness to participate but want to note that this thread has been inactive for nine months. In addition, this is a MS SQL Server forum and the Oracle statement is without value. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[sgursahaney]

Actually, I am still monitoring this thread in hopes that a solution is forthcoming. I appreciate the contrasts to Oracle but I still need a solution for SQL Server.

Regards,
Suresh

 

[tlbroadbent]

sgursahaney,

Consider this when thinking about Triggers.

1) If you have SQL 2000, you can use Instead of Triggers which fire before the insert or update.

2) You could set up a default value on the column so inserts will not fail. Then the trigger could fire after the update and perform the complex calculation.

3) You could insert a default value and then update in a trigger. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[crystalized]

This is a very simplified example of something we do for some of our tables - I have removed the stuff for deriving the value as it is not really relevant since your own formula will be what you use. But it might get you started.

CREATE TRIGGER [triggerName]ON dbo.tblSomeTable 
INSTEAD OF INSERT
NOT FOR REPLICATION
AS

	DECLARE
	@SomeInt INTEGER,
	@SomeVal NVARCHAR(40)


	SELECT @SomeVal = SomeVal FROM Inserted 
	
	SELECT @SomeInt = <Derive your value here>

	INSERT tblSomeTable (SomeVal,SomeInt) 
	VALUES (@SomeVal, @SomeInt)

Good Luck
Crystal
crystalized_s@yahoo.com

--------------------------------------------------

Experience is one thing you can't get for nothing.

-Oscar Wilde

 

[sgursahaney]

Crystal:
This looks like it will work for me! Thank you.