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:
Sent: Sat August 31, 2019 07:44 AM
From: Jonathan berry
Subject: issue with cast and scientific notation
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:
Sent: Fri August 30, 2019 03:58 PM
From: John Mares
Subject: issue with cast and scientific notation
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:
Sent: Tue August 27, 2019 08:29 AM
From: NIGEL CAMPBELL
Subject: issue with cast and scientific notation
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
Original Message:
Sent: Fri August 23, 2019 06:17 PM
From: John Mares
Subject: issue with cast and scientific notation
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