CODEWITHSUNDEEP
subquerysnowflake-cloud-data-platformsnowflake-schema
AdhiHari
AdhiHari

Reputation: 5

Snowflake - Getting Invalid argument types for function '=': error

I am trying to execute subquery to find physcian_id who have done 'Procedure' for the patient.

Below are the table referred in the query #event category table

EVENT_NAME            CATEGORY

Chemotherapy              Procedure
Radiation             Procedure
Immunosuppressants    Prescription
BTKI                      Prescription
Biopsy                    Test

#patient_treatment table

PATIENT_ID  EVENT_NAME           PHYSICIAN_ID
1       Radiation            1000
2       Chemotherapy         2000
1       Biopsy           1000
3       Immunosuppressants   2000
4       BTKI             3000
5       Radiation            4000
4       Chemotherapy     2000
1       Biopsy           5000
6       Chemotherapy     6000

#physician_speciality table

PHYSICIAN_ID    SPECIALITY
1000            Radiologist
2000            Oncologist
3000            Hermatologist
4000            Oncologist
5000            Pathologist
6000            Oncologist

#query tried to find the physcian who have done 'Procedure'

select ph.physician_id from physician_speciality ph where ph.physician_id in (select pt.event_name, pt.physician_id from patient_treatment pt where pt.event_name in (select ec.event_name, ec.category from event_category ec where ec.event_name = pt.event_name and ec.category='Procedure'));

But getting below error :

SQL compilation error
Invalid argument types for function '=': (VARCHAR(50), ROW(VARCHAR(50), VARCHAR(100)))

Upvotes: 0

Views: 410

Answers (1)

Gokhan Atil
Gokhan Atil

Reputation: 10039

Can you try this one?

select ph.physician_id from physician_speciality ph where ph.physician_id in (select pt.physician_id 
from patient_treatment pt where pt.event_name in 
(select ec.event_name from event_category ec where ec.event_name = pt.event_name and ec.category='Procedure'));

You tried to compare one field to two fields:

ph.physician_id in (select pt.event_name, pt.physician_id

Upvotes: 1

Related Questions