Dear all,
It seems the simplest way to do a longest match in Teradata is to use like and qualify rank.
However, it causes large spool during its product join.
And, we cannot use Update /w join to update with only the longest match record, as what Oracle does in PL/SQL.
I find a SQL in this archive. But I find its not quite efficient as Oracle. Any better way and comments?
-------------
SELECT
c.called_no
,p.Destination_Tag
FROM
(
SELECT
c.called_no
,p.Destination_Tag
,RANK() OVER (PARTITION BY c.called_no
ORDER BY p.pattern DESC) rnk
FROM
CDRs c
LEFT JOIN Prefixes p
ON c.called_no like p.pattern || '%'
) dt
WHERE rnk = 1;
---------------------------------------