Archives of the TeradataForum

Message Posted: Wed, 02 Sep 2009 @ 17:12:30 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Tesh-

It's the same SQL you'd use in any other database:

     Select p.patient_name,
     p.Patient_age,
     p.Patient_weight,
     Sum(case when d.drug_name = 'Warfarin' then 1 else 0 end)
     CountWarfairin,
     Sum(case when d.drug_name = 'Heparin' then 1 else 0 end) CountHeparin
     From patient p
     Left join drug_taken d
     On p.patient_id = d.patient_id
     -- And d.patient_id = 10 /* might need this in case index on patient_id
     and optimizer doesn't' use it */
     Where p.patient_id = 10
     Group by 1,2,3;

     Select p.patient_name,
     p.Patient_age,
     p.Patient_weight,
     D.CountWarfarin,
     D.CountHeparin
     >From patient p
     Inner join
     (select patient_id,
     Sum(case when drug_name = 'Warfarin' then 1 else 0 end) CountWarfairin,
     Sum(case when drug_name = 'Heparin' then 1 else 0 end) CountHeparin
     From drug_taken
     Where patient_id = 10
     Group by 1) d
     On p.patient_id = d.patient_id
     Where p.patient_id = 10;

You can't use subqueries in the select list, but you can use them elsewhere, as with correlated subqueries (although an equivalent query using OLAP instead of a correlation might be faster).

(In your case on the original database, depending on indexing, you might not want to use two scalar subqueries if the optimizer is going to turn that into two separate queries against drug_taken. It might be faster to use either of the above. If it's indexed on patient_id, probably no biggie to hit it twice.)

If I could buy myself a royal title, I'd seriously consider Count Warfarin.

Dave C

	<Prev	Next>		<<First	<Prev	Next>	Last>>