Results 1 to 4 of 4

Thread: SQL Operators

  1. #1
    Join Date
    Nov 2003
    Location
    england
    Posts
    95

    Unanswered: SQL Operators

    Hello,

    I am using a CASE statement and in my where clause I would like to retuen answers that meet a ceratin critera. At the end of my statement I finish with a >= 0 <=1 so anythign between 0and 1. I am not sure if I am being dense but I ahve tried simply putting in >= 0 <=1 but I get this error: ORA - 00905 - Missing Keyword, have tried using and but it doesnt like it? Any ideas what I should be putting in?

    Thanks

    Lucy

  2. #2
    Join Date
    Mar 2007
    Posts
    623
    Hello,

    please consult correct CASE statement syntax with SQL Reference book, available e.g. online on http://tahiti.oracle.com/. As you did not post an exact statement, it is hard to tell what is wrong. Anyway, did you really use ">= 0 <=1"?

    Anyway, a quick summary is also placed on http://www.orafaq.com/wiki/Case_%28SQL%29. The second variant is usable for this requirement. It would be very hard (if possible) to use the first variant.

  3. #3
    Join Date
    Nov 2003
    Location
    england
    Posts
    95

    Case

    I know quite dense today, what comes of not SQL all the time.

    My statement is:

    CASE
    WHEN ((SELECT
    MAX(job_status_log.log_effective_date)
    FROM
    job_status_log
    WHERE
    job_status_log.job_number = job.job_number AND
    job_status_log.status_code IN('R')) -
    (job_status_log.log_effective_date + follow_up.follow_up_days)) *24
    THEN '19.17'
    ELSE 'Not Late'
    END as Late

    What i want to do is evaluate the result so anything from the where clause that is between 0-24.

    Lucy

  4. #4
    Join Date
    Dec 2003
    Posts
    1,074
    Seems like this should work. Subtracting one date from another gives you the difference in days, with partial days represented by a fraction, so ... :

    Code:
    (CASE WHEN ((SELECT MAX(job_status_log.log_effective_date)
                FROM job_status_log
                WHERE job_status_log.job_number = job.job_number AND
                      job_status_log.status_code IN('R')) - 
                job_status_log.log_effective_date + follow_up.follow_up_days between 0 and 24)
          THEN '19.17'
          ELSE 'Not Late'
     END) as Late

Posting Permissions

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