Solved

Problem with Case Statement

Posted on 2010-09-03
14
481 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
  • 6
  • 4
  • 3
  • +1
14 Comments
 
LVL 45

Expert Comment

by:Kdo
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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
LVL 45

Expert Comment

by:Kdo
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:Kdo
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:Kdo
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:
Kdo 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:Kdo
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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

Recursive SQL in UDB/LUW (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
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…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

832 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