Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2003
    Posts
    14

    Question Unanswered: Wrong columntype created in view ...

    Hi all,

    I have a problem when creating a view:




    create or replace view cly_test as
    select
    decode(table_x.x_tstart,to_date('01.01.1753','dd.m m.yyyy'),NULL,table_x.x_tstart) TSTART,
    .
    .
    .
    .
    .
    from table_x,
    .
    .
    .



    The type of table_x.x_tstart is date and the type of TSTART should be date as well.
    When I execute the statement, a column of the type varchar(8) is generated. What do I do wrong?

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Wrong columntype created in view ...

    DECODE takes its return value type from the 1st return value, which in this case is NULL. Since NULL can be of any datatype, DECODE just assumes VARCHAR2. Use TO_DATE to force a date:

    decode(table_x.x_tstart,to_date('01.01.1753','dd.m m.yyyy'),TO_DATE(NULL),table_x.x_tstart) TSTART

  3. #3
    Join Date
    Jan 2003
    Posts
    14

    Re: Wrong columntype created in view ...

    That's exactly what I was looking for. I tested it and it works perfect.

    Thanks a lot :-)

Posting Permissions

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