If/then

[danielkessler]

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#

[LKBrwn_DBA]

Try this (not tested):

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#

[danielkessler]

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.

[LKBrwn_DBA]

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.

[danielkessler]

ok, thank you very much. You've been a great help to me.