• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 495
  • Last Modified:

Problem with Case Statement

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
pvsbandi
Asked:
pvsbandi
  • 6
  • 4
  • 3
  • +1
2 Solutions
 
Kent OlsenData Warehouse Architect / DBACommented:
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
 
cyberkiwiCommented:
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
 
pvsbandiAuthor Commented:
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
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
Kent OlsenData Warehouse Architect / DBACommented:
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
 
pvsbandiAuthor Commented:
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
 
cyberkiwiCommented:
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
 
cyberkiwiCommented:
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
 
Kent OlsenData Warehouse Architect / DBACommented:
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
 
Kent OlsenData Warehouse Architect / DBACommented:


:)
0
 
pvsbandiAuthor Commented:
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
 
Kent OlsenData Warehouse Architect / DBACommented:
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
 
momi_sabagCommented:
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
 
pvsbandiAuthor Commented:
Thanks mates! I borrowed from your ideas and figured out a way..
0
 
Kent OlsenData Warehouse Architect / DBACommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

  • 6
  • 4
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now