Cognos Analytics

Cognos Analytics

Connect, learn, and share with thousands of IBM Cognos Analytics users! 


#Product
#Analytics
#CognosAnalytics
#Analyticstools
#TechXchange Partner
#TechXchange Session
#TechXchange Presenter
#AI
 View Only

  • 1.  issue with cast and scientific notation

    Posted 08/23/19 06:17 PM
    ​I am trying to shift a Intersect(Tuple) type that I believe is integer to string
    cast ([Paid,Employee], varchar(15))
    works but it puts the number into scientific notation even when it small such as 300 (data comes in fine if I don't shift the data type but I need to include the numbers in string for  Is then statements Example your sales were 15500 dollars this is above the norm). I tried things like round hoping that would override it put have not had any luck ( also data types like char and string outright fail).  Is there another Query code I can use or add to change it? I am trying to build this in a client portal of IBM cognos so I am rather gimped how much deep I can look and how much I can change.

    Thanks you be a huge help so far.

    ------------------------------
    John Mares
    ------------------------------

    #CognosAnalyticswithWatson


  • 2.  RE: issue with cast and scientific notation

    Posted 08/24/19 08:30 AM
    Hi John,
    Have you tried something like:
    cast (   cast( [Paid,Employee] , decimal(10,2))    , varchar(15))

    //Henk

    ------------------------------
    HENK CAZEMIER
    ------------------------------



  • 3.  RE: issue with cast and scientific notation

    Posted 08/26/19 09:37 AM

    Thanks that did the trick.


    ------------------------------
    John Mares
    ------------------------------

    Original Message


  • 4.  RE: issue with cast and scientific notation

    Posted 08/27/19 10:15 AM

    Not sure if my previous reply made it.

    CAST in SQL is verify specific re how a non-charter type be represented as a character type.

    Consider the following example:

    select 1 as C1, 1.23 as C2, 7.77e0 as C3

    C1 is a literal representation of a precise numeric type which a vendor will treat as an integer type that can hold the value

    C2 is a literal representation of  a precise numeric type which a vendor will treat as a decimal with precision (p) and scale(s) based on the value

    C3 is an approximate numeric type i.e. Double, Real

    When you cast C3 to a VARCHAR, SQL requires the character string to be an equivalent version of a literal value.

    When you CAST (  CAST (   as DECIMAL (p,s)  as VARCHAR(n)) keep in mind that the literal will include as many trailing zeroes as defined by s.



    ------------------------------
    NIGEL CAMPBELL
    ------------------------------



  • 5.  RE: issue with cast and scientific notation

    Posted 08/27/19 10:21 AM

    When you CAST a non-string type to a string type, SQL standard expects that the resultant string value represent the original data type.

    For example if you were to type

    SELECT 1 C1, 1.5 C2 , 7.77e0 C3 from ....

    C1 would imply an integer literal where based on the precision of the number the vendor will pick an appropriate integer type

    C2 would imply a precise numeric type (i.e decimal) whose precision (p) and scale (s) is derived from the value

    C3 is an approximate number (i.e double, real etc)

    Hence, when you CAST ( C3 as varchar(9)) you get a string representation of an approximate numeric type.

    Note, if you  CAST( CAST ( as DECIMAL (P,S))  as VARCHAR(n)) you will get a precise decimal literal value.

    Keep in mind, this also means trailing zeroes will be included in the literal based on the P,S of the decimal.



    ------------------------------
    NIGEL CAMPBELL
    ------------------------------



  • 6.  RE: issue with cast and scientific notation

    Posted 08/30/19 03:58 PM
    ​Thanks 'Single Count is '||(cast (   cast( [numbercheck2], decimal(10,0))    , varchar(15)))||'% of enrollment' worked with [numbercheck2] = (([Medical].[Project].[Number of Single]/[Medical].[Project].[Number of Lives])*100) however if I try to build it all as one data item

    'Single Count is '||(cast (   cast( (([Medical].[Project].[Number of Single]/[Medical].[Project].[Number of Lives])*100)))    , varchar(15)))||'% of enrollment'

    It defaults the number to 1 not sure why

    ------------------------------
    John Mares
    ------------------------------

    Original Message


  • 7.  RE: issue with cast and scientific notation

    Posted 08/31/19 07:45 AM
    I'm not sure if this will help you, however I have had issues with Cast in SQL server when using integers a divide function and wanting an output with decimal places. 
    I trick that I found works was that I added 0.00 to the integer. 
    The output of the calculation was then correct.

    ------------------------------
    Jonathan berry
    ------------------------------

    Original Message


  • 8.  RE: issue with cast and scientific notation

    Posted 09/02/19 09:28 AM

    See my prior reply.

    Values such as  1, 123456789 etc will be parsed by a SQL engine an an integer type.

    The actual type they assume will depend on which integer types they support (i.e. smallint, integer, bigint) and the precision of the value. Should you enter a very large value which is larger than the precision of the vendors integer types, they may throw an error or promote to another type (i.e. double or decimal) that can hold the value.

    Meanwhile 1.00 should be parsed as a decimal value. There are some vendors who may not document that the driver/server parses it as  double. No correct, particularly if they have a DECIMAL/NUMERIC type etc.



    ------------------------------
    NIGEL CAMPBELL
    ------------------------------

    Original Message


Related Content