Why did the numeric value search in the varchar field fail; and a date format display question

Database
Enthusiast

Why did the numeric value search in the varchar field fail; and a date format display question

Can someone tell me the reason why the following is not working -

select count(*)

from table1

where memo_code='STATUS'

and new_txt='1';

but the following works -

select count(*)

from table1

where memo_code='STATUS'

and substr(new_txt,1,1)='1';

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!

8 REPLIES
Enthusiast

Re: Why did the numeric value search in the varchar field fail; and a date format display question

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.

Enthusiast

Re: Why did the numeric value search in the varchar field fail; and a date format display question

Thank you for responding.

BTW, I meant to say "new_txt is a varchar(20) field", not "memo_code". Thanks again.

Enthusiast

Re: Why did the numeric value search in the varchar field fail; and a date format display question

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

Enthusiast

Re: Why did the numeric value search in the varchar field fail; and a date format display question

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.

Junior Contributor

Re: Why did the numeric value search in the varchar field fail; and a date format display question

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';?

Enthusiast

Re: Why did the numeric value search in the varchar field fail; and a date format display question





NEW_TXT Char2HexInt(NEW_TXT)
6 3600000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000

Thank you.

Junior Contributor

Re: Why did the numeric value search in the varchar field fail; and a date format display question

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:

UPDATE tab
SET new_txt = TRIM(TRAILING '00'xc FROM new_txt)
WHERE new_txt LIKE '%' || '00'xc
Enthusiast

Re: Why did the numeric value search in the varchar field fail; and a date format display question

thank you so much!