All Forums Database
trail 5 posts Joined 09/06
05 Aug 2014
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!

cmedved 24 posts Joined 02/14
05 Aug 2014

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.

trail 5 posts Joined 09/06
06 Aug 2014

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

krishaneesh 140 posts Joined 04/13
06 Aug 2014

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

trail 5 posts Joined 09/06
06 Aug 2014

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.

dnoeth 4628 posts Joined 11/04
06 Aug 2014

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

Dieter

trail 5 posts Joined 09/06
06 Aug 2014

NEW_TXT

Char2HexInt(NEW_TXT)

6

3600000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000

Thank you.

dnoeth 4628 posts Joined 11/04
07 Aug 2014

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

 
 

Dieter

trail 5 posts Joined 09/06
07 Aug 2014

thank you so much!

You must sign in to leave a comment.