do someone know a solution for a count based on previous case statement ?
here is what i mean in a query, does not work acutally
SELECT
CASE
WHEN field1 IN ('a','b','c') THEN 'MATCH1'
WHEN field2 IN ('d','e','f') THEN 'MATCH2'
ELSE 'NOMATCH' END AS MATCHFIELD,
(SELECT COUNT(*) FROM TABLE2 WHERE field3=MATCHFIELD) AS SUBCOUNT
FROM TABLE1
i am on db2 v9.X. i have tryed HAVING but could not find a viable solution
regards
Wrap the SELECT with the case expression up in a derived table:
select MATCHFIELD,
(SELECT COUNT(*) FROM TABLE2 WHERE field3=MATCHFIELD) AS SUBCOUNTSELECT
from
(
select
CASE
WHEN field1 IN ('a','b','c') THEN 'MATCH1'
WHEN field2 IN ('d','e','f') THEN 'MATCH2'
ELSE 'NOMATCH'
END AS MATCHFIELD
FROM TABLE1
) dt
I would use JOIN and GROUP BY
SELECT MATCHFIELD, COUNT(*)
FROM TABLE2
JOIN
(
SELECT
CASE
WHEN field1 IN ('a','b','c') THEN 'MATCH1'
WHEN field2 IN ('d','e','f') THEN 'MATCH2'
ELSE 'NOMATCH' END AS MATCHFIELD
FROM TABLE1
) t ON field3=t.MATCHFIELD
GROUP BY MATCHFIELD
try this :
with tmp as (
SELECT CASE
WHEN field1 IN ('a','b','c') THEN 'MATCH1'
WHEN field2 IN ('d','e','f') THEN 'MATCH2'
ELSE 'NOMATCH' END AS MATCHFIELD
FROM TABLE1
)
select tmp.*, (SELECT COUNT(*) FROM TABLE2 WHERE field3=MATCHFIELD) AS SUBCOUNT
from tmp
