Results 1 to 7 of 7
  1. #1
    Join Date
    Jul 2012
    Posts
    6

    Unanswered: Need help with Sysdate function

    Hello..

    I am using Oracle 10g and I have a piece of code, which when executed, throws ORA-00905: missing keyword error

    select *
    from qsr
    where QSR.DTVALIDUNTIL = (select max(dtvaliduntil) from qsr)
    and QSR.DTVALIDUNTIL = case when QSR.DTVALIDUNTIL - TO_DATE ('12/31/2030', 'MM/DD/YYYY')= 0 then QSR.DTVALIDUNTIL = (sysdate, 'MM/DD/YYYY')
    when QSR.DTVALIDUNTIL - TO_DATE ('12/31/2030', 'MM/DD/YYYY') <0 then QSR.DTVALIDUNTIL = QSR.DTVALIDUNTIL
    end
    and QSR.ID = 11781

    Any help/review will be greatly appreciated.

    Thanks

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    what datatype is QSR.DTVALIDUNTIL?

    what is " (sysdate, 'MM/DD/YYYY')" supposed to do?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Jul 2012
    Posts
    6
    QSR.DTVALIDUNTIL is a date field

    "(sysdate, 'MM/DD/YYYY')" is supposed to give me todays Date in MM/DD/YYYY format

    Thanks for looking into it.

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Code:
    10:06:58 SQL> select (sysdate, 'MM/DD/YYYY') from dual;
    select (sysdate, 'MM/DD/YYYY') from dual
                   *
    ERROR at line 1:
    ORA-00907: missing right parenthesis
    
    
    10:07:04 SQL>
    you need to only use valid SQL syntax
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Jul 2012
    Posts
    6
    Thank you.

    I replaced it. but I still get the same error

    select *
    from qsr
    where QSR.DTVALIDUNTIL = (select max(dtvaliduntil) from qsr)
    and QSR.DTVALIDUNTIL = case when QSR.DTVALIDUNTIL - TO_DATE ('12/31/2030', 'MM/DD/YYYY')= 0 then QSR.DTVALIDUNTIL =(select (sysdate, 'MM/DD/YYYY') from dual)
    when QSR.DTVALIDUNTIL - TO_DATE ('12/31/2030', 'MM/DD/YYYY') <0 then QSR.DTVALIDUNTIL = QSR.DTVALIDUNTIL
    end
    and QSR.ID= 11178

    I am not sure how to go about this

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    DATE datatype have NO inherent format
    Code:
    SELECT * 
    FROM   qsr 
    WHERE  qsr.dtvaliduntil = (SELECT Max(dtvaliduntil) 
                               FROM   qsr) 
           AND qsr.dtvaliduntil = CASE 
                                    WHEN qsr.dtvaliduntil - To_date ('12/31/2030', 
                                                            'MM/DD/YYYY' 
                                                            ) = 0 THEN 
                                    qsr.dtvaliduntil = SYSDATE 
                                    WHEN qsr.dtvaliduntil - To_date ('12/31/2030', 
                                                            'MM/DD/YYYY' 
                                                            ) < 0 THEN 
                                    qsr.dtvaliduntil = qsr.dtvaliduntil 
                                  END 
           AND qsr.id = 11178
    below makes NO sense
    >qsr.dtvaliduntil = qsr.dtvaliduntil
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  7. #7
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Maybe I'm not seeing it well, but - is this correct?
    Code:
    and qsr.dtvaliduntil = case when ... then qsr.dtvaliduntil = sysdate
    Two equal signs?!? Huh? I'd say that the second qsr.dtvaliduntil is a surplus. Twice.

Posting Permissions

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