Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2002
    Posts
    227

    Unanswered: create view....with new date

    Hi,
    I've these table:

    tab1
    ID.......VARCHAR2(24)....PK
    DAYS.....NUMBER
    id........days
    01........5
    02........20

    TAB2
    ID........VARCHAR2(24)....FK ON TAB1(ID)
    DATE_ID...DATE
    id......date_id
    01......01/01/2004
    02......20/01/2004

    I'd to create a view:
    VIEW3
    ID..........VARCHAR2(24)
    NEW_DATE....DATE

    if sysdate < (days+date_id) then I want see (days+date_id) in the new view, else not.
    i.e. sysdate=26/01/2004
    id.......new_date
    01.......06/01/2004

    Can I have this with a view?
    with a decode?

    i tried this but don't run correctly:

    CREATE OR REPLACE VIEW TEST_DATE ( ID,
    NEW_DATE ) AS
    (SELECT TAB1.ID, decode('sysdate<days+date_id','days+date_id', null)
    from TAB1, TAB2
    where TAB1.id=TAB2.id);

    Thanks
    Raf

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

    Re: create view....with new date

    That doesn't work because 'sysdate<days+date_id' is nothing more than a character string like 'Hello world' as far as DECODE is concerned.

    If you can use CASE that would be easiest:

    CREATE OR REPLACE VIEW TEST_DATE ( ID,
    NEW_DATE ) AS
    SELECT TAB1.ID,
    CASE WHEN sysdate<days+date_id THEN days+date_id END
    from TAB1, TAB2
    where TAB1.id=TAB2.id;

Posting Permissions

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