Subqueries Workaround

Database
Fan

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
3 REPLIES
Teradata Employee

Re: Subqueries Workaround

That query doesn't appear to be syntactically valid. Perhaps you intended to write "AND" before "patient_id-10". With that correction, I believe the query would execute correctly on Teradata 13.0, which has enhanced support for scalar subqueries.
Enthusiast

Re: Subqueries Workaround


Select patient_name
Patient_age,
Patient_weight
P.CountWarfarin,
P.CountHeprin
from patient
left join
(select patient_id,sum(case when drug_name = 'Warfarin' then 1 else 0 end) CountWarfarin,
sum(case when drug_name = 'Heprin' then 1 else 0 end) CountHeprin from drug_taken where patient_id = 10 group by 1) P
on patient.patient_id=p.Patient_id
and patient_id=10
Teradata Employee

Re: Subqueries Workaround

This as "scalar subquery", unless you are using TD13, it won't work on previous releases.

btw you can use product join to do it :

Select P.patient_name,
P.Patient_age,
P.Patient_weight,
A.CountWarfarin,
B.CountHeprin
FROM patient P,
(Select Count(*) CountWarfarin from drug_taken WHERE drug_name='Warfarin' where patient_id=10) A ,
(Select Count(*) CountHeprin from drug_taken WHERE drug_name='Heprin' where patient_id=10) B
WHERE P.patient_id=10

or if you want to make it more generic ( but a litle bit slower unless you define a NUSI on drug_name and another one on patient_id
(i assume that patient_id is present in the drug_taken table )

Select P.patient_name,
P.Patient_age,
P.Patient_weight,
A.CountWarfarin,
B.CountHeprin
FROM patient P,
(Select patient_id , Count(*) CountWarfarin from drug_taken WHERE drug_name='Warfarin' group by 1 ) A ,
(Select patient_id , Count(*) CountHeprin from drug_taken WHERE drug_name='Heprin' group by 1 ) B
WHERE P.patient_id=10
and P.Patient_id = A.Patient_id
And P.Patient_id = b.Patient_id