|
|
|
Forum Newbie
Group: Forum Members
Last Login: 9/10/2009 3:59:11 PM
Posts: 2,
Visits: 13
|
|
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
|
|
|
|
|
Forum Member
Group: Forum Members
Last Login: 10/30/2009 6:28:19 AM
Posts: 34,
Visits: 117
|
|
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
|
|
|
|
|
Forum Newbie
Group: Forum Members
Last Login: 10/21/2009 4:47:49 PM
Posts: 7,
Visits: 29
|
|
|
The above reply is quite a alternative, else a volatile table can be used here.
|
|
|
|