reate table and insert data into Teradata database from MS SQL Server

[Mindgamez]

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

 

[al1024]

Why don't you use JOIN clause.
This is Teradata, not O**e or My***. JOIN is efficient !

SELECT  ptn.patient_name
    ,   ptn.Patient_age
    ,   ptn.Patient_weight
    ,   SUM(CASE WHEN drug_name = "Warfarin" THEN 1 ELSE 0 END)  AS CountWarfarin
    ,   SUM(CASE WHEN drug_name = "Heprin" THEN 1 ELSE 0 END)  AS CountHeprin
FROM    patient     AS ptn
    LEFT JOIN
        drug_taken  AS drg
        ON  ptn.patient_id = drg.patient_id
        AND drg.drug_name IN ("Heprin", "Warfarin")
WHERE   ptn.patient_id = 10
;