IC SunsetThe developerWorks Connections platform will be sunset on December 31, 2019. On January 1, 2020, this forum will no longer be available. More details available on our FAQ.
Topic
  • 2 replies
  • Latest Post - ‏2018-05-24T13:03:36Z by FPLAZAVI
FPLAZAVI
FPLAZAVI
20 Posts

Pinned topic SELECT CASE

‏2018-05-23T14:47:17Z | case select

Hi All, im trying to do a select but ... 

 

1) SELECT TMCVSR as NS_VOLUMEN , TMCCLS as CLASE_MEDIO, TMCDAT as FECH_REUTILIZ, TMCBTH as FECH_ADICION, 

              CASE 
              WHEN TMCEXP = '9999999' Then '*PERM'  
              WHEN TMCEXP = '0' Then '*NONE'  
              ELSE TMCEXP 
              END AS FECH_CADUCIDAD  FROM qusrbrm.qA1AMM

 

When run this statement the clause WHEN don't work ... FECH_CADUCIDAD always fulfill the ELSE and the WHEN expressions don't works 

 

NS_VOLUMEN CLASE_MEDIO FECH_REUTILIZ FECH_ADICION FECH_CADUCIDAD
CLNU76 ULTRIUM3 140121 100925 0
DC0001 ULTRIUM3 111129 70112 0
DC0002 ULTRIUM3 140121 70112 1111217
DC0003 ULTRIUM3 140121 70112 1120511
DC0004 ULTRIUM3 110908 70112 0
DC0005 ULTRIUM3 120811 70112 1120811

 

 

2) SELECT TMCVSR as NS_VOLUMEN , TMCCLS as CLASE_MEDIO, TMCDAT as FECH_REUTILIZ, TMCBTH as FECH_ADICION, 
              CASE 
              WHEN TMCEXP = '9999999' Then '*PERM'  
              WHEN TMCEXP = '0' Then '*NONE'  
               END AS FECH_CADUCIDAD
 FROM qusrbrm.qA1AMM

 

If I omit the ELSE the WHEN expressions the WHEN clause works fine ... but i haven't the others values ...

 

NS_VOLUMEN CLASE_MEDIO FECH_REUTILIZ FECH_ADICION FECH_CADUCIDAD
CLNU76 ULTRIUM3 140121 100925 *NONE
DC0001 ULTRIUM3 111129 70112 *PERM
DC0002 ULTRIUM3 140121 70112  
DC0003 ULTRIUM3 140121 70112  
DC0004 ULTRIUM3 110908 70112 *PERM
DC0005 ULTRIUM3 120811 70112  
DC0006 ULTRIUM3 110927 70112 *PERM
DC0007 ULTRIUM3 140121 70112  

 

3)SELECT TMCVSR as NS_VOLUMEN , TMCCLS as CLASE_MEDIO, TMCDAT as FECH_REUTILIZ, TMCBTH as FECH_ADICION, 
              CASE 
              WHEN TMCEXP = '9999999' Then '*PERM'  
              WHEN TMCEXP = '0' Then '*NONE'  
              ELSE 'TMCEXP
              END AS FECH_CADUCIDAD
 FROM qusrbrm.qA1AMM

 

In this case i can confirm that the logic is ok but neither i have the value

 

NS_VOLUMEN CLASE_MEDIO FECH_REUTILIZ FECH_ADICION FECH_CADUCIDAD
CLNU76 ULTRIUM3 140121 100925 *NONE
DC0001 ULTRIUM3 111129 70112 *PERM
DC0002 ULTRIUM3 140121 70112 TMCEXP
DC0003 ULTRIUM3 140121 70112 TMCEXP
DC0004 ULTRIUM3 110908 70112 *PERM
DC0005 ULTRIUM3 120811 70112 TMCEXP

 

 

Someone can confirm me that is possible to do the first option??

 

And the big question, someone have statement to query BRMS database??? ;-)

 

 

Thanks, 

 

Fernando 

  • robberendt
    robberendt
    84 Posts
    ACCEPTED ANSWER

    Re: SELECT CASE

    ‏2018-05-24T11:39:42Z  

    IDK what version of the OS you are on.  

    But, does your version of OS have TMCEXP as a string and not a numeric?  At 7.3 TMCEXP is a numeric and I would never compare a numeric with a string value like '0'.  Instead I would compare it to 0.

    Try this:

    SELECT TMCVSR as NS_VOLUMEN,       
    TMCCLS as CLASE_MEDIO,             
    TMCDAT as FECH_REUTILIZ,           
    TMCBTH as FECH_ADICION,            
    CASE                               
    WHEN TMCEXP = 9999999 Then cast('*PERM' as char(7))
    WHEN TMCEXP = 0 Then cast('*NONE' as char(7))
    ELSE cast(TMCEXP as char(7))
    END AS FECH_CADUCIDAD
    FROM qusrbrm.qA1AMM
    order by tmcexp;

    It's important to cast them all into the same type and size.

  • robberendt
    robberendt
    84 Posts

    Re: SELECT CASE

    ‏2018-05-24T11:39:42Z  

    IDK what version of the OS you are on.  

    But, does your version of OS have TMCEXP as a string and not a numeric?  At 7.3 TMCEXP is a numeric and I would never compare a numeric with a string value like '0'.  Instead I would compare it to 0.

    Try this:

    SELECT TMCVSR as NS_VOLUMEN,       
    TMCCLS as CLASE_MEDIO,             
    TMCDAT as FECH_REUTILIZ,           
    TMCBTH as FECH_ADICION,            
    CASE                               
    WHEN TMCEXP = 9999999 Then cast('*PERM' as char(7))
    WHEN TMCEXP = 0 Then cast('*NONE' as char(7))
    ELSE cast(TMCEXP as char(7))
    END AS FECH_CADUCIDAD
    FROM qusrbrm.qA1AMM
    order by tmcexp;

    It's important to cast them all into the same type and size.

  • FPLAZAVI
    FPLAZAVI
    20 Posts

    Re: SELECT CASE

    ‏2018-05-24T13:03:36Z  

    IDK what version of the OS you are on.  

    But, does your version of OS have TMCEXP as a string and not a numeric?  At 7.3 TMCEXP is a numeric and I would never compare a numeric with a string value like '0'.  Instead I would compare it to 0.

    Try this:

    SELECT TMCVSR as NS_VOLUMEN,       
    TMCCLS as CLASE_MEDIO,             
    TMCDAT as FECH_REUTILIZ,           
    TMCBTH as FECH_ADICION,            
    CASE                               
    WHEN TMCEXP = 9999999 Then cast('*PERM' as char(7))
    WHEN TMCEXP = 0 Then cast('*NONE' as char(7))
    ELSE cast(TMCEXP as char(7))
    END AS FECH_CADUCIDAD
    FROM qusrbrm.qA1AMM
    order by tmcexp;

    It's important to cast them all into the same type and size.

    More...

    Hi Rob, 

     

    Thanks you for the support ... i need read and read "DB2 for i SQL Reference" reedbook ...

     

    Now works... of course!!!

     

    Fernando 

Feed for this topic