Topic
  • 5 replies
  • Latest Post - ‏2012-12-06T11:20:25Z by SystemAdmin
SystemAdmin
SystemAdmin
535 Posts

Pinned topic case sql

‏2012-12-06T10:09:58Z |
HI all,

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
from merchk

but this is not possible...

Help me.. and excuse me for my bad english (I'm Italian)
Updated on 2012-12-06T11:20:25Z at 2012-12-06T11:20:25Z by SystemAdmin
  • vazymimil
    vazymimil
    8 Posts

    Re: case sql

    ‏2012-12-06T10:44:04Z  
    Hi PabloTo,

    try this:

    
    select f1, 
    
    case count(distinct f3) when 1 then min(f3) 
    
    else 
    'X' end from table1 group by f1
    
  • SystemAdmin
    SystemAdmin
    535 Posts

    Re: case sql

    ‏2012-12-06T10:51:22Z  
    • vazymimil
    • ‏2012-12-06T10:44:04Z
    Hi PabloTo,

    try this:

    <pre class="jive-pre"> select f1, case count(distinct f3) when 1 then min(f3) else 'X' end from table1 group by f1 </pre>
    No it doesn't work, in case of TYPEA sql answered me with ++
  • SystemAdmin
    SystemAdmin
    535 Posts

    Re: case sql

    ‏2012-12-06T10:59:31Z  
    Help
  • vazymimil
    vazymimil
    8 Posts

    Re: case sql

    ‏2012-12-06T11:10:42Z  
    No it doesn't work, in case of TYPEA sql answered me with ++
    That'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'
  • SystemAdmin
    SystemAdmin
    535 Posts

    Re: case sql

    ‏2012-12-06T11:20:25Z  
    • vazymimil
    • ‏2012-12-06T11:10:42Z
    That's strange, I get this result
    <pre class="jive-pre"> F1 Expression CASE TYPEA X TYPEB 1 TYPEC 1 TYPED 1 </pre>

    Did you put something like a dec() around the case? Like this:
    <pre class="jive-pre"> select f1, dec( case count(distinct f3) when 1 then min(f3) else 'X' end , 1, 0) from nfa.pabloto group by f1 </pre>

    Then you have to use a numerical value like 2 instead of 'X'
    Okay, now I unterstand (the problem is f3, because it is dec). May many thanks!