Results 1 to 3 of 3

Thread: Case Statement

  1. #1
    Join Date
    Aug 2009
    Posts
    1

    Question Case Statement

    I am using Oracle 10g. Sorry about that. I had alias' set up and then deleted them. I am trying to look for the start_time. Looking first at one status_id and if the start_time is null, then looking at another status id for the start_time.

    SELECT 'BLUEPRINT' as Type, eng_req_ID AS ADEPT_ID
    FROM
    (SELECT eng_req_id, MIN (start_time) AS BP_START,
    CASE
    when status_id = 2013 AND category_id = 20 and start_time is not null
    then start_time
    When status_id = 3014 and category_id = 20 and start_time is not null
    then start_time
    else ''
    end)
    from cscape1.eng_req_cat_status_log@test
    GROUP BY eng_req_id)


    I am trying to do a basic IF, Then Else statement looking for a date. I can't get the syntax correct. I want to look at one status Id and if it has a start_time then use it, but if not, I want to look at another Status_id. I am pretty new to this and any help would be appreciated.

    SELECT 'BLUEPRINT' as Type, bps.eng_req_ID AS ADEPT_ID,
    FROM
    (SELECT eng_req_id, MIN (start_time) AS BP_START,
    CASE
    when status_id = 2013
    AND category_id = 20
    WHEN
    status_id = 3014
    AND category_id = 17
    else ''
    end
    from cscape1.eng_req_cat_status_log@test
    GROUP BY eng_req_id)
    Last edited by abbymagga; 08-03-09 at 10:02.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you have several problems here

    first, you say you're looking for a date, but then you say you want to use a status_id

    second, your CASE is missing the THEN clauses

    finally, your outer query references the non-existent bps table and fails to use the MAX value of the derived table subquery, which is also missing its alias

    wanna try again?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Fourth you have posted in Chitty Chat and not told us your RDBMS. Rudy might recognise it, but I don't.

    Please let us know your RDBMS and I'll move the thread to the appropriate forum.
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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