I was wondering if select counts within selects are valid in Teradata? e.g.
CREATE VOLATILE TABLE #AllMems, no fallback, no log ( MbrshpNbr INTEGER ) PRIMARY INDEX (MbrshpNbr) ON COMMIT PRESERVE ROWS;
-- All mems from previous month INSERT INTO #AllMems SELECT MbrshpNbr FROM D_EDW_SEM_PRD01_V.AncClmItmFACT a11 JOIN D_EDW_SEM_PRD01_V.EDIChnlDIM EDI ON a11.EDIChnlId = EDI.EDIChnlId JOIN D_EDW_SEM_PRD01_V.MbrshpDIM MD ON a11.MbrshpId = MD.MbrshpId WHERE EDI.EDIDvceNbr IN ('OMSCL','SMARTPHN') AND ClmLodgDt >= DATE '2014-10-01' AND ClmLodgDt <= DATE '2014-10-31' --AND MD.MbrshpNbr = 20691660 AND BenPaidAmt > 0 GROUP BY MbrshpNbr HAVING SUM(BenPaidAmt) > 0;
SELECT AM2.MbrshpNbr, (SELECT COUNT(DISTINCT ACIF2.ClmLodgDt) FROM D_EDW_SEM_PRD01_V.MbrshpDIM MD2 JOIN D_EDW_SEM_PRD01_V.AncClmItmFACT ACIF2 ON ACIF2.MbrshpId = MD2.MbrshpId JOIN D_EDW_SEM_PRD01_V.EDIChnlDIM EDI2 ON ACIF2.EDIChnlId = EDI2.EDIChnlId WHERE MD2.MbrshpNbr = AM2.MbrshpNbr AND ACIF2.ClmLodgDt >= '2014-05-01' AND ACIF2.ClmLodgDt <= '2014-10-31' AND ACIF2.BenPaidAmt > 0 AND EDI2.EDIDvceNbr IN ('OMSCL','SMARTPHN')) AS TotalClaimDays FROM #AllMems AM2 --WHERE AM.MbrshpNbr = 20691660 GROUP BY AM2.MbrshpNbr ORDER BY AM2.MbrshpNbr;
DROP TABLE #AllMems;
Uncommenting either of the commented lines returns the result I am after (i.e. 1), but running the code as is returns a different result for MbrshpNbr = 20691660 (i.e. 28). The fact different results are returned leads me to believe I should be using derived tables such as pointed out by Dieter here, but was wondering if this is necessary?