CODEWITHSUNDEEP
teradata
Jackyz
Jackyz

Reputation: 165

peculiar teradata clause, Column_Name >= ''

I have a statament wrote for teradata by someone who don't work here anymore, so i can't ask him directly.
In this statament, the last clause in the where is : ... and Column_Name >= '' .I have no clue about what should that clause do.
If it matter Column_name is a [decimal](7,0) NULL.

Can someone explain to me which case are accepted and which are refused by that clause ?
to me it seams that should allow trough everything since everything is major or equal to null

Upvotes: 1

Views: 44

Answers (2)

k.asish kumar patro
k.asish kumar patro

Reputation: 21

When >='' clause is applied on a character column then it will restrict the null records.

If the table has 2 columns one Region_cd & rank_nr and the data in the table is as below:-

select * from databasename.tablename;

 *** Query completed. 5 rows found. Two column returned. 
 *** Total elapsed time was 1 second.

Region    |      rank_nr
----------     -----------
emea      |          1
amr       |          2
apac      |          3
?         |          4
          |          5

? is represents NULL and space in region_cd column for rank=5 is not visible

If we query in this table with where clause as Region>='' then it will result below 4 rows:-

select * from databasename.tablename where Region_cd >='' ;
 *** Query completed. 4 rows found. 2 columns returned. 
 *** Total elapsed time was 1 second.

col1      |    rank_nr
---------- -----------
emea      |          1
apac      |          3
amr       |          2
          |          5

Upvotes: 0

dnoeth
dnoeth

Reputation: 60482

When you compare a string to a numeric value the string is converted to a FLOAT, in your case the empty string '' is treated as 0, so this is just a stupid way to check for Column_Name >= 0 and filters negative values and NULL. You never know if this was actually the intention of the guy who wrote it :)

Upvotes: 1

Related Questions