Aggregate results appearing as text instead of numeric

Toad Data Point
1

I’m new to TDP (Oracle). I have noticed that when I use an aggregate function, such as, sum or count…the result values are left-aligned as if they were text instead of right-aligned as numeric values. This just seemed like a minor inconvenience until I applied a filter in the results window and it filtered the values as text.

In the example below, if I apply a filter to the RETURNS column of “> 6” only the first record will remain. Is there a way to fix this when I setup an aggregate?

YEAR
RETURNS
REFUNDDOLLARS
2012
693
26459.45
2013
1029
40000.2
2014
1257
59505.79
2015
1612
86580.71
2016
1961
100775.37
2017
125
8126.1

2

Could you please provide the following information?

  • Version and bitness of Toad Data Point.

  • Name of Oracle client, version and bitness.

  • The query and DDLs for the tables that are used in the query.

Thanks

Aleksey

3

Toad Data Point 4.0.1.775

Windows 7, 64-bit

OraClient12Home1 (64 bit)

SELECT INVENTORY_RATING.PART_NUMBER,

COUNT (INVENTORY_RATING.BAZAAR_VOICE_ID) AS REVIEWS,

AVG (INVENTORY_RATING.RATING_OVERALL) AS "Avg Overall Rating"

FROM DW_ORDER.INVENTORY_RATING INVENTORY_RATING

WHERE (INVENTORY_RATING.MODERATION_STATUS = ‘APPROVED’)

AND (INVENTORY_RATING.FLAG_DELETED IS NULL)

AND (INVENTORY_RATING.PART_NUMBER LIKE ‘SUM-%’)

GROUP BY INVENTORY_RATING.PART_NUMBER

HAVING (COUNT (INVENTORY_RATING.BAZAAR_VOICE_ID) > 3)

AND (AVG (INVENTORY_RATING.RATING_OVERALL) < 3)

ORDER BY 3 ASC

Only one table involved:
WEB_SITE_ID CHAR(4 BYTE)

TRANSACTION_ID VARCHAR2(64 BYTE)

PARENT_TRANS_ID VARCHAR2(64 BYTE)

BAZAAR_VOICE_ID VARCHAR2(16 BYTE)

MODERATION_STATUS VARCHAR2(50 BYTE)

WEB_PROFILE_ID VARCHAR2(64 BYTE)

PART_NUMBER VARCHAR2(16 BYTE)

PART_NUMBER_INTERNAL NUMBER(9, 0)

RATING_OVERALL NUMBER(3, 0)

COUNT_HELPFUL NUMBER(6, 0)

COUNT_UNHELPFUL NUMBER(6, 0)

FLAG_VERIFIED_PURCHASE VARCHAR2(4 BYTE)

USER_LOCATION VARCHAR2(100 BYTE)

RATING_APPEARANCE NUMBER(3, 0)

RATING_INSTALLATION NUMBER(3, 0)

RATING_PRICEVALUE NUMBER(3, 0)

RATING_QUALITY NUMBER(3, 0)

DATE_CUSTOMER_REVIEWED DATE

DATE_LAST_MODERATED DATE

DATE_REVIEW_DOWNLOADED DATE

FLAG_DELETED VARCHAR2(4 BYTE)

DATE_LAST_ETL DATE

4

If you copy and paste the results into Excel it will happily convert the numbers back to numbers. Even when you don’t want it do this (like with zero filled numbers) it does it anyhow (and stips off the leading zeros). Probably not what you wanted.

You can try: Cast(COUNT (INVENTORY_RATING.BAZAAR_VOICE_ID) as Int) AS REVIEWS

to see if it will force the type but I would have thought that Count would already set the data type as int.

5

Casting to Int had no effect.

6

Go into your Tools/Options/Environment/Grid and see what your Data Type Formating for Number and Integer looks like (on top right). I have Standard and General respectively.

7

I have the same settings.

8

That is hot right. it also seems to only be this way on the Oracle provider. We will get this fixed. QAT-9837