SQL Query Help

Question

Question

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

Chris Stewart · Oct 9

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

IDTYPE = 'EMPLOYEE_NUMBER'

OR

TYPE ='NUM'

should give you results

0 0

score 0 · Answer 1 · 2024-10-09T10:53:04-04:00

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

score 0 · Answer 2 · 2024-10-09T11:10:59-04:00

@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"

score 0 · Answer 3 · 2024-10-09T11:20:41-04:00

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

score 0 · Answer 4 · 2024-10-09T11:26:17-04:00

Christine Nyamu · Oct 9

@David Hockenbroch Everything else correct but one correction - what I want is the IdentityId

0 0

score 0 · Answer 5 · 2024-10-09T11:29:21-04:00

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.

score 0 · Answer 6 · 2024-10-09T11:40:46-04:00

@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.

score 0 · Answer 7 · 2024-10-09T11:27:27-04:00

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
            )

score 0 · Answer 8 · 2024-10-09T11:46:07-04:00

@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'
            )

score 0 · Answer 9 · 2024-10-09T13:35:28-04:00

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

score 0 · Answer 10 · 2024-10-09T13:47:36-04:00

Christine Nyamu · Oct 9

@Chris Stewart
THANK YOU. This worked!

0 0

score 0 · Answer 11 · 2024-10-09T15:00:25-04:00

Christine Nyamu · Oct 9

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

0 0

score 0 · Answer 12 · 2024-10-09T11:14:38-04:00

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.

score 0 · Answer 13 · 2024-10-09T11:20:50-04:00

Christine Nyamu · Oct 9

@David Hockenbroch I tried this before and it doesn't pull anything unfortunately

0 0

score 0 · Answer 14 · 2024-10-09T11:25:21-04:00

David Hockenbroch · Oct 9

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

0 0

score 0 · Answer 15 · 2024-10-09T11:29:41-04:00

@David Hockenbroch
When I run

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

I get two rows

IdentityId

56785

345678

score 0 · Answer 16 · 2024-10-09T11:38:50-04:00

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?

score 0 · Answer 17 · 2024-10-09T11:43:44-04:00

Christine Nyamu · Oct 9

@David Hockenbroch you are correct. Its quite frustrating as I want that second row.

0 0