Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

Troubleshooting
Research
Professional Opinions
Ask a Question
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

troubleshooting Question

PL/SQl Expanding the WHERE statement in query

Avatar of Allen Pitts
Allen PittsFlag for United States of America asked on
Oracle Database
3 Comments1 Solution175 ViewsLast Modified:
Hello expert,

Looking for PROTOCOL_ID associated with Employers linked to a certain Payer.

In the CCM_PROTOCOL table, where the OWNER_TYPE_CODE = 15001 (employers),
the OWNER_ID is the EMPLOYER_ID in the ASSOCIATION table.

So the query says: Show me the Protocols where the OWNER_ID
is in the subset of Employers associated with a Payer in the
Association table.

A query was drafted to do this and is copied here with below
marked '-- Cross Insurance Injury care Protocols' and works well.

Then the boss says 'besides Employers add Programs and Locations'.

So the WHERE  statement was expanded to include Programs (When I get Programs to
work will add Locations in a similar fashion.)

..........
where owner_id in
(SELECT
  EMPLOYER_ID
    FROM CCM.CCM_ASSOCIATION
  where payer_ID = 117413
  and IS_DISABLED = 0)
OR
(SELECT
  PROGRAM_ID
    FROM CCM.CCM_ASSOCIATION
  where payer_ID = 117413
  and IS_DISABLED = 0)
and  PROTOCOL_TYPE_CODE = 22001
and
(OWNER_TYPE_CODE =15001 -- employers
OR
OWNER_TYPE_CODE =15006) -- programs
and IS_DISABLED = 0  ;

How can I create a subset of Employers and Programs
from which to select Protocols?

Thanks.

Allen in Dallas

-- Cross Insurance Injury care Protocols
SELECT PROTOCOL_ID,
   PROTOCOL_NAME,
  PROTOCOL_TYPE_CODE,
    IS_DISABLED,
  OWNER_ID,
  OWNER_TYPE_CODE
FROM CCM.CCM_PROTOCOL
where owner_id in
(SELECT
  EMPLOYER_ID
    FROM CCM.CCM_ASSOCIATION
  where payer_ID = 117413
  and IS_DISABLED = 0)
and  PROTOCOL_TYPE_CODE = 22001
and OWNER_TYPE_CODE =15001
and IS_DISABLED = 0  ;
ASKER CERTIFIED SOLUTION
Avatar of Arana (G.P.)
Arana (G.P.)

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Commented:
This problem has been solved!
Unlock 1 Answer and 3 Comments.
See Answers