12-07-2015 12:42 PM
Do you have SAS/Access to Teradata, plus SAS formats installed in Teradata? I have a possible solution to the problem of viewing or analyzing BIGINT and very large (or very small) decimal values. But we don't have SAS formats installed in Teradata, so I cannot test my solution. If this works, then I can convince our admin and DBAs to permit this change in Teradata.
The basic idea is this:
Please let me know if you have SAS formats in Teradata, and I will write the code for the test.
I did my research, and was lucky enough to find code where Teradata does the cast, so I am theoretically confident in a positive result that will end the headache of BIGINT, etc. Here is the reference (and proof) in the SAS support site:
After SAS formats are installed on Teradata, we should be able to create SAS
views to access Teradata tables without having any issues reading BIGINT or
large DECIMAL values. Of course, I plan to check those large numeric
columns to make sure they are in the data range of 15 decimal digits.
A SAS document reference and programming example is here, or you can paste this URL:
If you scroll down a ways, you can see the SAS trace for a put() command, which casts that column:
PUT(PRICE,Dollar8.2) AS PRICE_C
SAS trace for Teradata code:
TERADATA_2: Executed: on connection 0
select distinct cast(sas_put("sas"."mailorderdemo"."PRICE", 'DOLLAR8.2')
as char(8)) as "PRICE_C" from "sas"."mailorderdemo"
12-08-2015 09:21 AM
Great if it works, even if a NUM column in SAS would be preferred.
But don't you have a test environment for Teradata, where you could convince your DBAs to install SAS formats - that shouldn't be too hard...?