This doesn't make sense to me - making me crazy - er

Question

Post reply

This doesn't make sense to me - making me crazy - er

krypto69

SSChampion

Points: 13615

December 9, 2025 at 12:36 pm

#4697711

Hi

So I have the below select query that won't return results if I have it this way:

--DOES NOT RETURN--
AND EXISTS (SELECT 1 FROM edi.dbo.PEC_RGT_EDI_834_Inbound_Exception xx 
WHERE (xx.SubscriberNumber = x.subscribernumber OR x.MBI = xx.mbi) 
AND xx.LastDateProcessed>'05/31/2022')

But will return results with this very small change:

--DOES RETURN-- REPLACE ABOVE WITH BELOW
AND EXISTS( SELECT 1 FROM edi.dbo.PEC_RGT_EDI_834_Inbound_Exception xx
WHERE (x.SubscriberNumber = xx.subscribernumber OR MBI = xx.mbi)
AND xx.LastDateProcessed>'05/31/2022')

Here is the statement:

DECLARE @FUTURE DATE
SET @FUTURE = '12/01/2025'
SELECT DISTINCT
   x.LastDateProcessed,
   FileType = 
CONVERT(VARCHAR(20),CASE WHEN x.TradingPartnerID = 'HUMANA_MCR' AND s.EDIFileName LIKE '%REMBX%' THEN 'REMBX'
 WHEN x.TradingPartnerID = 'HUMANA_MCR' AND s.[Action] = 4 THEN 'Daily - Audit'
 WHEN x.TradingPartnerID = 'HUMANA_MCR' AND s.[Action] = 2 THEN 'Daily'
 WHEN x.ExceptionType = 'Audit Findings' THEN 'Audit Findings'
 ELSE IIF(s.[Action] = 2, 'Daily','Audit')
END),
   Rejection = 
IIF( x.Rejection LIKE '%non par pcp%' , 'Non Par PCP; has been on file since ' + CONVERT(VARCHAR,x.FirstDateProcessed,101),x.Rejection),

       x.BenefitContractId,
       x.BenefitBegin,
       x.BenefitEnd,
       x.MBI,
       x.SubscriberNumber,
       x.MemberLastName,
       x.MemberFirstName,
       x.MemberBirthday,
       x.MemberAddress1,
       x.MemberAddress2,
       x.MemberCity,
       x.MemberState,
       x.MemberZip,
       x.MemberPhone,
       Payor = COALESCE(x.employergroup_ud,'Benefit Contract needs to be added'),
       s.ProviderID,
       s.ProviderFirstName,
       s.ProviderLastName
-- ###################################################################
--                  REQUIRED FIELDS FOR TRACKER SYSTEM
-- ###################################################################

,exceptionName = 
CASE WHEN x.TradingPartnerId LIKE 'Humana%' AND x.Rejection NOT LIKE '%non par pcp%' THEN 'Humana Eligibility Exceptions'
WHEN x.TradingPartnerId LIKE 'Humana%' AND x.Rejection LIKE '%non par pcp%' THEN 'Humana Non Par PCP Exceptions'
 WHEN x.TradingPartnerId = 'CP' THEN 'CarePlus Eligibility Exceptions'
 WHEN x.TradingPartnerId LIKE 'Wellcare%' THEN 'WellCare Eligibility Exceptions'
 WHEN x.TradingPartnerId = 'CENTENE' THEN 'Centene Eligibility Exceptions'
 WHEN x.TradingPartnerId = '821023772' THEN 'Devoted Eligibility Exceptions'
 WHEN x.TradingPartnerId = '810726576' THEN 'Solis Eligibility Exceptions'
 WHEN x.TradingPartnerId = 'ULTIMATE_H2962' THEN 'Ultimate Eligibility Exceptions'
 WHEN x.TradingPartnerId = 'ALHEALTH' THEN 'Alignment Eligibility Exceptions'
 WHEN x.TradingPartnerId = '824632870' THEN 'FloridaCare Eligibliity Exceptions'
 WHEN x.TradingPartnerId = '592015694' THEN 'Florida Blue Eligibliity Exceptions'
 WHEN x.TradingPartnerId = 'AMERIHEALTH' THEN 'Amerihealth Eligibliity Exceptions'
 WHEN x.TradingPartnerId = 'DRHCP' THEN 'DHCP Eligibliity Exceptions'
 ELSE 'Unknown Eligibility Exceptions'
END 
,'EDI' exceptionTeam -- exceptionTeam indicates which team is primarily responsible for dealing with the exception. 

--,RTRIM(x.TradingPartnerId) + '/' + x.SubscriberNumber + '/' + convert(VARCHAR(8),x.BenefitBegin,112) exceptionKey -- exceptionKey is the "primary key" of this type of exception. IE: Whatever value uniquely distinguishes this exception "row" from the others.
,CONVERT(VARCHAR(100), x.ID) exceptionKey


-- temp table ##exceptionResults4ABC0D39002845698A493D9FB834C964 is the required name of the final temp table where the stored procedure will output all exception detail data.
-- The tracker refresh process will look for this table when running the procedure in order to load the data into the system.
--INTO ##exceptionResults4ABC0D39002845698A493D9FB834C964

FROM edi.dbo.PEC_RGT_EDI_834_Inbound_Exception x
INNER JOIN Staging_Archive.dbo.staging_import s ON s.EnrollmentId = x.EnrollmentId
WHERE  x.BenefitBegin < @Future
AND x.Rejection NOT LIKE '%Future%'

--DOES NOT RETURN--
AND EXISTS (SELECT 1 FROM edi.dbo.PEC_RGT_EDI_834_Inbound_Exception xx 
WHERE (xx.SubscriberNumber = x.subscribernumber OR x.MBI = xx.mbi) 
AND xx.LastDateProcessed>'05/31/2022')

--DOES RETURN-- REPLACE ABOVE WITH BELOW
AND EXISTS( SELECT 1 FROM edi.dbo.PEC_RGT_EDI_834_Inbound_Exception xx
WHERE (x.SubscriberNumber = xx.subscribernumber OR MBI = xx.mbi)
AND xx.LastDateProcessed>'05/31/2022')


AND x.LOB = 'Medicare'
AND x.Rejection NOT LIKE '%non par pcp%'

I've had a few other devs look at it and no one can figure it out. To make it even odder this statement was working fine for many years..

This topic was modified 2 weeks, 6 days ago by krypto69.

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

ScottPletcher SSC Guru Points: 101223 More actions · Answer 1

Is there an MBI column in table "edi.dbo.PEC_RGT_EDI_834_Inbound_Exception"?

If so, the second query works because it is comparing xx.MBI to itself, which will always be equal (unless MBI is NULL, oc).

x.MBI references the value from an outer query, which apparently is never equal.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

krypto69 SSChampion Points: 13615 More actions · Answer 2

HI Scott

yes, there is an MBI column in table PEC_RGT_EDI_834_Inbound_Exception.

Is there a better way to write this - guess Im still not fully understanding why it works and how I should make the one that doesnt work..work. If that makes sense..?

ScottPletcher SSC Guru Points: 101223 More actions · Answer 3

D'OH, obviously there is an mbi column, since it's directly referenced in the query.

The point here is that, in a subquery, if you reference a column that exists in a table listed in the subquery -- table "xx" in this example -- then by default SQL will use the that column not an outer query column. That is, if you don't specify "x.MBI" then SQL will automatically assume "xx.MBI".

More generally, in a query like this:

SELECT ... FROM dbo.some_table A WHERE EXISTS(SELECT ... FROM dbo.some_table B WHERE col1 = 7)

If col1 exists in table "B", then SQL will use it, otherwise it will A.col1. SQL always tries to use a local value if possible.

Therefore, when you write "MBI = xx.MBI" you're comparing the column to itself.

The reason the query doesn't return results is that no rows with x.MBI = xx.MBI meet the other conditions in the subquery to allow rows to be returned. Apparently the data doesn't match what you expect it to be.

AND EXISTS( SELECT 1 FROM edi.dbo.PEC_RGT_EDI_834_Inbound_Exception xx

WHERE (x.SubscriberNumber = xx.subscribernumber OR x.MBI = xx.mbi)

AND xx.LastDateProcessed>'05/31/2022')

This reply was modified 2 weeks, 6 days ago by ScottPletcher.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

krypto69 SSChampion Points: 13615 More actions · Answer 4

Ah I see the light!

Thank you SO much Scott - great explanation!

Thanks again!