Luhn Algorithm in Teradata SQL

Luhn algorithm is used, among others, to calculate the checksum digit of credit cards and mobile handset IMEIs. The following is my attempt to implement this algorithm in Teradata sql. It flags each IMEI as valid or not. Needless to say, IMEIs would typically be read from a table rather than hard-coded as in this example.

SELECT dt3.IMEI
       ,CASE 
          WHEN (dt3.dig1 + dt3.dig2 + dt3.dig3 + dt3.dig4 
           + dt3.dig5 + dt3.dig6 + dt3.dig7
           + dt3.dig8 + dt3.dig9 + dt3.dig10 + dt3.dig11 
           + dt3.dig12 + dt3.dig13 
           + dt3.dig14 + dt3.dig15) MOD 10 = 0 THEN 'Y' 
          ELSE 'N'
       END AS VALID_IMEI          
FROM
(
SELECT dt2.IMEI
    ,dt2.dig1
    ,CASE 
            WHEN dt2.dig2 = 0 THEN 0
            WHEN dt2.dig2 MOD 9 = 0 THEN 9
            ELSE dt2.dig2 MOD 9
     END AS dig2
    ,dt2.dig3
    ,CASE 
            WHEN dt2.dig4 = 0 THEN 0
            WHEN dt2.dig4 MOD 9 = 0 THEN 9
            ELSE dt2.dig4 MOD 9
     END AS dig4
    ,dt2.dig5
   ,CASE 
            WHEN dt2.dig6 = 0 THEN 0
            WHEN dt2.dig6 MOD 9 = 0 THEN 9
            ELSE dt2.dig6 MOD 9
   END AS dig6
   ,dt2.dig7
   ,CASE 
            WHEN dt2.dig8 = 0 THEN 0
            WHEN dt2.dig8 MOD 9 = 0 THEN 9
            ELSE dt2.dig8 MOD 9
   END AS dig8
   ,dt2.dig9
   ,CASE 
            WHEN dt2.dig10 = 0 THEN 0
            WHEN dt2.dig10 MOD 9 = 0 THEN 9
            ELSE dt2.dig10 MOD 9
   END AS dig10
   ,dt2.dig11
   ,CASE 
            WHEN dt2.dig12 = 0 THEN 0
            WHEN dt2.dig12 MOD 9 = 0 THEN 9
            ELSE dt2.dig12 MOD 9
   END AS dig12
   ,dt2.dig13
   ,CASE 
            WHEN dt2.dig14 = 0 THEN 0
            WHEN dt2.dig14 MOD 9 = 0 THEN 9
            ELSE dt2.dig14 MOD 9
   END AS dig14
   ,dt2.dig15
FROM
(
SELECT dt1.IMEI
        ,SUBSTR(dt1.IMEI, 1, 1) AS dig1
        ,SUBSTR(dt1.IMEI, 2, 1) * 2 AS dig2
        ,SUBSTR(dt1.IMEI, 3, 1) AS dig3
        ,SUBSTR(dt1.IMEI, 4, 1)  * 2 AS dig4
        ,SUBSTR(dt1.IMEI, 5, 1) AS dig5
        ,SUBSTR(dt1.IMEI, 6, 1)  * 2 AS dig6
        ,SUBSTR(dt1.IMEI, 7, 1) AS dig7
        ,SUBSTR(dt1.IMEI, 8, 1)  * 2 AS dig8
        ,SUBSTR(dt1.IMEI, 9, 1) AS dig9
        ,SUBSTR(dt1.IMEI, 10, 1) * 2 AS dig10
        ,SUBSTR(dt1.IMEI, 11, 1) AS dig11
        ,SUBSTR(dt1.IMEI, 12, 1) * 2 AS dig12
        ,SUBSTR(dt1.IMEI, 13, 1) AS dig13
        ,SUBSTR(dt1.IMEI, 14, 1) * 2 AS dig14
        ,SUBSTR(dt1.IMEI, 15, 1) AS dig15
FROM
(
SELECT '999999999999999' AS IMEI
FROM SYS_CALENDAR.CALENDAR
WHERE calendar_date = CURRENT_DATE

UNION 

SELECT '352651010278244' AS IMEI
FROM SYS_CALENDAR.CALENDAR
WHERE calendar_date = CURRENT_DATE

) AS dt1
) AS dt2
) AS dt3
;

It returns the following answerset:

SQL Query Tuning

SQL query tuning is a dark art but here are some simple tips that anyone can use.

Monte Carlo Simulation to Estimate pi

Imagine a person randomly throwing darts at a square board with a unit circle inscribed on it. The proportion of darts that land in the circle multiplied by 4 gives an estimate of the value of pi. The point (x, y) hit by the dart is in the circle if x^2 + y^2 <= 1.

What follows is my implementation of this idea in Teradata sql, with the value of pi estimated to two decimal places. The resource at the end of this post has a nice discourse on this topic, with Python implementations and a video on Monte Carlo simulation.

/* Create a table with a random point (x,y) on the board */
CREATE VOLATILE TABLE tblTemp AS
(SELECT RANDOM(-100,100)/100.00 AS x
,RANDOM(-100,100)/100.00 AS y
,CASE WHEN x**2+y**2 <= 1 THEN 1
ELSE 0 
END AS inCirc
)WITH DATA PRIMARY INDEX (x)
ON COMMIT PRESERVE ROWS;

/* Generate a sufficiently large (but not too large!) number of random points (x,y) on the board */
CREATE VOLATILE TABLE tblRndPts AS
(WITH RECURSIVE tblRnd (lvl, x, y, inCirc) AS
(SELECT CAST(1 AS DECIMAL(18,0)) AS lvl, x, y, inCirc
FROM tblTemp

UNION ALL

SELECT lvl+1
,RANDOM(-100,100)/100.00 AS x1
,RANDOM(-100,100)/100.00 AS y1
,CASE WHEN x1**2+y1**2 <= 1 THEN 1
ELSE 0
END AS inCirc
FROM tblRnd AS t1
WHERE t1.lvl < 10000 /* total number of points (x, y) */
)
SELECT *
FROM tblRnd
) WITH DATA PRIMARY INDEX (lvl)
ON COMMIT PRESERVE ROWS;

/* Calculate pi and the diff from the actual value */
SELECT 4.00 * SUM(inCirc)/COUNT(*) AS pi, CAST(pi() - pi AS DECIMAL(4,2)) AS diff
FROM tblRndPts;

Click here to read more about this topic.

Deconstructing an SQL snippet

Recently, I came across the following snippet in a Teradata SQL script that came my way:

select calendar_date
from sys_calendar.calendar
where Calendar_Date Between
(((ADD_MONTHS(DATE,-1)) /100) *100 +1) (DATE) 
AND (((((ADD_MONTHS(DATE,-0)) /100 ) *100 +1) (DATE)) -1) (DATE);

I knew that Teradata, just like Excel, internally saves dates as integers but did not remember the conversion scheme. A quick search later, I had the formula:

Integer Date = (YEAR – 1900) * 10000 + (MONTH * 100) + DAY

So, Teradata internally saves today’s date, i.e. 2010-12-18, as 1101218. Here, year = 110 (110 years since 1900), month = 12 and day = 18.

Now, it was just a matter of taking the expressions apart piece by piece, starting from the innermost expressions.

In the line “(((ADD_MONTHS(DATE,-1)) /100 ) *100 +1) (DATE)”, the function ADD_MONTHS(DATE, -1) is merely adding -1 month to today’s date. Since today is 2010-12-18, the function returns 2010-11-18. It’s internally saved as 1101118.

The result of ADD_MONTHS(), 1101118, is divided by 100. This is an ‘integer’ division and results in 11011. The division removes the day part. If one tries to cast this number to date, Teradata complains, predictably.

In the next step, 11011 is multiplied by 100 to restore the day part, resulting in 1101100. However, there is something seriously wrong with 1101100 as it has ‘00’ for day part, which is illegal. This is why 1 is added to the result of (ADD_MONTHS(DATE,-1)) /100 ) *100 to give the first day of the previous month. The final '(DATE)' merely casts the integer to date.

So, all that the expression “(((ADD_MONTHS(DATE,-1)) /100 ) *100 +1) (DATE)” is doing is calculating the first day of the previous month. Similarly, the expression “( ( (((ADD_MONTHS(DATE,-0)) /100 ) *100 +1) (DATE) ) -1) (DATE)” calculates the last day of the previous month. It first calculates the first day of the current month and subtracts 1 day from it, which results in the last day of the previous month. (The ADD_MONTHS() function is redundant in the second expression as it is basically doing nothing except confound users).

Coincidentally, I had previously written a more intuitive way of achieving the same result. It has the virtue of not using magic numbers:

SELECT CALENDAR_DATE
FROM SYS_CALENDAR.CALENDAR
WHERE CALENDAR_DATE BETWEEN
ADD_MONTHS(CURRENT_DATE, -1) - EXTRACT(DAY FROM CURRENT_DATE) + 1
AND CURRENT_DATE - EXTRACT(DAY FROM CURRENT_DATE);

Correlated Subquery

The NULL issues discussed in the previous entry on subquery can be side-stepped with correlated subqueries. Correlated subqueries are different to subqueries in that for each outer row, the query tries to find a matching inner row. If such an inner row exists, then the outer row is returned. For example,

select *
from t1
where exists
(select *
from t2
where t1.a_col = t2.a_col);

The above query can be optimized as follows:

select *
from t1
where exists
(select '1'
from t2
where t1.a_col = t2.a_col);

Notice that the inner query projects the value '1' if there is a match instead of the whole row, which could have dozens of columns.

What happens if the WHERE clause has NOT EXISTS instead of EXISTS, and the inner query returns NULL? In that case, the outer query returns nothing, which is the expected behavior. Presto, the perils of NULLs are nullified!

Subquery And Null

Last week, I completed a four-day Teradata Advanced Sql course offered by Teradata Australia Pty Ltd and sponsored by my employer. In this series, I will document some of the insights that I gained and the tricks of the trade that I learnt. Today, I will write about some gotchas associated with subqueries.

The following is a garden variety subquery that I write all the time:

SELECT *
FROM t1
WHERE some_col NOT IN (SELECT a_col FROM t2);

The above works as long as the subquery does not return a NULL in what is effectively a comma separated list of values within the pair of parenthesis. However, things go pear-shaped if the subquery returns one or more NULLs in the list. When that happens, the outer query returns nothing.

The outer query compares some_col with each value returned by the subquery.  Say, the subquery returns val1, val2 and NULL. For the outer query to return a row, the condition that needs to be true is this: 

some_col not equal to val1
AND some_col not equal to val2
AND some_col not equal to NULL;

Comparison with a NULL results in NULL, and so does ANDing a value with a NULL. Therefore, the preceding condition never becomes true, and the outer query does not return anything.

NULL is not an issue in the following query (it has 'IN' instead of 'NOT IN' in the WHERE clause):

SELECT *
FROM t1
WHERE some_col IN (SELECT a_col FROM t2);

In the above query, some_col needs to be equal to only one of the values returned by the subquery for the outer query to return a row. In other words, some_col gets ORed with the values returned by the subquery. Therefore, NULL is not an issue.

There are ways to circumvent the problems caused by NULLs in a subquery, and they will be discussed in the next installment of this series.