SQL and floating point

[hespora]

Hi there,


to get this right out of the way: I know there's better ways than what I'm doing. I don't have access to them; my org's C11 is still way in IT testing and not live - thus no data modules, and in our C10, we do not have external data available.

So what do I do in order to use external data in reports? I reformat my excel sheets into this format and paste into an SQL item in the report definition:

Code Select Expand


SELECT tbl.sku, tbl.cost
FROM (VALUES

('A',1),
('B',2),
('C',3),
('D',4)

) tbl(sku,cost)

Set SQL Syntax to IBM Cognos, and that works like a charm for strings and integers. Today, however, was the first time I had to use decimal numbers, and that's going all wonky:

a) if I have a fixed number of decimal places but a varying number of places in the integer parts, Cognos shifts the decimal point. E.g., replace the 1,2,3,4 above with 1.1, 2.4, 3.6, and 41.7, and Cognos will return 1.1, 2.4, 3.6 and 4.2. This seems to be dependent on the number of places in the first value, but I'm not certain.

b) In some cases (and I have not yet fully determined which those are) I'm getting an error message "Decimal Value XYZ is too large for Precision A, Scale B".

I'm helping myself by rounding and multiplying so that in my SQL statement I only have integers, which I then divide again in the query, but that of course is a hack upon a hack - not pretty.
Anyone got a solution for this?

[cognostechie]

If it is easy to post the SQL that works, why is it difficult to post the SQL that does not work? Why expect everyone to wreck their brains trying to figure out why you are doing wrong in your query? Having a visual of the problem makes it much easier to understand rather than having an imagination of the problem.