Results 1 to 9 of 9
  1. #1
    Join Date
    Nov 2010
    Posts
    30

    Unanswered: HELP: How to declare NVL datatype?

    Hi there,

    This is a fairly newbie question because I'm still a learning beginner

    How do I declare data type NUMBER with NVL?

    For instance, I'm creating an attribute contact_number and it starts with 0.
    Hence, I'm presuming it'd be something like this...
    (I was specifically told to be careful of NULL value)
    contact_number NVL(NUMBER, 0)
    Is that true?

    Any suggestion is very much appreciated.

    Thank you and regards.

  2. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    NVL is a function that takes an expression and a default value. If the expression is null the default value will be returned otherwise the expression.

    I don't really understand what you are trying to do, but if you want to avoid NULL values in that column, simply declare it as NOT NULL
    Code:
    contact_number NUMBER NOT NULL
    This will generate an error if someone tries to put a NULL value into that column.

    You can also define a default value if the column is not specified in an INSERT statement:
    Code:
    contact_number NUMBER NOT NULL DEFAULT 0
    but this will only have an effect for INSERTs that explicitely exclude that column.

  3. #3
    Join Date
    Nov 2010
    Posts
    30

    Is it like this?

    Thank you very much for replying really quickly.

    Well, I'm trying to do the following...
    CREATE TABLE customer
    (
    customer_id NUMBER(5),
    firstname CHAR(25),
    lastname CHAR(25),
    address_line_1 VARCHAR(30),
    address_line_2 VARCHAR(30),
    contact_no NUMBER(20),
    email VARCHAR2(30),

    CONSTRAINT client_PK PRIMARY KEY (customer_id)
    );
    but the phone number starts with zero so I was told to control that null value.
    Are you suggesting to do as follows?
    contact NUMBER(20) NOT NULL
    Or should I declare it as a constraint? like...
    CONSTRAINT cust_contact_nn CHECK (NUMBER IS NOT NULL)
    I thought I was supposed to use NVL function here.

    Please help me out again.

    Thanks in advance.

    Regards.

  4. #4
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    contact NUMBER(20) NOT NULL

    and

    CONSTRAINT cust_contact_nn CHECK (NUMBER IS NOT NULL)

    are equivalent. There is no practical difference between those two.

  5. #5
    Join Date
    Nov 2010
    Posts
    30
    Quote Originally Posted by shammat View Post
    contact NUMBER(20) NOT NULL

    and

    CONSTRAINT cust_contact_nn CHECK (NUMBER IS NOT NULL)

    are equivalent. There is no practical difference between those two.
    Thank you very much, sir.
    You answered everything I wanted to know within a very short time
    You're a life saver.

    Best regards,
    db_newbie

  6. #6
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    the phone number starts with zero
    If that's the case, NUMBER is a wrong data type for that column - it should be VARCHAR2.

  7. #7
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by Littlefoot View Post
    If that's the case, NUMBER is a wrong data type for that column - it should be VARCHAR2.
    Depends on what "starts with zero" means

    I understood, the numbering should start with the number zero.

    But you are right as well. If it means "each sequence of numbers should start with the character zero" then this could either be done by storing that character when using VARCHAR or by simply formatting the number with a leading zero during retrieval.

  8. #8
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    I still don't think that NUMBER is the right choice (at least, here where I live).

    For example: my telephone number is 654-321. I live in Zagreb. Zagreb's city code is 1. It means that - when someone calls me (and doesn't live in Zagreb) - has to dial "1-654-321".

    On the other hand, my friend lives in Split and his telephone number is 987-654. Split's city code is 021, so I call him by dialing "021-987-654".

    From those two examples, it appears that (even without formatting numbers with dashes) NUMBER column would contain "1654321" (which is OK) and "21987654" (which is wrong). VARCHAR2 would contain "1654321" and "021987654" (both OK).

  9. #9
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    I agree, I would store a phone number (at least international ones) as characters as well.

    But after all the column is called contact_number, so it might be something totally different

Posting Permissions

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