Re: SQL Case Frustration

Chuck,

   Thank you for the suggestion.  I really liked the DIGITS solution.  It looks cleaner to me. 


select orgno concat '-'                          
          concat DIGITS(dec(major, 5)) concat '-'   
          concat DIGITS(dec(sub, 3)),               
          net2,ytd2                                 
from glsum12                                     
where orgno = 30 and major between 97319 and 97456
order by major 


Jim Minisce                                 



________________________________
From: CRPence <CRPbottle@xxxxxxxxx>
To: rpg400-l@xxxxxxxxxxxx
Sent: Thursday, February 28, 2013 6:07 PM
Subject: Re: SQL Case Frustration

On 28 Feb 2013 14:56, Jim Minisce wrote:

<<SNIP>>I would like to use the Case statement to ensure the sub
account code is formatted correctly when I convert the numeric values
to character. ex when the sub account = 0, then '000'; when the sub
account = 10, then '010'; when the sub account = 100, then '100'.

Here is what I have so far:

    Select orgno concat '-' concat major concat '-'

    The CONCAT operator is missing here.  The CASE returns a value, not
an expression, so...

    Case
      When sub<  10 THEN
        concat right('00' || Rtrim(SUBSTR(sub,1,3)), 3)
      When sub>= 10 and sub<  100 THEN
        concat right('0' || Rtrim(SUBSTR(sub,1,3)), 3)
      Else
        concat sub

  The CONCAT operator must be removed as prefix to each of the WHEN and
the ELSE above

      End as FullGL,

  net2,ytd2
from glsum12
where orgno = 30 and major between 97319 and 97456
order by major


  The interactive SQL session is yelling at me, saying that the Case keyword is not expected at line 2.


  Any help would be greatly appreciated!!
Jim Minisce

  However there is likely a better way to do what you want using
DIGITS(dec(sub, 3))... totally eliminating the CASE.