Can someone tell me the reason why the following is not working -
but the following works -
memo_code is a varchar(20) field.
In addition, why does cast(memo_tmstamp as date format 'YYYY-MM-DD') still display in the format as 3/31/2013, instead of 2013-03-31 in Teradata Assitance Answersheet window?
Thank you very much!
Posting the DDL for your table would help as well as the error.
As for the date problem, I believe SQL Assistant has date formatting settings. Tools->Options->Date Format. It is processed in the application, so the format specified in the database does not really matter. Alternatively, you can cast it to a CHAR.
Thank you for responding.
BTW, I meant to say "new_txt is a varchar(20) field", not "memo_code". Thanks again.
Can you please check the length of the New_text column. There might be a space added like the value is '1 ' or something of the like which is not making it to match with '1'. What you can try is if the condition trim(new_txt)='1' is working or not. I second the comments made by cmedved on the date display part
trim(new_txt) did not work either. Only substr(new_txt,1,1) works in this case.
And I tried CHAR2HEXINT(new_txt). No special character is revealed there. Only one digit number in the field. Weird.
Any trailing blank will be ignored during comparison ('1' = '1 '), only a leading blank will not be equal ('1' <> ' 1'), but TRIM removes both leading and trailing blanks.
Can you show the CHAR2HEXINT(new_txt) result for substr(new_txt,1,1)='1';?
That's it, a binary '00' is not a blank '20'.
If it's supposed to be a VARCHAR you probably need to remove the trailing 00 using TRIM(TRAILING '00'xc FROM new_txt)
I would suggest to permanently UPDATE your column with this TRIM, of course plus fixing this issue in your data load:
SET new_txt = TRIM(TRAILING '00'xc FROM new_txt)
WHERE new_txt LIKE '%' || '00'xc