DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: If/then

  1. #1
    Join Date
    Sep 2004
    Posts
    51

    If/then

    I have this statement in my SELECT:
    b.major1 || ',' || b.major2 || ',' || b.minor

    But I really want to only concatenate IF b.major2 is not null or b.minor is not null. I was able to partially do it with CASE, but I've not been able to make it work otherwise.

    Can you help me get this done with IF? Is there a good tutorial to use so that I don't post these simple questions?


    Here's the whole query:

    SELECT
    a.passport_listing_id
    ,a.passport_registration_id
    ,coalesce(b.email, c.email) email
    ,coalesce((b.major1), c.dept) major
    ,CASE
    WHEN b.major2 is not null THEN b.major1 || ', ' || b.major2
    END as a_major

    ,e.passport_survey_id
    ,f.title
    ,f.start_date
    FROM passport_registration a
    Left outer join students b
    On a.student_id = b.student_id
    Left outer join people c
    On a.people_id = c.id
    Left outer join passport_attendance d
    On a.passport_listing_id = d.passport_listing_id
    and a.s_uid = d.s_uid
    Left outer join passport_survey e
    On a.passport_listing_id = e.passport_listing_id
    and a.s_uid = e.s_uid

    Left outer join passport_listing f
    On a.passport_listing_id = f.passport_listing_id

    WHERE a.s_uid = #s_uid#

  2. #2
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,492

    Cool Case..end case

    Try this (not tested):
    Code:
    SELECT   A.Passport_listing_id, A.Passport_registration_id,
             COALESCE (B.Email, C.Email) Email,
             COALESCE ( (B.Major1), C.Dept) Major,
             CASE
                 WHEN B.Major2 IS NOT NULL OR B.Minor IS NOT NULL
                 THEN
                        B.Major1
                     || DECODE (B.Major2, NULL, '', ',' || B.Major2)
                     || DECODE (B.Minor, NULL, '', ',' || B.Minor)
             END AS A_major, 
             E.Passport_survey_id, F.Title, F.Start_date
      FROM                       Passport_registration A
                             LEFT OUTER JOIN
                                 Students B
                             ON A.Student_id = B.Student_id
                         LEFT OUTER JOIN
                             People C
                         ON A.People_id = C.Id
                     LEFT OUTER JOIN
                         Passport_attendance D
                     ON A.Passport_listing_id = D.Passport_listing_id
                        AND A.S_uid = D.S_uid
                 LEFT OUTER JOIN
                     Passport_survey E
                 ON A.Passport_listing_id = E.Passport_listing_id
                    AND A.S_uid = E.S_uid
             LEFT OUTER JOIN
                 Passport_listing F
             ON A.Passport_listing_id = F.Passport_listing_id
     WHERE   A.S_uid = #s_uid#
    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

  3. #3
    Join Date
    Sep 2004
    Posts
    51
    That largely worked! Thank you. It left out the situation where if there was ONLY b.major1, it left it blank. I put in an ELSE for that. I also forgot to say that I was originally using coalesce to choose between the conjugation you worked out for me and another field c.dept. I was able to add that into the code you gave me with another DECODE. here's how it turned out:

    CASE
    WHEN b.major2 IS NOT NULL OR b.minor IS NOT NULL OR c.dept IS NOT NULL
    THEN
    b.major1
    || DECODE (b.major2, NULL, '', ', ' || b.major2)
    || DECODE (b.minor, NULL, '', ', ' || b.minor)
    || DECODE (c.dept, NULL, '', ', ' || c.dept)
    ELSE b.major1
    END AS major



    I have a question though. I don't quite understand DECODE. The first value is the expression. The 2nd through N value is a list of comparison's for the expression. Is that right? Or is it 1 compare and then one result and 1 compare and one result, in sets. And is the default value, the last value, designated by it not being part of a 2-part set?


    Thank you very, very much for your assistance.

  4. #4
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,492

    Talking Decode()

    DECODE is simple:

    1st parameter is the expression, then there are "pairs" of values and last is the default if none of the values match.
    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

  5. #5
    Join Date
    Sep 2004
    Posts
    51
    ok, thank you very much. You've been a great help to me.

Posting Permissions

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


Click Here to Expand Forum to Full Width