Solved

Problem with Case Statement

Posted on 2010-09-03
14
484 Views
Last Modified: 2012-05-10
Hi,

  I have the following query.
   For this ID, i have PHYSICAL_ABUSE_SW = 'Y' and DRUGEXPOSED_NEWBORN_SW = 'Y'
   But from my select, i get only "Physical Abuse"..
   I'm expecting "Neglect" to be picked as a second row, but it's not picking.
   Can someone kindly help?
 
select
(case 
 when 
  (SUSPICIOUS_DEATH_SW ='Y' OR
               NON_ACCIDENTAL_SW = 'Y' OR
               INJURY_INCONSISTENT_SW = 'Y' OR
               INJURY_SUSPICIOUS_SW = 'Y' OR
               CHILD_TOXIC_CHEMICALS_SW = 'Y' OR
               CAREGIVER_ACTION_SW = 'Y' OR
               PHYSICAL_ABUSE_SW = 'Y'
   )           
 THEN 'Physical Abuse' 
When (SEXUAL_MOLESTATION_SW ='Y' OR
               SEXUAL_ACT_SIBLINGS_SW ='Y' OR
               SEXUAL_EXPLOIT_CAREGIVER_SW ='Y' OR
               SEXUAL_PHY_BEH_SUS_ABUSE_SW ='Y'  OR
               SEXUAL_ABUSE_SW = 'Y'
               )
     THEN 'Sexual Abuse'
When   ( DEATH_NEGLECT_SW ='Y' OR
               NON_ORGANIC_FAILURE_SW = 'Y' OR
               MALNUTRITION_SW = 'Y' OR
               UNSAFE_HOME_SW = 'Y' OR
               INADEQUATE_CLOTHING_SW = 'Y' OR
               INADEQUATE_SUPERVISION_SW = 'Y' OR
               DISCHARGED_FACILITY_SW = 'Y' OR
               CAREGIVER_INTERVENE_SW = 'Y' OR
               MAL_NEG_CHILD_ABANDONED_SW = 'Y' OR
               CHILD_DISABLED_SW = 'Y' OR
               CHILD_LEFTALONE_SW = 'Y' OR
               CHILD_LEFTALONE_LONG_SW = 'Y' OR
               PRIORDEATH_CHILD_SW = 'Y' OR
               DRUGEXPOSED_NEWBORN_SW = 'Y' OR
               SEXUAL_PERPETRATOR_SW = 'Y' OR
               CAREGIVER_IMPAIRMENT_SW = 'Y' OR
               UNREASONABLE_DELAY_SW = 'Y' OR
               NEGLECT_SW = 'Y' OR
               NEGLECT_SD_SW = 'Y'
               )
           Then 'Neglect'
When (CHILD_IMPAIRMENT_FAILURE_SW = 'Y' OR
               mental_injury_sw= 'Y'
               )
      Then 'Mental Injury - Neglect'
When (CHILD_IMPAIRMENT_SW = 'Y' OR
               mental_injury_sw= 'Y')
            Then 'Mental Injury - Abuse'
            
           END
) maltreatment_type
from screening
where screening_id = 9000

Open in new window

0
Comment
Question by:pvsbandi
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
  • 3
  • +1
14 Comments
 
LVL 45

Expert Comment

by:Kent Olsen
ID: 33595930
Hi pvsbandi,

The result of a select statement is a subset of the data that was passed through the filter.  Since no join is performed, the results are limited to the rows in table "screening".  You can not duplicate a row from the source table without joining it something.

Can you describe in a bit more detail what you'd like the results to be?


Kent
0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 33595940
Do you mean to have all of those as separate columns?
select
case 
 when 
  (SUSPICIOUS_DEATH_SW ='Y' OR
               NON_ACCIDENTAL_SW = 'Y' OR
               INJURY_INCONSISTENT_SW = 'Y' OR
               INJURY_SUSPICIOUS_SW = 'Y' OR
               CHILD_TOXIC_CHEMICALS_SW = 'Y' OR
               CAREGIVER_ACTION_SW = 'Y' OR
               PHYSICAL_ABUSE_SW = 'Y'
   )
 THEN 'Physical Abuse' else '' end [Physical Abuse],
case When (SEXUAL_MOLESTATION_SW ='Y' OR
               SEXUAL_ACT_SIBLINGS_SW ='Y' OR
               SEXUAL_EXPLOIT_CAREGIVER_SW ='Y' OR
               SEXUAL_PHY_BEH_SUS_ABUSE_SW ='Y'  OR
               SEXUAL_ABUSE_SW = 'Y'
               )
     THEN 'Sexual Abuse' else '' end [Sexual Abuse],
case When   ( DEATH_NEGLECT_SW ='Y' OR
               NON_ORGANIC_FAILURE_SW = 'Y' OR
               MALNUTRITION_SW = 'Y' OR
               UNSAFE_HOME_SW = 'Y' OR
               INADEQUATE_CLOTHING_SW = 'Y' OR
               INADEQUATE_SUPERVISION_SW = 'Y' OR
               DISCHARGED_FACILITY_SW = 'Y' OR
               CAREGIVER_INTERVENE_SW = 'Y' OR
               MAL_NEG_CHILD_ABANDONED_SW = 'Y' OR
               CHILD_DISABLED_SW = 'Y' OR
               CHILD_LEFTALONE_SW = 'Y' OR
               CHILD_LEFTALONE_LONG_SW = 'Y' OR
               PRIORDEATH_CHILD_SW = 'Y' OR
               DRUGEXPOSED_NEWBORN_SW = 'Y' OR
               SEXUAL_PERPETRATOR_SW = 'Y' OR
               CAREGIVER_IMPAIRMENT_SW = 'Y' OR
               UNREASONABLE_DELAY_SW = 'Y' OR
               NEGLECT_SW = 'Y' OR
               NEGLECT_SD_SW = 'Y'
               )
           Then 'Neglect' else '' end [Neglect],
case When (CHILD_IMPAIRMENT_FAILURE_SW = 'Y' OR
               mental_injury_sw= 'Y'
               )
      Then 'Mental Injury - Neglect' else '' end [Mental Injury - Neglect],
case When (CHILD_IMPAIRMENT_SW = 'Y' OR
               mental_injury_sw= 'Y')
            Then 'Mental Injury - Abuse' else '' end [Mental Injury - Abuse]
, maltreatment_type
from screening
where screening_id = 9000

Open in new window

0
 

Author Comment

by:pvsbandi
ID: 33595967
No, the result should only be one column for the maltreatment_type..
  for  ID =  9000,

   maltreatment_type should be "Physical Abuse" and "neglect".But the first one is only coming out as output.
0
Forrester Webinar: xMatters Delivers 261% ROI

Guest speaker Dean Davison, Forrester Principal Consultant, explains how a Fortune 500 communication company using xMatters found these results: Achieved a 261% ROI, Experienced $753,280 in net present value benefits over 3 years and Reduced MTTR by 91% for tier 1 incidents.

 
LVL 45

Expert Comment

by:Kent Olsen
ID: 33596013
Hi pvsbandi,

The CASE statement is evaluated left to right (or top to bottom) and accepts the FIRST condition that is true.  It may be that several, or even all of the conditions are true for any given set of data, but the first CASE clause that evaluates to TRUE is selected.

If "PHYSICAL_ABUSE_SW" is true, you'll get the 'Physical Abuse' as a result when what you may have wanted is 'Sexual Abuse'.

You might evaluate the priority of the items and order them that way.  Evaluate Death, the Sexual Abuse, then Physical Abuse.


Kent
0
 

Author Comment

by:pvsbandi
ID: 33596040
I want all qualifications to show up on the report..there is no priority here..

  If case doesn't work here, can you please suggest a workaround to display the required result?
0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 33596056
Try this then
select substr(
case 
 when 
  (SUSPICIOUS_DEATH_SW ='Y' OR
               NON_ACCIDENTAL_SW = 'Y' OR
               INJURY_INCONSISTENT_SW = 'Y' OR
               INJURY_SUSPICIOUS_SW = 'Y' OR
               CHILD_TOXIC_CHEMICALS_SW = 'Y' OR
               CAREGIVER_ACTION_SW = 'Y' OR
               PHYSICAL_ABUSE_SW = 'Y'
   )
 THEN ',Physical Abuse' else '' end +
case When (SEXUAL_MOLESTATION_SW ='Y' OR
               SEXUAL_ACT_SIBLINGS_SW ='Y' OR
               SEXUAL_EXPLOIT_CAREGIVER_SW ='Y' OR
               SEXUAL_PHY_BEH_SUS_ABUSE_SW ='Y'  OR
               SEXUAL_ABUSE_SW = 'Y'
               )
     THEN ',Sexual Abuse' else '' end +
case When   ( DEATH_NEGLECT_SW ='Y' OR
               NON_ORGANIC_FAILURE_SW = 'Y' OR
               MALNUTRITION_SW = 'Y' OR
               UNSAFE_HOME_SW = 'Y' OR
               INADEQUATE_CLOTHING_SW = 'Y' OR
               INADEQUATE_SUPERVISION_SW = 'Y' OR
               DISCHARGED_FACILITY_SW = 'Y' OR
               CAREGIVER_INTERVENE_SW = 'Y' OR
               MAL_NEG_CHILD_ABANDONED_SW = 'Y' OR
               CHILD_DISABLED_SW = 'Y' OR
               CHILD_LEFTALONE_SW = 'Y' OR
               CHILD_LEFTALONE_LONG_SW = 'Y' OR
               PRIORDEATH_CHILD_SW = 'Y' OR
               DRUGEXPOSED_NEWBORN_SW = 'Y' OR
               SEXUAL_PERPETRATOR_SW = 'Y' OR
               CAREGIVER_IMPAIRMENT_SW = 'Y' OR
               UNREASONABLE_DELAY_SW = 'Y' OR
               NEGLECT_SW = 'Y' OR
               NEGLECT_SD_SW = 'Y'
               )
           Then ',Neglect' else '' end +
case When (CHILD_IMPAIRMENT_FAILURE_SW = 'Y' OR
               mental_injury_sw= 'Y'
               )
      Then ',Mental Injury - Neglect' else '' end +
case When (CHILD_IMPAIRMENT_SW = 'Y' OR
               mental_injury_sw= 'Y')
            Then ',Mental Injury - Abuse' else '' end
, 2) maltreatment_type
from screening
where screening_id = 9000

Open in new window

0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 33596064
oops.. use this instead
select substr(
case 
 when 
  (SUSPICIOUS_DEATH_SW ='Y' OR
               NON_ACCIDENTAL_SW = 'Y' OR
               INJURY_INCONSISTENT_SW = 'Y' OR
               INJURY_SUSPICIOUS_SW = 'Y' OR
               CHILD_TOXIC_CHEMICALS_SW = 'Y' OR
               CAREGIVER_ACTION_SW = 'Y' OR
               PHYSICAL_ABUSE_SW = 'Y'
   )
 THEN ',Physical Abuse' else '' end ||
case When (SEXUAL_MOLESTATION_SW ='Y' OR
               SEXUAL_ACT_SIBLINGS_SW ='Y' OR
               SEXUAL_EXPLOIT_CAREGIVER_SW ='Y' OR
               SEXUAL_PHY_BEH_SUS_ABUSE_SW ='Y'  OR
               SEXUAL_ABUSE_SW = 'Y'
               )
     THEN ',Sexual Abuse' else '' end ||
case When   ( DEATH_NEGLECT_SW ='Y' OR
               NON_ORGANIC_FAILURE_SW = 'Y' OR
               MALNUTRITION_SW = 'Y' OR
               UNSAFE_HOME_SW = 'Y' OR
               INADEQUATE_CLOTHING_SW = 'Y' OR
               INADEQUATE_SUPERVISION_SW = 'Y' OR
               DISCHARGED_FACILITY_SW = 'Y' OR
               CAREGIVER_INTERVENE_SW = 'Y' OR
               MAL_NEG_CHILD_ABANDONED_SW = 'Y' OR
               CHILD_DISABLED_SW = 'Y' OR
               CHILD_LEFTALONE_SW = 'Y' OR
               CHILD_LEFTALONE_LONG_SW = 'Y' OR
               PRIORDEATH_CHILD_SW = 'Y' OR
               DRUGEXPOSED_NEWBORN_SW = 'Y' OR
               SEXUAL_PERPETRATOR_SW = 'Y' OR
               CAREGIVER_IMPAIRMENT_SW = 'Y' OR
               UNREASONABLE_DELAY_SW = 'Y' OR
               NEGLECT_SW = 'Y' OR
               NEGLECT_SD_SW = 'Y'
               )
           Then ',Neglect' else '' end ||
case When (CHILD_IMPAIRMENT_FAILURE_SW = 'Y' OR
               mental_injury_sw= 'Y'
               )
      Then ',Mental Injury - Neglect' else '' end ||
case When (CHILD_IMPAIRMENT_SW = 'Y' OR
               mental_injury_sw= 'Y')
            Then ',Mental Injury - Abuse' else '' end
, 2) maltreatment_type
from screening
where screening_id = 9000

Open in new window

0
 
LVL 45

Expert Comment

by:Kent Olsen
ID: 33596080
So if Sexual abuse and Physical abuse are found, show both in the same column?

Just concatenate the values returned by each CASE statement.


Kent

select
(case 
 when 
  (SUSPICIOUS_DEATH_SW ='Y' OR
               NON_ACCIDENTAL_SW = 'Y' OR
               INJURY_INCONSISTENT_SW = 'Y' OR
               INJURY_SUSPICIOUS_SW = 'Y' OR
               CHILD_TOXIC_CHEMICALS_SW = 'Y' OR
               CAREGIVER_ACTION_SW = 'Y' OR
               PHYSICAL_ABUSE_SW = 'Y'
   )           
 THEN 'Physical Abuse ' ELSE '' END ||
 WHEN (SEXUAL_MOLESTATION_SW ='Y' OR
               SEXUAL_ACT_SIBLINGS_SW ='Y' OR
               SEXUAL_EXPLOIT_CAREGIVER_SW ='Y' OR
               SEXUAL_PHY_BEH_SUS_ABUSE_SW ='Y'  OR
               SEXUAL_ABUSE_SW = 'Y'
               )
     THEN 'Sexual Abuse ' ELSE '' END ||
 CASE WHEN   ( DEATH_NEGLECT_SW ='Y' OR
               NON_ORGANIC_FAILURE_SW = 'Y' OR
               MALNUTRITION_SW = 'Y' OR
               UNSAFE_HOME_SW = 'Y' OR
               INADEQUATE_CLOTHING_SW = 'Y' OR
               INADEQUATE_SUPERVISION_SW = 'Y' OR
               DISCHARGED_FACILITY_SW = 'Y' OR
               CAREGIVER_INTERVENE_SW = 'Y' OR
               MAL_NEG_CHILD_ABANDONED_SW = 'Y' OR
               CHILD_DISABLED_SW = 'Y' OR
               CHILD_LEFTALONE_SW = 'Y' OR
               CHILD_LEFTALONE_LONG_SW = 'Y' OR
               PRIORDEATH_CHILD_SW = 'Y' OR
               DRUGEXPOSED_NEWBORN_SW = 'Y' OR
               SEXUAL_PERPETRATOR_SW = 'Y' OR
               CAREGIVER_IMPAIRMENT_SW = 'Y' OR
               UNREASONABLE_DELAY_SW = 'Y' OR
               NEGLECT_SW = 'Y' OR
               NEGLECT_SD_SW = 'Y'
               )
           Then 'Neglect ' ELSE '' END ||
 CASE When (CHILD_IMPAIRMENT_FAILURE_SW = 'Y' OR
               mental_injury_sw= 'Y'
               )
      Then 'Mental Injury - Neglect ' ELSE '' END ||
 CASE When (CHILD_IMPAIRMENT_SW = 'Y' OR
               mental_injury_sw= 'Y')
            Then 'Mental Injury - Abuse' ELSE '' END 

) maltreatment_type
from screening
where screening_id = 9000

Open in new window

0
 
LVL 45

Expert Comment

by:Kent Olsen
ID: 33596086


:)
0
 

Author Comment

by:pvsbandi
ID: 33596204
Thanks, but this is not what i want..
   OK, let me try to rephrase.
  My original Data :
    ID                     PHYSICAL_ABUSE_SW  DRUGEXPOSED_NEWBORN_SW
    9000                             Y                                          Y

   Desiired Output :
        ID                              Maltreatment_type
       9000                            Physical Abuse
       9000                             Neglect.

Hope, i'm not confusing you.
 
0
 
LVL 45

Accepted Solution

by:
Kent Olsen earned 250 total points
ID: 33596285
Hi pvs,

That goes back to my statement that you can not produce multiple rows of output from a single row of data.


But it can be done.   See below.  (Actually, the right way to do this is with recursive SQL.)


Kent

SELECT screening_id, 
  CASE WHEN idx = 1 AND
           (SUSPICIOUS_DEATH_SW ='Y' OR
               NON_ACCIDENTAL_SW = 'Y' OR
               INJURY_INCONSISTENT_SW = 'Y' OR
               INJURY_SUSPICIOUS_SW = 'Y' OR
               CHILD_TOXIC_CHEMICALS_SW = 'Y' OR
               CAREGIVER_ACTION_SW = 'Y' OR
               PHYSICAL_ABUSE_SW = 'Y')           
       THEN 'Physical Abuse '
  CASE WHEN idx = 2 AND
           (SEXUAL_MOLESTATION_SW ='Y' OR
               SEXUAL_ACT_SIBLINGS_SW ='Y' OR
               SEXUAL_EXPLOIT_CAREGIVER_SW ='Y' OR
               SEXUAL_PHY_BEH_SUS_ABUSE_SW ='Y'  OR
               SEXUAL_ABUSE_SW = 'Y')
      THEN 'Sexual Abuse '
 CASE WHEN idx = 3 AND
           (DEATH_NEGLECT_SW ='Y' OR
               NON_ORGANIC_FAILURE_SW = 'Y' OR
               MALNUTRITION_SW = 'Y' OR
               UNSAFE_HOME_SW = 'Y' OR
               INADEQUATE_CLOTHING_SW = 'Y' OR
               INADEQUATE_SUPERVISION_SW = 'Y' OR
               DISCHARGED_FACILITY_SW = 'Y' OR
               CAREGIVER_INTERVENE_SW = 'Y' OR
               MAL_NEG_CHILD_ABANDONED_SW = 'Y' OR
               CHILD_DISABLED_SW = 'Y' OR
               CHILD_LEFTALONE_SW = 'Y' OR
               CHILD_LEFTALONE_LONG_SW = 'Y' OR
               PRIORDEATH_CHILD_SW = 'Y' OR
               DRUGEXPOSED_NEWBORN_SW = 'Y' OR
               SEXUAL_PERPETRATOR_SW = 'Y' OR
               CAREGIVER_IMPAIRMENT_SW = 'Y' OR
               UNREASONABLE_DELAY_SW = 'Y' OR
               NEGLECT_SW = 'Y' OR
               NEGLECT_SD_SW = 'Y')
      Then 'Neglect '
 CASE When idx = 4 AND
           (CHILD_IMPAIRMENT_FAILURE_SW = 'Y' OR
               mental_injury_sw= 'Y')
      Then 'Mental Injury - Neglect '
 CASE When idx = 5 AND
           (CHILD_IMPAIRMENT_SW = 'Y' OR
               mental_injury_sw= 'Y')
      Then 'Mental Injury - Abuse' 
      ELSE ''
  END maltreatment_type
from
(
  VALUES (1), (2), (3), (4), (5)
) t0 (idx)
CROSS JOIN screening
where screening_id = 9000
  and maltreatment_type <> ''
ORDER BY screening_id, idx;

Open in new window

0
 
LVL 37

Assisted Solution

by:momi_sabag
momi_sabag earned 250 total points
ID: 33596304
you can try something like

select ID, 'Physical Abuse'
from your_table
where physical_abuse_sw = 'Y'

union all

select ID, 'Neglected'
from your_table
where drugexposed_newborn_sw = 'Y'

order by 1
0
 

Author Closing Comment

by:pvsbandi
ID: 33597041
Thanks mates! I borrowed from your ideas and figured out a way..
0
 
LVL 45

Expert Comment

by:Kent Olsen
ID: 33601109
Hi momi,

I've been writing a LOT of recursive SQL lately and am obviously showing its effects.

In this case, I think that your idea of using 5 queries and UNION ALL may be better.


Kent
0

Featured Post

Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

November 2009 Recently, a question came up in the DB2 forum regarding the date format in DB2 UDB for AS/400.  Apparently in UDB LUW (Linux/Unix/Windows), the date format is a system-wide setting, and is not controlled at the session level.  I'm n…
Recursive SQL in UDB/LUW (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question