Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2012
    Posts
    31

    Unanswered: Case statement using multiple ELSE AND?

    Hi-
    I am more adept at MS Access. That being said, I am trying to rework some Access SQL into DB2 SQL. Here is the literal translation which does not work (error posted after code). Error aside, I'm thinking there is some way to consolidate this but am

    Case
    When TMD.C_LEVEL_CARE In ('NI','AC') And TMD.C_TYPE_AUTH 'INP' And TMD1.C_SERVICE_CAT Not In ('MEH','SUA') Then TMD1.C_SERVICE_CAT
    ELSE
    WHEN TMD.C_LEVEL_CARE In ('NI','AC') And TMD.C_TYPE_AUTH 'INP' And TMD1.C_SERVICE_CAT In ('MEH','SUA')Then 'MHSA'
    ELSE
    WHEN TMD.C_TYPE_AUTH In ('IOP','PDP') Then 'MHSA IOP and PDP'
    ELSE
    WHEN TMD.C_LEVEL_CARE In ('SN','L1','L2','L3','L4') Then 'SNF'
    ELSE
    WHEN TMD.C_LEVEL_CARE In ('SB','TR') Then 'Sub-Acute'
    ELSE
    WHEN TMD.C_LEVEL_CARE ('RE') Then 'Rehab' ELSE 'NA/Other' END As "Type of Day",


    The error at this point:
    Error during Execute
    42601(-104)[IBM][CLI Driver][DB2] SQL0104N An unexpected token "'INP'" was found following "". Expected tokens may include: "( . MICROSECONDS MICROSECOND SECONDS SECOND MINUTES MINUTE". SQLSTATE=42601

    Thank you for any and all suggestions.
    Laura

  2. #2
    Join Date
    Apr 2012
    Posts
    1,035
    Provided Answers: 18
    When asking for help, always give your DB2-server version and fixpack , and your client details.

    The syntax of DB2 CASE does not have "else" after each WHEN clause.

    Take a look at the many samples that IBM provides with the DB2-server installation.

    For example if your DB2-Server runs on Windows then look in C:\Program Files\IBM\SQLLIB\samples\clp

  3. #3
    Join Date
    Jul 2012
    Posts
    31
    I've spent all day looking on the web but am still stuck. I do not have anything here \IBM\SQLLIB\samples\clp to use. I generally am one who searches online before posting to a forum. But here I am again.
    My DB2 Info:
    DB2 9.1 FP2 and DB2 Connect 9.1 FP2

    I now have a lengthy Case statement because I couldn't get an "in" statement to work within this case statement. This has left a few stragglers though which involves pulling in an additional field other than what the case statement is pointing to.
    This is what I have:
    Case TMD.C_LEVEL_CARE
    When 'SB' Then 'Sub-Acute'
    When 'TR' Then 'Sub-Acute'
    When 'SN' Then 'SNF'
    When 'L1' Then 'SNF'
    WHen 'L2' Then 'SNF'
    WHen 'L3' Then 'SNF'
    WHen 'L4' Then 'SNF'
    When 'RE' Then 'Rehab'
    When 'P1' Then 'MHSA IOP and PDP'
    When 'P2' Then 'MHSA IOP and PDP'
    When 'P3' Then 'MHSA IOP and PDP'
    WHEN 'NI' THen TMD1.C_SERVICE_CAT

    Else TMD.C_LEVEL_CARE End AS "Type Of Day",

    I also tried to use a wildcard either with the L or the P so I could just use one line, but that didn't work either.
    What I am left with needing to do is

    When 'AC' And TMD1.C_TYPE_AUTH = 'INP' And TMD1.C_SERVICE_CAT Not In ('MEH','SUA') Then TMD1.C_SERVICE_CAT

    then the above Else statement to finish it off.

    If someone can assist me with making that work I can get the other combinations to work.

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    There are two types of the case expression available in DB2: simple, the one you are trying to use (CASE something WHEN value THEN....) and searched (CASE WHEN <search expression> THEN ...). You need to use the latter:

    Code:
    CASE 
    WHEN TMD.C_LEVEL_CARE = 'AC' And TMD1.C_TYPE_AUTH = 'INP' And TMD1.C_SERVICE_CAT Not In ('MEH','SUA') Then TMD1.C_SERVICE_CAT 
    WHEN TMD.C_LEVEL_CARE = 'SB' Then 'Sub-Acute'
    WHEN TMD.C_LEVEL_CARE = ...
    ELSE...
    END
    ---
    "It does not work" is not a valid problem statement.

  5. #5
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    laurastreng, If you remove all the ELSE except for the last one your statement should work once some things are fixed. n_i fixed one of them in his reply. Going from your first post:

    The first and second WHEN have:

    And TMD.C_TYPE_AUTH 'INP' And

    This should probably be:
    And TMD.C_TYPE_AUTH = 'INP' And

    In the last WHEN you have:

    TMD.C_LEVEL_CARE ('RE') Then

    This should probably be:
    TMD.C_LEVEL_CARE IN ('RE') Then

    Here is a formated statement with the corrections I mentioned:
    Code:
    Case
         When     TMD .C_LEVEL_CARE      In ('NI','AC') 
              And TMD .C_TYPE_AUTH        = 'INP' 
              And TMD1.C_SERVICE_CAT Not In ('MEH','SUA')              Then TMD1.C_SERVICE_CAT
    
         WHEN     TMD .C_LEVEL_CARE      In ('NI','AC') 
              And TMD .C_TYPE_AUTH        = 'INP' 
              And TMD1.C_SERVICE_CAT     In ('MEH','SUA')              Then 'MHSA'
    
         WHEN     TMD .C_TYPE_AUTH       In ('IOP','PDP')              Then 'MHSA IOP and PDP'
    
         WHEN     TMD .C_LEVEL_CARE      In ('SN','L1','L2','L3','L4') Then 'SNF'
    
         WHEN     TMD .C_LEVEL_CARE      In ('SB','TR')                Then 'Sub-Acute'
    
         WHEN     TMD .C_LEVEL_CARE      in ('RE')                     Then 'Rehab' 
                                                                       ELSE 'NA/Other' 
    END As "Type of Day"

  6. #6
    Join Date
    Jul 2012
    Posts
    31
    Thank you both N I and Stealth DBA! Following both of your replies, I was able to get this to work. I bow down to you both!
    Case
    WHEN TMD.C_LEVEL_CARE = 'AC' AND TMD.C_TYPE_AUTH = 'INP' AND TMD1.C_SERVICE_CAT Not In ('MEH','SUA')Then TMD1.C_SERVICE_CAT
    WHEN TMD.C_LEVEL_CARE In ('NI','AC') And TMD.C_TYPE_AUTH = 'INP' And TMD1.C_SERVICE_CAT In ('MEH','SUA') Then 'MHSA'
    WHEN TMD.C_LEVEL_CARE In('SB','TR') Then 'Sub-Acute'
    WHEN TMD.C_LEVEL_CARE In ('SN','L1','L2','L3','L4') Then 'SNF'
    WHEN TMD.C_TYPE_AUTH In ('IOP','PDP') Then 'MHSA IOP and PDP'
    WHEN TMD.C_LEVEL_CARE = 'RE' Then 'Rehab' ELSE 'NA/Other' End As "Type Of Day",

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •