Solved

# Case Statement

Posted on 2014-07-18
281 Views
Hello,

Please assist with understanding this case statement. Let me know if any additional information is needed.
where
(
case
when (select count(*) from dss.contactaddress where contactid = ca.contactid and addresstype='Home' and program = ca.program) =0
then (case
when (select count(*) from dss.contactaddress where contactid = ca.contactid and preferred='1' and program = ca.program) =0
then (select min(addressid) from dss.contactaddress where contactid = ca.contactid and program = ca.program)
else (select min(addressid) from dss.contactaddress where contactid = ca.contactid and preferred='1' and program = ca.program)
end)
else (select min(addressid) from dss.contactaddress where contactid = ca.contactid and addresstype='Home' and program = ca.program)
end)
0
Question by:jverasql
• 2

LVL 73

Expert Comment

ID: 40205091
If the  number of Home addresses is 0 and the number of addresses with Preferred = 1 is 0 then
return rows where the addressid is the MINIMUM address id for that contact and program

If the number of Home addresses is 0 and the number of addresses with preferred = 1 is NOT 0 then
return rows where the address is the MINIMUM address id where preferred = 1 for that contact and program

if the number of Home addresses is NOT 0 then
return rows where the address is the MINIMUM address id for Home addresses for that contact and program
0

LVL 76

Assisted Solution

slightwv (䄆 Netminder) earned 150 total points
ID: 40205095
The general syntax of the CASE statement you have is:
CASE
WHEN "this"="this" THEN this
WHEN "that"="that" THEN that
ELSE something_else
END

If you break the giant case down and indent properly, you'll see you just have nested case statements.

What parts are confusing?
0

LVL 48

Accepted Solution

PortletPaul earned 350 total points
ID: 40205875
``````WHERE ca.addressid =
(
CASE
WHEN (                                     -- CHECK for HOME ADDRESS
SELECT
COUNT(*)
WHERE contactid = ca.contactid
AND addresstype = 'Home'
AND program = ca.program
)
= 0 THEN (CASE                             -- if NO home address
WHEN (                                    -- CHECK for PREFERRED ADDRESS
SELECT
COUNT(*)
WHERE contactid = ca.contactid
AND preferred = '1'
AND program = ca.program
)                                         -- if no preferred address
= 0 THEN (                                -- USE first available ADDRESS
SELECT
WHERE contactid = ca.contactid
AND program = ca.program
)
ELSE (                                    -- use first preferred ADDRESS
SELECT
WHERE contactid = ca.contactid
AND preferred = '1'
AND program = ca.program
) END)
ELSE (                                    -- There IS a home address so use it
SELECT
WHERE contactid = ca.contactid
AND addresstype = 'Home'
AND program = ca.program
) END)
``````
0

LVL 73

Expert Comment

ID: 40209804
jverasql - can I ask why a split didn't include the first post - did it not answer your question?  I does (I think) explain exactly what your nested case statements do.  If not, what's missing?
0

## Join & Write a Comment Already a member? Login.

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

#### 758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

#### Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!