Creating Defaults using Column Names

Hello everyone, I need your help for the below:

When I need to voucher, the distribution date should be the same as invoice date. The SQL won't allow me to use the invoice date as my default for distribution date. This is because the invoice date is a Column names.

Please help.

Thanks in advance!

use computed column.

Hello Mak, thanks for your attention.

May you please give a sample of computed column script?

Thank you so much

Which rdbms do you use? If use ms sql, can find samples in sql books online.

Hello Thanks to you, may I ask what you can recommend website for sql books online. Sorry to bother you too much.

Sql books online is installed alone with sql server or sql client by default.

or you can access BOL from here

http://msdn.microsoft.com/library/de...ar_ts_2x5u.asp

This is a great world, everyone is helping. However if you have time, may you please give me sample script to create defaults using column names. I am very new on this field. Thanks for the free tutorial.

USE pubs
GO
CREATE DEFAULT phonedflt AS 'unknown'
sp_bindefault phonedflt, 'authors.phone'

Hello rmiao, how are u? thanks for your patience on this matter,

may I ask what is "pubs", is this a function? or this is the syntax or a table:

Data given:
Table name: APvoucher (accounts payable)
Column: Invoicedate
Column: Distributiondate (must default as Invoice date)
Default name to be used: Distdef

Using the above:


Use APvoucher(if table)
Go
Create Default Distdef as 'Invoicedate'
sp_bindefault distdef 'APvoucher.Distributiondate'

Is this correct?

Also by the way, in one my of needed script, I also to use the Default function using constant values. I use the 3rd and 4th line with constant value then execute. Then I went to our frontend and input some data, however it would not default to my constant value. For example as below:

Create Default timefencedef as '5'
sp_bindefault timefencedef 'Itemmaster.timefence'

The above didn't work or default to 5 when I am encoding a sample item on our MRP database.

Please again help. Thanks so much.

Pubs is sample db in sql server, so you should replace APvoucher with db name in your code.

If you input specific value in column that has default value, sql will take inputted value and ignore default one. If you leave that column blank, sql will fill it with default value.

oh I see, thanks so much.

I will try and will let you know updates. Have a nice day to you rmiao and Mak!

Oh by the way, with the script used above, can the sql script finds the table of APvoucher?

Sample:
Use DB01
Go
Create Default Distdef as 'Invoicedate' (should this be APvoucher.Invoicedate?)
sp_bindefault distdef 'APvoucher.Distributiondate'

I think for sql script the column names will be a broad one. Do you agree? But can SQL do that?

Thanks sooooo much

Invoicedate is default value for column Distributiondate in table APvoucher. If the column's data type is datetime, you should put date and time for default value. Can't pull data from other column as default value, may define Distributiondate as computed column in this case.

Thank you. Yes I am aware that I can't pull other column as default value and that I should Distributiondate as computed column.
May I ask how can I define Distributiondate as computed column? Thanks again for help.

The issue is that if you create Distributiondate as computed column, you can't input value for that column. In your case, it'll be same as Invoicedate. Is it what you like to do? If so, why need two columns in the table with same data?

Yes, it is okay that we can't input the value for that column as long as it default as per invoice date field.

This how our Front end designed their software separating the invoice date and distribution.

Thanks and regards,

Then you can create it like:

create table t
(
Invoicedate datetime,
Distributiondate as Invoicedate,
...
)

I can't create another table. The distribution date is also connected to other tables. May you please give other advise. Please help. Thanks again.

I didn't ask you to create another table, just give you sample code on how to create column as computed column. Try modify the column in enterprise manager.