All Forums Database
frozenshine 7 posts Joined 03/15
10 Mar 2015
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     

ank
Fred 1096 posts Joined 08/04
10 Mar 2015

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))

frozenshine 7 posts Joined 03/15
10 Mar 2015

Thank you so much, it worked perfect!

ank

You must sign in to leave a comment.