I have this table:
f1 f2 f3
TYPEA 101 1
TYPEA 102 1
TYPEA 103 0
TYPEA 17 1
TYPEB 301 1
TYPEC 302 1
TYPED 201 1
TYPED 202 1
TYPED 203 1
f3 can be only 1 or 0.
I want to know if f3 for the same f1, and if f3 is both 1 and 0 I would a 'X' in return operation:
something like this:
with merchk (status) as( SELECT distinct(f3) FROM table1 WHERE
f1 = 'TYPEA' GROUP BY f3)
select case count(*) when 0 then 'ERROR'
when '1' then status
else 'X' end
but this is not possible...
Help me.. and excuse me for my bad english (I'm Italian)
This topic has been locked.
5 replies Latest Post - 2012-12-06T11:20:25Z by SystemAdmin
Pinned topic case sql
Answered question This question has been answered.
Unanswered question This question has not been answered yet.
Updated on 2012-12-06T11:20:25Z at 2012-12-06T11:20:25Z by SystemAdmin
vazymimil 20000019DS8 Posts
vazymimil 20000019DS8 PostsACCEPTED ANSWER
Re: case sql2012-12-06T11:10:42Z in response to SystemAdminThat's strange, I get this result
F1 Expression CASE TYPEA X TYPEB 1 TYPEC 1 TYPED 1
Did you put something like a dec() around the case? Like this:
select f1, dec( case count(distinct f3) when 1 then min(f3) else 'X' end , 1, 0) from nfa.pabloto group by f1
Then you have to use a numerical value like 2 instead of 'X'