CASE statment inside where clause and corelated subquery ERROR:3707

Database

CASE statment inside where clause and corelated subquery ERROR:3707

SELECT  oute.*

FROM PDP_TMP.EV_SBSCRP_BTS_USG oute

WHERE  

CASE WHEN oute.PROD_TYPE= EVO

THEN oute.TotalDataCount  =  SELECT  MAX (inne.TotalDataCount)

                                                FROM PDP_TMP.EV_SBSCRP_BTS_USG inne

                                                 WHERE inne.SBSCRP_ID =oute.SBSCRP_ID     

ELSE WHEN oute.PROD_TYPE = VFONE

THEN oute.TotalUsage =        SELECT  MAX (inne.TotalUsage)

                                                FROM PDP_TMP.EV_SBSCRP_BTS_USG inne

                                                 WHERE inne.SBSCRP_ID =oute.SBSCRP_ID     

2 REPLIES
Teradata Employee

Re: CASE statment inside where clause and corelated subquery ERROR:3707

WHERE expects a logical expression, but CASE returns either numeric or character.

Use AND/OR to combine the predicates instead. Also put parentheses around your scalar subqueries.

SELECT oute.*

FROM PDP_TMP.EV_SBSCRP_BTS_USG oute

WHERE

(oute.PROD_TYPE= EVO

AND oute.TotalDataCount = (SELECT MAX (inne.TotalDataCount)

    FROM PDP_TMP.EV_SBSCRP_BTS_USG inne

    WHERE inne.SBSCRP_ID =oute.SBSCRP_ID))

OR

(oute.PROD_TYPE = VFONE

AND oute.TotalUsage = (SELECT MAX (inne.TotalUsage)

FROM PDP_TMP.EV_SBSCRP_BTS_USG inne

WHERE inne.SBSCRP_ID =oute.SBSCRP_ID))

Re: CASE statment inside where clause and corelated subquery ERROR:3707

Thank you so much, it worked perfect!