-
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!
-
-
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.
-
-
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.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|