CODEWITHSUNDEEP
nullteradata
Melissa Pazen
Melissa Pazen

Reputation: 77

How to remove null date data in Teradata 16.20

I have a number of rows in which date fields are null. I need to include these rows in my answer set. The only things we've managed to accomplish is to set a "placeholder date" (01-01-1900) and remove them in Excel. I want Teradata NOT to yield "?"; I want the equivalent of ''.

Here's an exact example: , CASE WHEN SOA.Performed_date IS NULL THEN '' ELSE SOA.Performed_date END AS Sequence_of_Activities

The only code I've gotten to work is: , CASE WHEN SOA.Performed_date IS NULL THEN '0001-01-01' ELSE SOA.Performed_date END AS Sequence_of_Activities

Here's a similar working example, equally unsatisfying:

, Max(CASE WHEN Care_Activity_Type_ID = 435 
        THEN PERFORMED_DATE 
        ELSE DATE '0001-01-01' 
    end) Annl_Reassess

The error for the first example is: Datatype mismatch in expression.

Upvotes: 0

Views: 1346

Answers (2)

hhoeck
hhoeck

Reputation: 371

In fact Teradata RDBMS returns NULL, not ?. So you can ensure in RDBMS that not NULL is delivered (as Dieter wrote above using COALESCE).

But in SQL Assistant & TD Studio you can change how NULL is displayed (and exported). Maybe you're looking to change this setting. :) In TD Studio it's Settings -> Teradata Datatools -> Result Set Viewer -> Data Format -> Display NULL Value as: -> >here you are<

Upvotes: 0

dnoeth
dnoeth

Reputation: 60482

If you want to return '' instead of null you must cast the date to a string:

coalesce(to_char(Performed_date, 'yyyy-mm-dd'), '')

Btw, the ? representing NULL is a client setting.

Upvotes: 1

Related Questions