Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Wed, 02 Sep 2009 @ 19:02:30 GMT


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


Subj:   Re: Subqueries Workaround
 
From:   Leslie, Quincy

I like #3 best:

Try #1:

     Select
     A.patient_name
     ,A.Patient_age
     ,A.Patient_weight
     ,B.CountHeprin
     ,C.CountWarfarin
     FROM
     Patient A
     ,(Select Count(*) AS CountHeprin
     from drug_taken WHERE drug_name="Heprin" and patient_id=10)B
     ,(Select Count(*) AS CountWarfarin from drug_taken
     WHERE drug_name="Warfarin" and patient_id=10) C
     WHERE
     A.patient_id=10

It runs, but it may not provide the output format you desire.

- or try #2 (better formatting) -

     Select
     A.patient_name
     ,A.Patient_age
     ,A.Patient_weight
     ,B.CountHeprin
     ,C.CountWarfarin
     ,A.patient_id
     FROM
     Patient A
     ,(Select patient_id, Count(*) AS CountHeprin
     from drug_taken WHERE drug_name="Heprin" group by 1)B
     ,(Select patient_id, Count(*) AS CountWarfarin from drug_taken
     WHERE drug_name="Warfarin" group by 1) C
     WHERE
     A.patient_id=10 AND
     A.patient_id=B.patient_id AND
     A.patient_id=C.patient_id

- or try #3 -

     Select
     A.patient_name
     ,A.Patient_age
     ,A.Patient_weight
     ,B.drug_name
     ,B.DRUG_COUNT
     ,A.patient_id
     FROM
     Patient A
     ,(SEL drug_name, count(*) AS DRUG_COUNT
     FROM drug_taken
     WHERE
     drug_name IN ('Heprin','Warfarin') AND
     patient_id=10
     GROUP BY 1) B
     WHERE
     A.patient_id=10 AND
     A.patient_id=B.patient_id

All Untested...



     
  <Prev Next>   <<First <Prev Next> Last>>  
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 15 Jun 2023