Search for a pattern in a derived table - Teradata like operator and display Single-Quotes

We all know about Pattern-Matching and how it works.

But, in a real life scenario, we have to find matching data from 2 different applications.

The below query was used to compare 2 applications where the 'date' part (last 5 digits) of the Surrogate key differed.

Key take-aways:

1. Use of LIKE ANY keyword

2. How to use "Single-quotes" as Character in Teradata

Query:

SELECT * FROM DW_PROD2.DIM_CUSTOMER

WHERE CAST(CUS_ID_NO AS VARCHAR(20)) LIKE ANY

(

SELECT 

'''' || (SUBSTRING(CAST(A.CUS_ID_NO AS VARCHAR(20)) FROM 1 FOR 11 ) || '%' || ''''

FROM

DW_PROD1.CUSTOMER

WHERE TRS_DT = '2012-11-17'

);

Points to remember:

1. Converting CUS_ID_NO to Varchar was necessary since Pattern-Matching can be done only for Character values

2. The inner query will give results like