How to use RTRIM in Case statement

• shankley

  SSC Enthusiast

  Points: 117

  More actions

  October 1, 2015 at 10:54 am

  #301176

  I have a DistributorInvoiceNumber that can end with in 'R', 'A', 'CRR' or 'CR'.

  I am trying to write a case statement like so:

  CASE WHEN RIGHT([ih].[DistributorInvoiceNumber],1) = 'A'

  THEN 'ADJ'

  WHEN RIGHT([ih].[DistributorInvoiceNumber],1) = 'R'

  THEN 'REV'

  WHEN RIGHT([ih].[DistributorInvoiceNumber],3) = 'CRR'

  THEN 'REV'

  WHEN RIGHT([ih].[DistributorInvoiceNumber],2) = 'CR'

  THEN 'CREDIT'

  ELSE NULL END AS 'Status'

  For the most part the code is working, with the exception of the fields that just end in 'R'.

  An example of this is 471268R, 2525125901CRR, 11100325230CR

  Basically if the number ends with an A, then its an Adjustment, if it ends with JUST an R, then its a Reversal; if it ends with just a CR then it is a Credit and if it ends with CRR then it is a Reversal (Credit Reversal).

  How can I differentiate between the different R's since three of them end with R? Would I use a RTRIM command somehow?

• Alvin Ramard

  SSC-Forever

  Points: 41190

  More actions

  October 1, 2015 at 11:21 am

  #1830851

  shankley (10/1/2015)

  ---

  I have a DistributorInvoiceNumber that can end with in 'R', 'A', 'CRR' or 'CR'.

  I am trying to write a case statement like so:

  CASE WHEN RIGHT([ih].[DistributorInvoiceNumber],1) = 'A'

  THEN 'ADJ'

  WHEN RIGHT([ih].[DistributorInvoiceNumber],1) = 'R'

  THEN 'REV'

  WHEN RIGHT([ih].[DistributorInvoiceNumber],3) = 'CRR'

  THEN 'REV'

  WHEN RIGHT([ih].[DistributorInvoiceNumber],2) = 'CR'

  THEN 'CREDIT'

  ELSE NULL END AS 'Status'

  For the most part the code is working, with the exception of the fields that just end in 'R'.

  An example of this is 471268R, 2525125901CRR, 11100325230CR

  Basically if the number ends with an A, then its an Adjustment, if it ends with JUST an R, then its a Reversal; if it ends with just a CR then it is a Credit and if it ends with CRR then it is a Reversal (Credit Reversal).

  How can I differentiate between the different R's since three of them end with R? Would I use a RTRIM command somehow?

  If you test for CRR before CR, and CR before testing for R then you should get what you want.

  ---

  
  Alvin Ramard
  Memphis PASS Chapter[/url]

  All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

  For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]