Results 1 to 10 of 10
  1. #1
    Join Date
    Nov 2011
    Posts
    5

    Unanswered: defaulting a date value

    When creating customers in our POS system there is an expiration date field that is NOT required. If a date is entered everything is good. The problem is when a date is not entered it was resulting in a NULL value in the expiration date field.

    We are trying to default the expiration_date to a future date '01-01-9999' when it was not entered by the agent. But cannot seem to get it to work.

    We set the field up as 'NOT NULL' default '01-01-9999' but get the field cannot be null error message on the insert if a date is not entered.

    With the field set to allow nulls with the default '01-01-9999' the record is inserted, but the expiration date field is blank.

    Should this be handled through the insert trigger instead?

    Thank you very much for you help

  2. #2
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    If you want the default value to be applied you have to either not include that column during INSERT or use the DEFAULT keyword:
    Code:
    INSERT INTO your_table (id, some_column) 
    VALUES 
    (1, 'test');
    or
    Code:
    INSERT INTO your_table (id, some_column, expiration_date) 
    VALUES 
    (1, 'test', DEFAULT);

  3. #3
    Join Date
    Nov 2011
    Posts
    5

    defaulting a date value

    Thank you for your reply. The cashier may actually enter a date. If they do, then we want to use that value. It is only when they don't enter a date in the expiration date field that we want to default it.

    I am thinking based on your response, we do need to handle it in the trigger.

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    You must have done something wrong, because - it works if you set it up properly. Here's an example:
    Code:
    SQL> create table test
      2    (id       number,
      3     name     varchar2(20),
      4     end_date date default to_date('01.01.9999', 'dd.mm.yyyy') not null
      5    );
    
    Table created.
    
    SQL> insert into test (id, name) values (1, 'Little');
    
    1 row created.
    
    SQL> insert into test (id, name, end_date) values (2, 'Foot', to_date('28.09.2011', 'dd.mm.yyyy'));
    
    1 row created.
    
    SQL> select * from test;
    
            ID NAME                 END_DATE
    ---------- -------------------- ----------
             1 Little               01.01.9999
             2 Foot                 28.09.2011
    
    SQL>
    As you can see, the first INSERT statement doesn't contain the END_DATE column, but - when selecting from a table - that record got its (END_DATE's) default value.

  5. #5
    Join Date
    Sep 2011
    Posts
    75
    Hello,

    Try this

    CREATE TABLE order_status (
    order_status_id INTEGER,
    status VARCHAR2(20) DEFAULT 'Order placed' NOT NULL,
    last_modified DATE DEFAULT SYSDATE
    );

    INSERT INTO order_status (order_status_id) VALUES (2 );

    ORDER_STATUS_ID STATUS LAST_MODIFIED
    --------------- -------------------- -----------
    2 Order placed 2011-NOV-03

  6. #6
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by cedwards65 View Post
    It is only when they don't enter a date in the expiration date field that we want to default it.
    Then simply don't send a value if the user doesn't enter one as shown in my first example. No need for a trigger.

  7. #7
    Join Date
    Nov 2011
    Posts
    5

    defaulting a date value

    Thank you for the responses.

    The insert statement comes from the POS system and is not dynamically created based on whether the cashier enters a date or not. So it will always contain a position in the insert statement for the date whether one is entered or not.

    I need to be able to handle the defaulting of the value at the database level if the cashier leaves it blank. That is why I was thinking I would need to do it in the insert trigger.

  8. #8
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Then simply use a before row trigger on the table and set the value if null.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  9. #9
    Join Date
    Mar 2007
    Posts
    623
    Quote Originally Posted by cedwards65 View Post
    The insert statement comes from the POS system and is not dynamically created based on whether the cashier enters a date or not. So it will always contain a position in the insert statement for the date whether one is entered or not.
    What about handling it in the POS system (send the default value if nothing is entered)? Something like default column value in database for that input field?

  10. #10
    Join Date
    Nov 2011
    Posts
    5

    defaulting a date value

    @beilstwh
    That is what I was thinking, but i just wanted to make sure I wasn't just creating the table wrong for the default not to work.

    @flyboy
    Unfortunately, it is a COTS product and getting them to change something this small for us is ridiculously difficult..

    Thank you to everyone who responded. Looks like I will go with the trigger on the insert.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •