SQL to pull back a couple different scenarios.

"on the same claim."

What does that mean, in terms of column values?  We know nothing about your data, there's no way we possibly could.  

You're using two different tables, what does each contain?  This is all just incredibly confusing.  There's no way for us to make any real sense of it as is.

This question just cries out for some sample data, with and without the criteria for selection, and the expected results. It will help clarify your intent and provide a test case for the experts.

It looks like you have (at least) three different tables involved in your query:
AP_CLAIM
AP_CLAIM_PX_2
PE

The portion of your "where" clause that you have provided certainly doesn't include all of the information we would need to help you.

Please tell us:
1. Is this all of the tables used in this query?
2. Column details for each table.
3. Indexes on these tables
4. Join conditions for these tables
5. Row counts for these tables (OK, this last one isn't actually "required" for us, but it can certainly help us to find an efficient solution for you.)

Ok here is what i have so far. I believe i have satisfied the 1st part of the request in the where clause but still struggling with the OR on the 2nd part.

WHERE 
AP_CLAIM.DATE_RECEIVED between '02/16/2019' and '02/17/2020' and  
(PE.PROC_CODE in ('76376','76377') or (AP_CLAIM_PX_2.PAT_PAY_CMP_CMG = '1005000742;G' or AP_CLAIM_PX_2.PAT_PAY_CMP_CMG = '1005000743;G' or AP_CLAIM_PX_2.PAT_PAY_CMP_CMG = '1005000745;G'))

Select allOpen in new window

Criteria1:
DOS 02/16/2019-2/17/2020 and
All claims that contain a service line with either 76376 or 76377 (regardless of what component group this hit) and
Any other service lines that hit any of the following component groups 1005000742;G; 1005000743;G; 1005000745;G on the same claim.
 

I am struggling with the last part of the request which is "OR"

Criteria2:
All claims that contain ONLY a service line with either 76376 or 76377 and no other service lines present. So if any other codes are present on the service line do not pull in. Only pull in if one of the 2 codes above exist by itself. These codes are PE.PROC_CODE

I thought maybe something similar to this but i know this isnt right.

 Or AP_CLAIM.claim_id IN
(select ubc.claim_id
FROM HCCLCO.AP_CLAIM_PX UBC
LEFT OUTER JOIN HCCLCO.CLARITY_EAP PE2 ON PE2.PROC_ID = AP_CLAIM_PX.PROC_ID
where (PE2.PROC_CODE = '76376' or PE2.PROC_CODE = '76377'))

Select allOpen in new window

Thank you,

I don't see how a "LEFT OUTER JOIN" helps you here.   If you could post your entire "where" clause here, that would likely make things easier for us.

Instead of more SQL please make up some sample data and post the expected results from that data.

For your "or" condition you need something more like this:

PE.PROC_CODE in ('76376','76377')
and not exists (select 1 from pe2
where  pe2.[your_key] = [your_key] 
and pe2.proc_code not in ('76376','76377'))

Select allOpen in new window

but, since we don't have your complete "where" clause, it is impossible for us to write exactly what you need.

Not sure the "exists" piece is necessary and it would likely cause you to access the table twice.

Yes, the "exists..." clause does cause the table to be accessed twice.  But this is the requirement I was trying to meet: "if any other codes are present on the service line do not pull in. Only pull in if one of the 2 codes above exist by itself".  Since we have to check for both "the 2 codes above" and make sure that "any other codes are *NOT* present", I don't know of a way to do that without accessing the table twice.

But trying to write this without being able to see the full "where" clause is challenging.  I don't know which other column value in the PE table needs to match the other table(s) when we check the PROC_CODE values.  Maybe that is a claim_id that must match AP_CLAIM.claim_id?

If the entire "where" clause could be posted here, that would make things much easier for us.

Here is my full code:

All claims that contain a service line with either 76376 or 76377 (regardless of what component group this hit) and
Any other service lines that hit any of the following component groups 1005000742;G; 1005000743;G; 1005000745;G on the same claim.
OR
All claims that contain ONLY a service line with either 76376 or 76377 and no other service lines present.  

SELECT DISTINCT CM1.INTERNAL_ID AS CLAIM_NO,ap_claim.acct_num_with_ven AS Invoice,
PAT.PAT_MRN_ID,PAT.PAT_NAME,CAST (pat.Birth_Date AS DATE) as BIRTH_DATE,trunc(((sysdate - pat.birth_date)/365.25),0.1) AS PAT_AGE, 
ZC_CLM_STATUS.TITLE AS CLM_STS, ZC_CLM_AP_STAT.TITLE AS AP_STS_C, 

CASE	WHEN AP_CLAIM.ORIG_REV_CLM_ID IS NULL AND AP_CLAIM.ORIG_ADJST_CLM_ID IS NULL THEN 'Original Claim'		
	WHEN AP_CLAIM.ORIG_REV_CLM_ID IS NULL AND AP_CLAIM.ADJST_CLM_ID IS NOT NULL AND AP_CLAIM.ORIG_ADJST_CLM_ID IS NOT NULL THEN 'Multiple Adj Interim Claim'	
    WHEN AP_CLAIM.ORIG_REV_CLM_ID IS NOT NULL THEN 'Reversal Claim'		
	WHEN AP_CLAIM.ORIG_REV_CLM_ID IS NULL AND AP_CLAIM.ADJST_CLM_ID IS NULL AND AP_CLAIM.ORIG_ADJST_CLM_ID IS NOT NULL THEN 'Adjusted Claim'	
 END AS Claim_State,
spec.name AS SPECIALTY, AP_CLAIM.TOT_PAT_TOTAL, AP_CLAIM.TOT_COPAY, AP_CLAIM.TOT_COINS, PX.COPAYMENT, PX.COINSURANCE,
CF.NAME  AS FORMAT,
T2.NAME AS "CAP/NON-CAP", IE.NAME AS "INT/EXT",ZC_IN_OUT_NET.TITLE AS IN_OUT_NET_C,
ZC_ALLOWED_CODE.NAME As LINE_ALLOWED_CODE,
CLARITY_EPP.BENEFIT_PLAN_NAME, CLARITY_EPM.PAYOR_NAME, GRP.PLAN_GRP_NAME,LOB.LOB_NAME,V.VENDOR_NAME,
POS.POS_NAME AS PLACE_OF_SERVICE,SER.PROV_NAME AS PROVIDER,
AP_CLAIM.SERV_DATE, AP_CLAIM.SERVICE_START_DATE , AP_CLAIM.SERVICE_END_DATE , AP_CLAIM.DATE_RECEIVED ,trunc((SYSDATE - ap_claim.date_received - 1),0.1) AS CLAIM_AGE,
AP_CLAIM_PX_2.LINE, AP_CLAIM_PX.POS_TYPE_C,AP_CLAIM_PX.MODIFIERS, 
ap_claim.type_of_bill,
PE.PROC_CODE, PE.PROC_NAME, 
RV.REVENUE_CODE , RV.REVENUE_CODE_NAME , 
pe.modifier, 
EAP.CPT_CODE_1,EAP.CPT_CODE_2,EAP.CPT_CODE_3,EAP.CPT_CODE_4,EAP.CPT_CODE_5,
EAP.CPT_CODE_6,EAP.CPT_CODE_7,EAP.CPT_CODE_8,EAP.CPT_CODE_9,EAP.CPT_CODE_10,
REV.REV_CODE_1,REV.REV_CODE_2,REV.REV_CODE_3, REV.REV_CODE_4,REV.REV_CODE_5,
DX.CLM_DX_1,DX.CLM_DX_2,DX.CLM_DX_3,DX.CLM_DX_4,DX.CLM_DX_5,
DX.CLM_DX_6,DX.CLM_DX_7,DX.CLM_DX_8,DX.CLM_DX_9,DX.CLM_DX_10,
AP_CLAIM_PX.BILLED_AMT, AP_CLAIM_PX.ALLOWED_AMT, AP_CLAIM_PX.NET_PAYABLE,
AP_CLAIM_PX_2.PAT_PAY_CMP_CMG, CMKM.INTERNAL_ID AS BENEFIT_PACKAGE_ID, CL_CMK.BENEFIT_PACKAGE_NM ,CL_CMA.ADJUD_FORMULA_NAME

FROM HCCBBB.AP_CLAIM AP_CLAIM 
LEFT OUTER JOIN HCCBBB.PATIENT PAT ON PAT.PAT_ID=AP_CLAIM.PAT_ID
INNER JOIN HCCBBB.CLM_MAP CM1 ON CM1.CID = AP_CLAIM.CLAIM_ID 
left OUTER JOIN HCCBBB.AP_CLAIM_2 C2 ON AP_CLAIM.CLAIM_ID = C2.CLAIM_ID 
LEFT OUTER JOIN HCCBBB.AP_CLAIM_3 C3 ON AP_CLAIM.CLAIM_ID = C3.CLAIM_ID 
LEFT OUTER JOIN HCCBBB.ZC_CLM_TRAIT_2 T2 ON C2.CLM_TRAIT_2_C = T2.CLM_TRAIT_2_C 
LEFT OUTER JOIN HCCBBB.ZC_INT_EXT IE ON C3.INT_EXT_C = IE.INT_EXT_C
LEFT OUTER JOIN HCCBBB.ZC_CLM_STATUS ZC_CLM_STATUS ON ZC_CLM_STATUS.STATUS_C=AP_CLAIM.STATUS_C
LEFT OUTER JOIN HCCBBB.ZC_CLM_AP_STAT ZC_CLM_AP_STAT ON ZC_CLM_AP_STAT.AP_STS_C=AP_CLAIM.AP_STS_C
LEFT OUTER JOIN HCCBBB.ZC_IN_OUT_NET ZC_IN_OUT_NET ON ZC_IN_OUT_NET.IN_OUT_NET_C=AP_CLAIM.IN_OUT_NET_C
LEFT OUTER JOIN HCCBBB.ZC_CLAIM_FORMAT CF ON AP_CLAIM.CLAIM_FORMAT_C = CF.INTERNAL_ID 
LEFT OUTER JOIN HCCBBB.zc_specialty spec ON ap_claim.assoc_spec_c = spec.specialty_c
LEFT OUTER JOIN HCCBBB.AP_CLAIM_PX AP_CLAIM_PX ON AP_CLAIM_PX.CLAIM_ID= AP_CLAIM.CLAIM_ID 
LEFT OUTER JOIN HCCBBB.AP_CLAIM_PX_2 AP_CLAIM_PX_2 ON (AP_CLAIM_PX_2.CLAIM_ID= AP_CLAIM_PX.CLAIM_ID AND AP_CLAIM_PX_2.LINE = AP_CLAIM_PX.LINE)
LEFT OUTER JOIN HCCBBB.CL_CMA CL_CMA ON CL_CMA.ADJUD_FORMULA_ID=AP_CLAIM_PX_2.PAT_PAY_CMA
LEFT OUTER JOIN HCCBBB.CL_CMK CL_CMK ON CL_CMK.BENEFIT_PACKAGE_ID = AP_CLAIM_PX_2.PAT_PAY_CMK
LEFT OUTER JOIN HCCBBB.CMK_MAP CMKM ON CMKM.CID = CL_CMK.BENEFIT_PACKAGE_ID
LEFT OUTER JOIN HCCBBB.CLARITY_EAP PE ON PE.PROC_ID = AP_CLAIM_PX.PROC_ID 
--LEFT OUTER JOIN HCCBBB.CL_UB_REV_CODE RV ON RV.UB_REV_CODE_ID = AP_CLAIM_PX.UBC_REVENUE_CODE_ID 
LEFT OUTER JOIN HCCBBB.EPP_MAP EPP_MAP ON EPP_MAP.CID =AP_CLAIM.BENEFIT_PLAN_ID 
LEFT OUTER JOIN HCCBBB.ZC_ALLOWED_CODE ZC_ALLOWED_CODE ON ZC_ALLOWED_CODE.ALLOWED_CODE_C=AP_CLAIM_PX.ALLOWED_CODE_C
LEFT OUTER JOIN HCCBBB.CLARITY_EPP CLARITY_EPP ON CLARITY_EPP.BENEFIT_PLAN_ID=AP_CLAIM.BENEFIT_PLAN_ID
LEFT OUTER JOIN HCCBBB.CLARITY_EPM CLARITY_EPM ON CLARITY_EPM.PAYOR_ID=AP_CLAIM.PAYOR_ID
/* LEFT OUER JOIN HCCBBB.CMG_MAP ON CL_CMG.COMPONENT_GRP_ID=CMG_MAP.CID*/
LEFT OUTER JOIN HCCBBB.CLARITY_LOB LOB ON LOB.LOB_ID = AP_CLAIM.CLM_LOB_ID 
LEFT OUTER JOIN HCCBBB.PLAN_GRP GRP ON GRP.PLAN_GRP_ID = AP_CLAIM.PLAN_GROUP_ID
LEFT OUTER JOIN HCCBBB.CLARITY_VENDOR V ON AP_CLAIM.VENDOR_ID = V.VENDOR_ID 
LEFT OUTER JOIN HCCBBB.CLARITY_POS POS ON POS.POS_ID=AP_CLAIM.LOC_ID
LEFT OUTER JOIN HCCBBB.CLARITY_SER SER ON SER.PROV_ID=AP_CLAIM.PROV_ID
LEFT OUTER JOIN HCCBBB.CL_UB_REV_CODE RV ON RV.UB_REV_CODE_ID = AP_CLAIM_PX.UBC_REVENUE_CODE_ID 
LEFT OUTER JOIN HCCBBB.AP_CLAIM_DX DX ON DX.CLAIM_ID = AP_CLAIM.CLAIM_ID
LEFT OUTER JOIN HCCBBB.CLARITY_EDG EDG ON EDG.DX_ID=DX.DX_ID
LEFT OUTER JOIN HCCBBB.AP_CLAIM_PX PX ON PX.CLAIM_ID = AP_CLAIM.CLAIM_ID
LEFT OUTER JOIN (SELECT	PXX.CLAIM_ID
				,MAX(CASE WHEN PXX.LINE = 1 THEN F.REF_BILL_CODE       || ' - '|| F.DX_NAME    ELSE NULL END) AS CLM_DX_1
				,MAX(CASE WHEN PXX.LINE = 2 THEN F.REF_BILL_CODE       || ' - '|| F.DX_NAME   ELSE NULL END) AS CLM_DX_2
				,MAX(CASE WHEN PXX.LINE = 3 THEN F.REF_BILL_CODE       || ' - '|| F.DX_NAME   ELSE NULL END) AS CLM_DX_3
				,MAX(CASE WHEN PXX.LINE = 4 THEN F.REF_BILL_CODE       || ' - '|| F.DX_NAME   ELSE NULL END) AS CLM_DX_4
				,MAX(CASE WHEN PXX.LINE = 5 THEN F.REF_BILL_CODE       || ' - '|| F.DX_NAME   ELSE NULL END) AS CLM_DX_5
				,MAX(CASE WHEN PXX.LINE = 6 THEN F.REF_BILL_CODE       || ' - '|| F.DX_NAME   ELSE NULL END) AS CLM_DX_6
                ,MAX(CASE WHEN PXX.LINE = 7 THEN F.REF_BILL_CODE       || ' - '|| F.DX_NAME   ELSE NULL END) AS CLM_DX_7
                ,MAX(CASE WHEN PXX.LINE = 8 THEN F.REF_BILL_CODE       || ' - '|| F.DX_NAME   ELSE NULL END) AS CLM_DX_8
                ,MAX(CASE WHEN PXX.LINE = 9 THEN F.REF_BILL_CODE       || ' - '|| F.DX_NAME   ELSE NULL END) AS CLM_DX_9
                ,MAX(CASE WHEN PXX.LINE = 10 THEN F.REF_BILL_CODE       || ' - '|| F.DX_NAME   ELSE NULL END) AS CLM_DX_10

            FROM HCCBBB.AP_CLAIM_DX PXX INNER JOIN HCCBBB.CLARITY_EDG F ON PXX.DX_ID = F.DX_ID 
        GROUP BY PXX.CLAIM_ID ) DX ON AP_CLAIM.CLAIM_ID = DX.CLAIM_ID	  

LEFT OUTER JOIN ( SELECT  PXX.POS_TYPE_C,  PXX.CLAIM_ID
                 ,MAX(CASE WHEN PXX.LINE = 1 THEN C.PROC_CODE|| '-' || C.PROC_NAME  ELSE NULL END) AS CPT_CODE_1
                 ,MAX(CASE WHEN PXX.LINE = 2 THEN C.PROC_CODE|| '-' || C.PROC_NAME  ELSE NULL END) AS CPT_CODE_2
                 ,MAX(CASE WHEN PXX.LINE = 3 THEN C.PROC_CODE|| '-' || C.PROC_NAME  ELSE NULL END) AS CPT_CODE_3
                 ,MAX(CASE WHEN PXX.LINE = 4 THEN C.PROC_CODE|| '-' || C.PROC_NAME  ELSE NULL END) AS CPT_CODE_4
                 ,MAX(CASE WHEN PXX.LINE = 5 THEN C.PROC_CODE|| '-' || C.PROC_NAME  ELSE NULL END) AS CPT_CODE_5
                 ,MAX(CASE WHEN PXX.LINE = 6 THEN C.PROC_CODE|| '-' || C.PROC_NAME  ELSE NULL END) AS CPT_CODE_6
                 ,MAX(CASE WHEN PXX.LINE = 7 THEN C.PROC_CODE|| '-' || C.PROC_NAME  ELSE NULL END) AS CPT_CODE_7
                 ,MAX(CASE WHEN PXX.LINE = 8 THEN C.PROC_CODE|| '-' || C.PROC_NAME  ELSE NULL END) AS CPT_CODE_8
                 ,MAX(CASE WHEN PXX.LINE = 9 THEN C.PROC_CODE|| '-' || C.PROC_NAME  ELSE NULL END) AS CPT_CODE_9
                 ,MAX(CASE WHEN PXX.LINE = 10 THEN C.PROC_CODE|| '-' || C.PROC_NAME ELSE NULL END) AS CPT_CODE_10
	FROM HCCBBB.AP_CLAIM_PX PXX INNER JOIN HCCBBB.CLARITY_EAP C ON PXX.PROC_ID = C.PROC_ID 

GROUP BY PXX.CLAIM_ID, PXX.POS_TYPE_C ) EAP ON AP_CLAIM.CLAIM_ID = EAP.CLAIM_ID
LEFT OUTER JOIN (SELECT PXX.CLAIM_ID  
                 ,MAX(CASE WHEN PXX.LINE = 1 THEN RV.REVENUE_CODE|| '-' || RV.REVENUE_CODE_NAME  ELSE NULL END) AS REV_CODE_1
                 ,MAX(CASE WHEN PXX.LINE = 2 THEN RV.REVENUE_CODE|| '-' || RV.REVENUE_CODE_NAME  ELSE NULL END) AS REV_CODE_2
                 ,MAX(CASE WHEN PXX.LINE = 3 THEN RV.REVENUE_CODE|| '-' || RV.REVENUE_CODE_NAME  ELSE NULL END) AS REV_CODE_3
                 ,MAX(CASE WHEN PXX.LINE = 4 THEN RV.REVENUE_CODE|| '-' || RV.REVENUE_CODE_NAME  ELSE NULL END) AS REV_CODE_4
                 ,MAX(CASE WHEN PXX.LINE = 5 THEN RV.REVENUE_CODE|| '-' || RV.REVENUE_CODE_NAME ELSE NULL END) AS REV_CODE_5

                 FROM HCCBBB.AP_CLAIM_PX PXX INNER JOIN HCCBBB.CL_UB_REV_CODE RV ON RV.UB_REV_CODE_ID = PXX.UBC_REVENUE_CODE_ID
                GROUP BY PXX.CLAIM_ID ) REV ON AP_CLAIM.CLAIM_ID=REV.CLAIM_ID

WHERE
AP_CLAIM.DATE_RECEIVED between '02/16/2019' and '02/17/2020' and 

(PE.PROC_CODE in ('76376','76377') or (AP_CLAIM_PX_2.PAT_PAY_CMP_CMG = '1005000742;G' or AP_CLAIM_PX_2.PAT_PAY_CMP_CMG = '1005000743;G' or AP_CLAIM_PX_2.PAT_PAY_CMP_CMG = '1005000745;G'))


/*  
Or AP_CLAIM.claim_id IN
(select ubc.claim_id
FROM HCCBBB.AP_CLAIM_PX UBC
LEFT OUTER JOIN HCCBBB.CLARITY_EAP PE2 ON PE2.PROC_ID = AP_CLAIM_PX.PROC_ID
where (PE2.PROC_CODE = '76376' or PE2.PROC_CODE = '76377'))
*/ 


        
 order by CM1.INTERNAL_ID, PAT.PAT_MRN_ID, AP_CLAIM_PX_2.LINE asc;

Select allOpen in new window

Your first and third requirements appear to be contradictory:
  "All claims that contain a service line with either 76376 or 76377 …"
  "All claims that contain ONLY a service line with either 76376 or 76377 and no other service lines present."

Maybe that is because we don't have a complete picture of your table structure?

Please tell us:
1. Which table the service_line value is/are in.
2. Can there be multiple records for the same claim_id with different service_line code?
3. Which columns (and/or combinations of columns) are unique in each of the tables in this query.

We don't know your application or your table and index structures or your data, so for us to be able to help you, you have to give us enough information to understand some of these key aspects of your system.

@culwatrnca11,
You keep sending sql code, which is apparently not giving what you want. All of the responders have asked for more detail about the table structure and data, so they can provide you with code that will get you want. All the responders can do is guess what you need at this point.

slightwv,

Your suggestion actually solved my issue. You had helped me before on a similar issue. I went back and looked at that solution and was able to modify to work with this solution. Thank you again.