Results 1 to 3 of 3

Thread: Case Statement

  1. #1
    Join Date
    Nov 2011
    Posts
    4

    Unanswered: Case Statement

    Hi everyone. I need help with my Case statement or another recommended solution.

    I have a table named JOB and a table named COMPLAINT. I need to retun a column with the name TYPE with a value of 'TYPE5' or 'TYPE1' based on a value in another table. Here is what I have. I am getting a syntax error. Am I missing something small or do I need to go about this in a different manner.

    SELECT CUSTOMERNO, JDATE,

    CASE
    WHEN
    (SELECT TRIM(TYPE) FROM COMPLAINT WHERE COMPLAINT.JOBNO = JOB.JOBNO = '5') THEN 'TYPE5'

    ELSE
    WHEN
    (SELECT TRIM(TYPE) FROM COMPLAINT WHERE COMPLAINT.JOBNO = JOB.JOBNO = '1') THEN 'TYPE1'

    END AS TYPE
    FROM JOB

    Thanks

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Try this:

    Code:
    SELECT j.CUSTOMERNO, j.JDATE,
    CASE
    WHEN
    TRIM(c.TYPE) = '5') THEN 'TYPE5'
    WHEN
    TRIM(c.TYPE) = '1') THEN 'TYPE1'
    ELSE '?'
    END AS TYPE
    FROM JOB as j
    inner join COMPLAINT as c on (C.JOBNO = J.JOBNO)
    Andy

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Another example which is a little near to your original query.

    Example:
    Code:
    SELECT CUSTOMERNO
         , JDATE
         , CASE (SELECT c.TYPE FROM COMPLAINT c WHERE c.JOBNO = j.JOBNO)
           WHEN '5' THEN 'TYPE5'
           WHEN '1' THEN 'TYPE1'
           ELSE          '?????'
           END AS TYPE
     FROM  JOB j
    Note: I thought that if no left blank(s) were in column TYPE, then TRIM function is not neccesary.

Posting Permissions

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