Page 1 of 2 12 LastLast
Results 1 to 15 of 20

Thread: Creating Defaults using Column Names

  1. #1
    Join Date
    Feb 2006
    Posts
    13

    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!

  2. #2
    Join Date
    Sep 2002
    Location
    Fantasy
    Posts
    4,254

  3. #3
    Join Date
    Feb 2006
    Posts
    13
    Hello Mak, thanks for your attention.

    May you please give a sample of computed column script?

    Thank you so much

  4. #4
    Join Date
    Sep 2002
    Posts
    5,938
    Which rdbms do you use? If use ms sql, can find samples in sql books online.

  5. #5
    Join Date
    Feb 2006
    Posts
    13
    Hello Thanks to you, may I ask what you can recommend website for sql books online. Sorry to bother you too much.

  6. #6
    Join Date
    Sep 2002
    Posts
    5,938
    Sql books online is installed alone with sql server or sql client by default.

  7. #7
    Join Date
    Sep 2002
    Location
    Fantasy
    Posts
    4,254

  8. #8
    Join Date
    Feb 2006
    Posts
    13
    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.

  9. #9
    Join Date
    Sep 2002
    Posts
    5,938
    USE pubs
    GO
    CREATE DEFAULT phonedflt AS 'unknown'
    sp_bindefault phonedflt, 'authors.phone'

  10. #10
    Join Date
    Feb 2006
    Posts
    13
    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.

  11. #11
    Join Date
    Sep 2002
    Posts
    5,938
    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.

  12. #12
    Join Date
    Feb 2006
    Posts
    13
    oh I see, thanks so much.

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

  13. #13
    Join Date
    Feb 2006
    Posts
    13
    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

  14. #14
    Join Date
    Sep 2002
    Posts
    5,938
    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.

  15. #15
    Join Date
    Feb 2006
    Posts
    13
    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
  •