Subqueries Workaround

Database
N/A

Subqueries Workaround

Hi,

I noticed that I can’t do sub queries in Teradata.

For example:

Select patient_name

Patient_age,

Patient_weight,

(Select Count(*) from drug_taken WHERE drug_name=”Warfarin” patient_id=10) CountWarfarin,

(Select Count(*) from drug_taken WHERE drug_name=”Heprin” patient_id=10) CountHeprin

FROM patient

WHERE patient_id=10

So what is the workaround for this? Where I don’t have to create a stored procedure.

Thank you,

-Tesh
2 REPLIES

Re: Subqueries Workaround

It's not that Sub-queries are not allowed in Teradata. It has to be handelled properly.
Check - if the following query is giving the same result or not.

SELECT
PATIENT_NAME
,PATIENT_AGE
,PATIENT_WEIGHT
,COUNTWARFARIN.CNT1
,COUNTHEPRIN.CNT2

FROM
PATIENT
,(SELECT COUNT(*) CNT1 FROM DRUG_TAKEN WHERE DRUG_NAME = 'WARFARIN' AND PATIENT_ID=10) COUNTWARFARIN
,(SELECT COUNT(*) CNT2 FROM DRUG_TAKEN WHERE DRUG_NAME = 'HEPRIN' AND PATIENT_ID=10) COUNTHEPRIN
WHERE
PATIENT_ID=10
;

Other Query Experts need to interfere and answer - Cummon!!!

Regards,
ANIMESH DUTTA
N/A

Re: Subqueries Workaround

The above reply is quite a alternative, else a volatile table can be used here.