Solved

Problem with Case Statement

Posted on 2010-09-03
14
477 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 45

Expert Comment

by:Kdo
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 45

Expert Comment

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


:)
0
 

Author Comment

by:pvsbandi
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Thanks mates! I borrowed from your ideas and figured out a way..
0
 
LVL 45

Expert Comment

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

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 (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…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

771 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now