Friday, 30 November 2012

ABS - Absolute function in Teradata.
In mathematics, the absolute value (or modulus| a | of a real number a is the non-negative value of a without regard to its sign.
Mod (not to be confused with Modulus) function in SQL acts differently.

Example:
So, let us put the ABS function to use.
Suppose I have to find the balance from an account table and decide if its a Credit Balance or a Debit Balance. So, we are not concerned about the sign, rather, in this query request we are more interested to compare the values. Hence, use ABS function, so that the signs (+ or -) do not affect your query results.



Query:

SELECT
CUSTOMER_ID
,SUM(ABS(CREDIT_TRNS) AS CREDIT
,SUM(DEBIT_TRNS) AS DEBIT
,CASE WHEN CREDIT > DEBIT THEN 'CREDIT_BALANCE'
WHEN CREDIT < DEBIT THEN 'DEBIT_BALANCE'
END
AS BALANCE_TYPE
FROM
DW_PROD1.DIM_ACCOUNT_TRNS;

The reason for using ABS(CREDIT_TRNS) is because Banks treats deposits as liabilities and are entered as negative values, representing amount to be paid back to corporate houses and individuals.




No comments:

Post a Comment

Please share your thoughts and let us know the topics you want covered