cast(CONTRACT_LENGTH_MONTHS, varchar)

What is wrong?
Thanks!

Hi,

As Patrick suggests, specify the maximum size of the varchar to cast to:

cast(CONTRACT_LENGTH_MONTHS, varchar(5))

Pick a value greater than the greatest number of digits you want the item to support.

Incidentally, I misunderstood the intention of the post initially. I assumed you were trying to convert numbers to words, eg

2137 --> Two thousand one hundred and thirty seven

If ever you need this, I have a technique.

Cheers!

MF.

Original Message:
Sent: Wed July 27, 2022 05:09 PM
From: Sergio Marinho da Silva
Subject: Cognos Module (11.1.7) - convert number to text (varchar) - only first digit retrieved

Hi, team!

I'm trying to convert numbers to text in a custom column in Cognos Module, but it is only returning the first digit of the number.
I'm using this formula:

cast(CONTRACT_LENGTH_MONTHS, varchar)

What is wrong?
Thanks!

------------------------------
Sergio Marinho da Silva
------------------------------

#CognosAnalyticswithWatson

Hi Mark,

I would like to have the technique yo have to convert numbers to words.

Thanks in advance

cast(CONTRACT_LENGTH_MONTHS, varchar)

Hi Antonio,

This technique was initially conceived and written by Lynn Bramande, so most of the credit goes to her. I adapted it to use Cognos syntax.

#/* 
  -- Convert numeric amount to words

  -- Supports figures up to 999,999,999.99
  -- This example uses figures that have no more than two places after the decimal point
  -- Adaptation in part4 would be required (round/trunc functions) to support figures with more precision
  -- If the value is negative the MINUS label is determined
  -- The number is parsed into three pieces representing millions, thousands, and hundreds
  -- Three zero padded strings are produced for each piece
  -- floor returns the portion to the left of the decimal point after conversion to absolute value
  -- The value minus the floor returns the portion after the decimal point
  -- e.g., The value 1,306,579.82 would parse part 1 as 001; part2 as 306; part3 as 579 and 0.82 as part4

-- OK, now that you know the background, get going!
 
    #/* Start with the Minus label */#
    CASE WHEN [YourQueryItemHere] < 0 THEN 'MINUS ' ELSE '' END +
    #/* Check if the value is just zero */#
    CASE [YourQueryItemHere] WHEN 0 THEN 'ZERO' ELSE '' END +
    #/*
    -- Process Part1 for Millions
    --
    -- First digit indicates Hundreds of Millions */#
    CASE substring(substring(substring ( '000000000' + cast(floor(abs([YourQueryItemHere])),varchar(12)),character_length('000000000' + cast(floor(abs([YourQueryItemHere])),varchar(12)))-8 , 9),1,3),1,1)
        WHEN '0' THEN ''               WHEN '1' THEN 'One Hundred'                     
        WHEN '2' THEN 'Two Hundred' WHEN '3' THEN 'Three Hundred'                     
        WHEN '4' THEN 'Four Hundred' WHEN '5' THEN 'Five Hundred'                     
        WHEN '6' THEN 'Six Hundred' WHEN '7' THEN 'Seven Hundred'                     
        WHEN '8' THEN 'Eight Hundred' WHEN '9' THEN 'Nine Hundred'                     
    END  +               
    #/* Second digit 0 indicates ones of Millions
    -- Second digit 1 indicates teens of Millions
    -- Other values for second digit indicates more than 10 Million */#
    CASE substring(substring(substring ( '000000000' + cast(floor(abs([YourQueryItemHere])),varchar(12)),character_length('000000000' + cast(floor(abs([YourQueryItemHere])),varchar(12)))-8 , 9),1,3),2,1)                                       
        WHEN '0'  THEN                                         
            CASE substring(substring(substring ( '000000000' + cast(floor(abs([YourQueryItemHere])),varchar(12)),character_length('000000000' + cast(floor(abs([YourQueryItemHere])),varchar(12)))-8 , 9),1,3),3,1)                             
                WHEN '0' THEN ''        WHEN '1' THEN ' One'                          
                WHEN '2' THEN ' Two'   WHEN '3' THEN ' Three'                        
                WHEN '4' THEN ' Four'  WHEN '5' THEN ' Five'                         
                WHEN '6' THEN ' Six'   WHEN '7' THEN ' Seven'                        
                WHEN '8' THEN ' Eight' WHEN '9' THEN ' Nine'                        
            END
        WHEN '1' THEN                                          
            CASE substring(substring(substring ( '000000000' + cast(floor(abs([YourQueryItemHere])),varchar(12)),character_length('000000000' + cast(floor(abs([YourQueryItemHere])),varchar(12)))-8 , 9),1,3),3,1)                             
                WHEN '0' THEN ' Ten'       WHEN '1' THEN ' Eleven'                       
                WHEN '2' THEN ' Twelve'    WHEN '3' THEN ' Thirteen'                     
                WHEN '4' THEN ' Fourteen'  WHEN '5' THEN ' Fifteen'                      
                WHEN '6' THEN ' Sixteen'   WHEN '7' THEN ' Seventeen'                    
                WHEN '8' THEN ' Eighteen'  WHEN '9' THEN ' Nineteen'                     
            END                                              
        WHEN '2' THEN  ' Twenty'  WHEN '3' THEN  ' Thirty'                              
        WHEN '4' THEN  ' Forty'   WHEN '5' THEN  ' Fifty'                             
        WHEN '6' THEN  ' Sixty'   WHEN '7' THEN  ' Seventy'                           
        WHEN '8' THEN  ' Eighty'  WHEN '9' THEN  ' Ninety'                            
    END +
    #/* Third digit indicates ones of Millions
    -- No need to process third digit if second was 0 or 1 because it was processed above */#
    CASE substring(substring(substring ( '000000000' + cast(floor(abs([YourQueryItemHere])),varchar(12)),character_length('000000000' + cast(floor(abs([YourQueryItemHere])),varchar(12)))-8 , 9),1,3),2,1)                                       
        WHEN '0' THEN ''
        WHEN '1' THEN ''
        ELSE
          CASE substring(substring(substring ( '000000000' + cast(floor(abs([YourQueryItemHere])),varchar(12)),character_length('000000000' + cast(floor(abs([YourQueryItemHere])),varchar(12)))-8 , 9),1,3),3,1)                                       
            WHEN '0' THEN ''        WHEN '1' THEN ' One'                          
            WHEN '2' THEN ' Two'   WHEN '3' THEN ' Three'                        
            WHEN '4' THEN ' Four'  WHEN '5' THEN ' Five'                         
            WHEN '6' THEN ' Six'   WHEN '7' THEN ' Seven'                        
            WHEN '8' THEN ' Eight' WHEN '9' THEN ' Nine'                        
          END
    END +
    #/* End Millions section, apply label if value is in the Millions */#
    CASE WHEN substring(substring ( '000000000' + cast(floor(abs([YourQueryItemHere])),varchar(12)),character_length('000000000' + cast(floor(abs([YourQueryItemHere])),varchar(12)))-8 , 9),1,3) <> '000' then ' Million' ELSE '' end + 
    #/*
    -- Process Part2
    --
    -- Logic is the same as part 1 (only the label at the end of the section is different) */#
    CASE substring(substring(substring ( '000000000' + cast(floor(abs([YourQueryItemHere])),varchar(12)),character_length('000000000' + cast(floor(abs([YourQueryItemHere])),varchar(12)))-8 , 9),4,3),1,1)                                       
        WHEN '0'  THEN ''              WHEN '1' THEN ' One Hundred'                     
        WHEN '2' THEN ' Two Hundred' WHEN '3' THEN ' Three Hundred'                     
        WHEN '4' THEN ' Four Hundred' WHEN '5' THEN ' Five Hundred'                     
        WHEN '6' THEN ' Six Hundred' WHEN '7' THEN ' Seven Hundred'                     
        WHEN '8' THEN ' Eight Hundred' WHEN '9' THEN ' Nine Hundred'                     
    END  +                                                  
    CASE substring(substring(substring ( '000000000' + cast(floor(abs([YourQueryItemHere])),varchar(12)),character_length('000000000' + cast(floor(abs([YourQueryItemHere])),varchar(12)))-8 , 9),4,3),2,1)                                       
        WHEN '0'  THEN                                         
            CASE substring(substring(substring ( '000000000' + cast(floor(abs([YourQueryItemHere])),varchar(12)),character_length('000000000' + cast(floor(abs([YourQueryItemHere])),varchar(12)))-8 , 9),4,3),3,1)                             
                WHEN '0' THEN ''        WHEN '1' THEN ' One'                          
                WHEN '2' THEN ' Two'   WHEN '3' THEN ' Three'                        
                WHEN '4' THEN ' Four'  WHEN '5' THEN ' Five'                         
                WHEN '6' THEN ' Six'   WHEN '7' THEN ' Seven'                        
                WHEN '8' THEN ' Eight' WHEN '9' THEN ' Nine'                        
            END                             
        WHEN '1' THEN                                          
            CASE substring(substring(substring ( '000000000' + cast(floor(abs([YourQueryItemHere])),varchar(12)),character_length('000000000' + cast(floor(abs([YourQueryItemHere])),varchar(12)))-8 , 9),4,3),3,1)                             
                WHEN '0' THEN ' Ten'       WHEN '1' THEN ' Eleven'                       
                WHEN '2' THEN ' Twelve'    WHEN '3' THEN ' Thirteen'                     
                WHEN '4' THEN ' Fourteen'  WHEN '5' THEN ' Fifteen'                      
                WHEN '6' THEN ' Sixteen'   WHEN '7' THEN ' Seventeen'                    
                WHEN '8' THEN ' Eighteen'  WHEN '9' THEN ' Nineteen'                     
            END                                              
        WHEN '2' THEN  ' Twenty'  WHEN '3' THEN  ' Thirty'                              
        WHEN '4' THEN  ' Forty'   WHEN '5' THEN  ' Fifty'                             
        WHEN '6' THEN  ' Sixty'   WHEN '7' THEN  ' Seventy'                           
        WHEN '8' THEN  ' Eighty'  WHEN '9' THEN  ' Ninety'                            
    END +
    CASE substring(substring(substring ( '000000000' + cast(floor(abs([YourQueryItemHere])),varchar(12)),character_length('000000000' + cast(floor(abs([YourQueryItemHere])),varchar(12)))-8 , 9),4,3),2,1)                                       
        WHEN '0' THEN ''
        WHEN '1' THEN ''
        ELSE
          CASE substring(substring(substring ( '000000000' + cast(floor(abs([YourQueryItemHere])),varchar(12)),character_length('000000000' + cast(floor(abs([YourQueryItemHere])),varchar(12)))-8 , 9),4,3),3,1)                                       
            WHEN '0' THEN ''        WHEN '1' THEN ' One'                          
            WHEN '2' THEN ' Two'   WHEN '3' THEN ' Three'                        
            WHEN '4' THEN ' Four'  WHEN '5' THEN ' Five'                         
            WHEN '6' THEN ' Six'   WHEN '7' THEN ' Seven'                        
            WHEN '8' THEN ' Eight' WHEN '9' THEN ' Nine'                        
          END
    END +
    #/* End Thousands section, apply label if value is in the Thousands */#
    CASE WHEN substring(substring ( '000000000' + cast(floor(abs([YourQueryItemHere])),varchar(12)),character_length('000000000' + cast(floor(abs([YourQueryItemHere])),varchar(12)))-8 , 9),4,3) <> '000' then ' Thousand' ELSE ' ' END +
    #/* 
    -- Process Part3
    --
    -- Logic is the same as part 1 (there is no label at the end) */#
    CASE substring(substring(substring ( '000000000' + cast(floor(abs([YourQueryItemHere])),varchar(12)),character_length('000000000' + cast(floor(abs([YourQueryItemHere])),varchar(12)))-8 , 9),7,3),1,1)                                       
        WHEN '0'  THEN ''              WHEN '1' THEN ' One Hundred'                     
        WHEN '2' THEN ' Two Hundred' WHEN '3' THEN ' Three Hundred'                     
        WHEN '4' THEN ' Four Hundred' WHEN '5' THEN ' Five Hundred'                     
        WHEN '6' THEN ' Six Hundred' WHEN '7' THEN ' Seven Hundred'                     
        WHEN '8' THEN ' Eight Hundred' WHEN '9' THEN ' Nine Hundred'                     
    END  +                                                  
    CASE substring(substring(substring ( '000000000' + cast(floor(abs([YourQueryItemHere])),varchar(12)),character_length('000000000' + cast(floor(abs([YourQueryItemHere])),varchar(12)))-8 , 9),7,3),2,1)                                       
        WHEN '0'  THEN                                         
            CASE substring(substring(substring ( '000000000' + cast(floor(abs([YourQueryItemHere])),varchar(12)),character_length('000000000' + cast(floor(abs([YourQueryItemHere])),varchar(12)))-8 , 9),7,3),3,1)                             
                WHEN '0' THEN ''        WHEN '1' THEN ' One'                          
                WHEN '2' THEN ' Two'   WHEN '3' THEN ' Three'                        
                WHEN '4' THEN ' Four'  WHEN '5' THEN ' Five'                         
                WHEN '6' THEN ' Six'   WHEN '7' THEN ' Seven'                        
                WHEN '8' THEN ' Eight' WHEN '9' THEN ' Nine'                        
            END                                              
        WHEN '1' THEN                                          
            CASE substring(substring(substring ( '000000000' + cast(floor(abs([YourQueryItemHere])),varchar(12)),character_length('000000000' + cast(floor(abs([YourQueryItemHere])),varchar(12)))-8 , 9),7,3),3,1)                             
                WHEN '0' THEN ' Ten'       WHEN '1' THEN ' Eleven'                       
                WHEN '2' THEN ' Twelve'    WHEN '3' THEN ' Thirteen'                     
                WHEN '4' THEN ' Fourteen'  WHEN '5' THEN ' Fifteen'                      
                WHEN '6' THEN ' Sixteen'   WHEN '7' THEN ' Seventeen'                    
                WHEN '8' THEN ' Eighteen'  WHEN '9' THEN ' Nineteen'                     
            END                         
        WHEN '2' THEN  ' Twenty'  WHEN '3' THEN  ' Thirty'                              
        WHEN '4' THEN  ' Forty'   WHEN '5' THEN  ' Fifty'                             
        WHEN '6' THEN  ' Sixty'   WHEN '7' THEN  ' Seventy'                           
        WHEN '8' THEN  ' Eighty'  WHEN '9' THEN  ' Ninety'                            
    END +
    CASE substring(substring(substring ( '000000000' + cast(floor(abs([YourQueryItemHere])),varchar(12)),character_length('000000000' + cast(floor(abs([YourQueryItemHere])),varchar(12)))-8 , 9),7,3),2,1)                                       
        WHEN '0' THEN ''
        WHEN '1' THEN ''
        ELSE
          CASE substring(substring(substring ( '000000000' + cast(floor(abs([YourQueryItemHere])),varchar(12)),character_length('000000000' + cast(floor(abs([YourQueryItemHere])),varchar(12)))-8 , 9),7,3),3,1)                                       
            WHEN '0' THEN ''        WHEN '1' THEN ' One'                          
            WHEN '2' THEN ' Two'   WHEN '3' THEN ' Three'                        
            WHEN '4' THEN ' Four'  WHEN '5' THEN ' Five'                         
            WHEN '6' THEN ' Six'   WHEN '7' THEN ' Seven'                        
            WHEN '8' THEN ' Eight' WHEN '9' THEN ' Nine'                        
          END
    END + 
    #/* END Hundreds section
     Conclude with fractional amount */#
    ' AND ' +
     (if (abs([YourQueryItemHere]) = floor(abs([YourQueryItemHere]))) then ('0') else (cast( (abs([YourQueryItemHere]*100) - floor(abs([YourQueryItemHere]))*100),varchar(4)))) + '/100'

Put this syntax into a query calculation, and replace all instances of [YourQueryItemHere] with your real item containing the number you want to display as words.

Cheers!
MF.

cast(CONTRACT_LENGTH_MONTHS, varchar)