0
188
Question Christine Nyamu · Oct 9, 2024

SQL Query Help

#InterSystems IRIS for Health #SQL

Hello all,

I need help with coming up with a SQL query that pulls only one value. I have a case where two providers share the exact same name. Each has a different NPI number and IdentityTypeId. I tried the below query - output is also below. 

SELECT *                        
FROM PhysTable                        
WHERE ProviderName = 'DOE, JOE' AND Type = 'NPI'                        
                        
UNION                        
                        
SELECT *                        
FROM PhysTable                        
WHERE IdentityId = '345678'                        
 

Output

ID Type IDType IdentityId IdentityTypeId ProvId ProviderName ProviderType
292||1001 NPI NPI 12345 1001 9242 DOE, JOE Nurse
252||1001 NPI NPI 56785 1001 8252 DOE, JOE Doctor
252||61 NUM EMPLOYEE NUMBER 345678 61 8252 DOE, JOE Doctor

What I want is the IdentityId highlighted in red that is "56785" that is Type NPI. The ProvId and ProviderName both come in the HL7 message but IdentityId etc. do not. Any ideas on how I can do this? Thanks. 

Editing to add: we do get the IdentityId of "345678" of Type NUM but we do not get the IdentityId of "12345" or "345678" of Type NPI.

Product version: IRIS 2022.2
Discussion (18)2
Log in or sign up to continue

Comments

Chris Stewart · Oct 9, 2024

Not sure I am quite understanding the ask, but I think you want something like

SELECT *                        
FROM PhysTable                        
WHERE ProviderName = 'DOE, JOE' AND Type = 'NPI' AND ProvId =8252
0
Christine Nyamu  Oct 9, 2024 to Chris Stewart

@Chris Stewart I initially marked this as the correct answer but I just realized that was incorrect. We do not get the ProvId "8252" in the message, we do get the IdentityId "345678"

0
David Hockenbroch  Oct 9, 2024 to Christine Nyamu

So you get the IdentityId and the ProviderName, and you know the Type, and you want to write a query to get the ProvId?

0
David Hockenbroch  Oct 9, 2024 to Christine Nyamu

I think I'm confused. In your previous comment you said "We do not get the ProvId "8252" in the message, we do get the IdentityId "345678"." So I thought you got the IdentityId in the message.

0
Christine Nyamu  Oct 9, 2024 to David Hockenbroch

@David Hockenbroch 
I realize why I confused you, sorry. So YES we do get the IdentityId of "345678" of Type NUM but we do not get the IdentityId of "12345" or "345678" of Type NPI. 

0
Chris Stewart  Oct 9, 2024 to David Hockenbroch

I am very confused about what the inputs are, and what the desired output is, but in that case a subquery would be needed

SELECT *                      
FROM PhysTable                        
WHERE ProviderName = 'DOE, JOE' AND Type = 'NPI' 
AND ProvId =(
             Select ProvId 
             from PhysTable 
             where ProviderName = 'DOE,JOE' 
              AND Type='EMPLOYEE NUMBER' 
              and IdentityId = 345678
            )
0
Christine Nyamu  Oct 9, 2024 to Chris Stewart

@Chris Stewart 
I have updated my post and hopefully this gives some more clarity. 

I did try your suggestion with a few minor adjustments to the ones in bold but it did not pull anything unfortunately. 

SELECT *                      
FROM PhysTable                        
WHERE ProviderName = 'DOE, JOE' AND Type = 'NPI' 
AND ProvId =(
             Select ProvId 
             from PhysTable 
             where ProviderName = 'DOE,JOE' 
              AND Type='NPI' 
              and IdentityId = '345678'
            )
0
Chris Stewart  Oct 9, 2024 to Christine Nyamu

But 345678 is not of Type NPI, its an Employee_Number in your output above.  Restricting the subquery to NPI will return nothing for that number.   If you change the subquery to the correct Type restriction,  i would expect this to work

0
Chris Stewart  Oct 9, 2024 to Chris Stewart

I see my mistake above, mixing Type and IdType.  Either 

IDTYPE = 'EMPLOYEE_NUMBER'

OR

TYPE ='NUM'

should give you results 

0
Christine Nyamu  Oct 9, 2024 to Christine Nyamu

Nevermind my question... just realized there was something else wrong with my class. Thanks

0
David Hockenbroch · Oct 9, 2024

Do you mean you want to know how to get just that column?

SELECT IdentityId
FROM PhysTable
WHERE ProviderName = 'DOE, JOE' AND type = 'NPI' AND ProvID = '8252'

Or if this is in SQL embedded in ObjectScript:

&sql(SELECT IdentityId INTO :IdentityId FROM PhysTable WHERE ProviderName = 'DOE, JOE' AND type = 'NPI' AND ProvId = '8252')

That will get the Identity ID and put it into the variable IdentityId for you to use in further code.

0
David Hockenbroch  Oct 9, 2024 to Christine Nyamu

What if you just do SELECT IdentityId FROM PhysTable? Do you get a column, or nothing?

0
Christine Nyamu  Oct 9, 2024 to David Hockenbroch

@David Hockenbroch 
When I run 

SELECT IdentityId                       
FROM PhysTable                        
WHERE ProviderName = 'DOE, JOE' AND Type = 'NPI'    

I get two rows

IdentityId
56785
345678
0
David Hockenbroch  Oct 9, 2024 to Christine Nyamu

If you don't have any other criteria you can use in your where clause, that will happen. Based on the data you've shown us, you'd need either ProvId or ProviderType to get it down to 1 row.

If you just want the first row returned and don't really care which one it is, you could do

SELECT TOP 1 IdentityId 
FROM PhysTable 
WHERE ProviderName = 'DOE, JOE' AND Type = 'NPI' 
ORDER BY IdentityId

But that's only if you always want the lower number, and I doubt that's the case, right?

0