-
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#
-
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
-
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.
-
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
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|