# Two decimals sql to table on the perspective page

**URL:** <https://forum.inductiveautomation.com/t/two-decimals-sql-to-table-on-the-perspective-page/55615>\
**Category:** Ignition\
**Created:** [February 1, 2022, 4:36pm UTC](https://forum.inductiveautomation.com/t/two-decimals-sql-to-table-on-the-perspective-page/55615 "2022-02-01T16:36:22Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![zacharyw.larson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zacharyw.larson/32/23749_2.png) [@zacharyw.larson](https://forum.inductiveautomation.com/u/zacharyw.larson)\
**Post date:** [February 1, 2022, 4:36pm UTC](https://forum.inductiveautomation.com/t/two-decimals-sql-to-table-on-the-perspective-page/55615/1 "2022-02-01T16:36:23Z")

</div>

```auto
cast(coalesce(100.0*(Rejects)/nullif(attemped,0),0)as numeric(38,2)) 
as '% Rejects/Attempted'

```

So I both used a cast and tried to make sure I used a float.

I also went to format the number as 4 decimals, and then deleted two zeros.

Whatever I do though, when I get 100%, it shows 100 instead of 100.00

replacing the 38 with 5 would work, but sometimes I get data that I both can’t ignore, need to show, and yet is 3000% for some reason or another, like part of the line broke and circumventing resulting in some counting irregularities, so then the 5 will result in an overflow error.  
I could catch the overflow, I could do a case, see the value size then set the decimal  
hoping there is a better cleaner way

---

<div class="post-metadata">

**Author:** ![zacht](https://avatars.discourse-cdn.com/v4/letter/z/ea5d25/32.png) [@zacht](https://forum.inductiveautomation.com/u/zacht)\
**Post date:** [February 1, 2022, 8:19pm UTC](https://forum.inductiveautomation.com/t/two-decimals-sql-to-table-on-the-perspective-page/55615/2 "2022-02-01T20:19:29Z")

</div>

Return the data in whatever way is easiest to query (ie don’t wory about casting to numeric) and then edit the `numberFormat` string in the table. I didn’t check your SQL, so I’m taking your word for it that the forumla returns the data you want. Your alias name may work, but I don’t like spaces, so I’d go with:

`COALESCE(100.0*(rejects)/NULLIF(attemped,0),0) AS rejectPercent`

In `props.columns` of the table, create as many objects as you have columns you want to display (looks like 1). Use the plus button and an object with the correct structure will be automatically created.

- Set the `field` value of the appropriate column to `rejectPercent` (or whatever you use as an alias).
- In the same object, set numberFormat to `0,0.00`. This will guarentee you two decimal points.
- Set `header.title` to `% Rejects/Attempted` to preserve the display you want.

---

<div class="post-metadata">

**Author:** ![zacharyw.larson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zacharyw.larson/32/23749_2.png) [@zacharyw.larson](https://forum.inductiveautomation.com/u/zacharyw.larson)\
**Post date:** [February 1, 2022, 9:13pm UTC](https://forum.inductiveautomation.com/t/two-decimals-sql-to-table-on-the-perspective-page/55615/3 "2022-02-01T21:13:10Z")

</div>

I set the number format to 0,0.0000 and I can get 4 digits in the decimal.

Then, when 100 comes in though, the zeroes seem to be ignored.

I can live with it, but if it can be fixed that would be good.

---

<div class="post-metadata">

**Author:** ![zacht](https://avatars.discourse-cdn.com/v4/letter/z/ea5d25/32.png) [@zacht](https://forum.inductiveautomation.com/u/zacht)\
**Post date:** [February 1, 2022, 9:32pm UTC](https://forum.inductiveautomation.com/t/two-decimals-sql-to-table-on-the-perspective-page/55615/4 "2022-02-01T21:32:27Z")

</div>

This is the default table data edited to add some 100s. It seems to work here, so I’m not sure what the issue is. I’m using Ignition V8.1.5.

 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/0/a/0a1bedbe5e4f14f63d0fed0781ecce46cd12ec02.png)

---

<div class="post-metadata">

**Author:** ![zacharyw.larson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zacharyw.larson/32/23749_2.png) [@zacharyw.larson](https://forum.inductiveautomation.com/u/zacharyw.larson)\
**Post date:** [February 2, 2022, 2:09pm UTC](https://forum.inductiveautomation.com/t/two-decimals-sql-to-table-on-the-perspective-page/55615/5 "2022-02-02T14:09:21Z")

</div>

I will try it again.

I am on 8.1.0