Data type does not match a Defined Type name error

Database
Enthusiast

Data type does not match a Defined Type name error

Hi I have below piece of code 

DATABASE ndeshpande;
SELECT DISTINCT
Click.Pgm_Cd
,Click.Send_Job_ID
, Click.Send_URL_Id
, Click.ET_Subscr_Key
, Click.URL_Val
, CASE
WHEN Click.URL_Val LIKE '%artist%'
THEN SUBSTR (Click.URL_Val, INDEX(Click.URL_Val,'artist/')+7, 10 )
ELSE NULL
END
, CASE
WHEN Click.URL_Val LIKE '%event%'
THEN SUBSTR (Click.URL_Val, INDEX(Click.URL_Val,'event/')+6, 16)
ELSE NULL
END
,Click.Email_Addr_Txt
,Patron.patron_id
,patron.Patron_Lkup_ID
,click.click_dttm
FROM VW_ClickPref_DataLoad AS Click
INNER JOIN pextgvw.et_patron_LN AS patron
ON click.ET_Subscr_Key = CAST (patron.et_subscr_id AS VARCHAR(50))
AND patron.PGM_Cd = Click.PGM_Cd
LEFT OUTER JOIN VW_ClickPref_URLExtract AS ex
ON ex.Email_Addr_Txt = Click.Email_Addr_Txt
AND ex.Send_Job_ID = Click.Send_Job_ID
WHERE ex.Email_Addr_Txt IS NULL;

and it is giving me the error mentioned below:

Select Failed. [3706] Syntax error: Data type "P" does not match a Defined Type name.

I am not seeing this issue anywhere. Can someone please help me identify the issue here?

-Nik

Tags (1)
5 REPLIES
Enthusiast

Re: Data type does not match a Defined Type name error

Ok. Now this is getting weird. 

I am getting this error for any piece of code I write.. Please help.

Teradata Employee

Re: Data type does not match a Defined Type name error

Try pasting the SQL into Notepad, then copy the "plain text" version and paste it back into SQL Assistant.

Teradata Employee

Re: Data type does not match a Defined Type name error

When you copy SQL from other tool (mostly) it can have some special characters. You might want to try pasting the SQL in Notepad++ or other tool which can show hidden characters as well.

Enthusiast

Re: Data type does not match a Defined Type name error

Thanks a lot guys. This was right. 

Also, I got to know another quirk in TD 13.10 that for the code above(i.e. using a function inside) needs to have below piece at the top. Is this for TD13.1 or other versions as well?

DATABASE <database_name>;

Teradata Employee

Re: Data type does not match a Defined Type name error

The DATABASE statment provides a default qualifier for unqualified object names (such as the view names in your example); it's not directly related to using a function. This is true for all releases.