If it works in a plain select statement, why can't I use it as part of a where clause?
Basically I have a table with a bunch of GUIDs in the above dashless format that I need to join to a table with the dash format and this is my attempt at testing the substring code that will be a part of the join operator.
That's not the correct syntax for the Teradata "substring" function. I suspect you are using ODBC (e.g. SQL Assistant) and allowing it to parse the query. When you put the function in the SELECT list the driver can do the processing on the client side and make it appear to be OK, but it can't do that in a WHERE clause.
You need to either use either of the correct syntax variants for "substring", e.g. substr(textcol,4,2) substring(textcol FROM 4 FOR 2)