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
Arana (G.P.)

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

Join our community to see this answer!
Unlock 1 Answer and 3 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 3 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros