I was wondering if select counts within selects are valid in Teradata? e.g.
CREATE VOLATILE TABLE #AllMems, no fallback, no log
PRIMARY INDEX (MbrshpNbr)
ON COMMIT PRESERVE ROWS;
-- All mems from previous month
INSERT INTO #AllMems
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 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?