CODEWITHSUNDEEP
sqlteradata
user3867061
user3867061

Reputation: 73

CREATE VIEW returns error "date does not match a defined type name", but the actual query runs normally

Using Teradata SQL, I'm running a query against an 'Order History' table I loaded, which involves finding the most recent order where a particular change occured. I ran into some trouble as the Order_Date was loaded as VARCHAR with varying lengths, however I've gotten around this by using CASE WHEN to align the character lengths of the dates and casting the column as a timestamp.

I now want to save this query as a VIEW, however the CREATE VIEW statement fails as the date "does not match a Defined Type name". I'm not sure why this error is occurring as the actual statement runs fine?

Could someone help point out what I am missing?

Create VIEW DB.ViewName as (

select Serv_No, Serv_Attrib, Order_Activ_Date
from (
select Serv_No, Serv_Attrib,
    cast
(
CASE WHEN char_length(Order_Act_Date) = 21 AND index(order_act_Date,' ') = 10
    THEN '0' || '' || Order_Act_Date

        WHEN char_length(Order_Act_Date) = 20 AND index(order_act_Date,' ') = 10
    THEN '0' || '' || substr(Order_Act_Date,1,10) || '0' || substr(Order_Act_Date,length(order_act_Date) - 10 + 1,10)

        WHEN char_length(Order_Act_Date) = 21 AND index(order_act_Date,' ') = 11
      THEN substr(Order_Act_Date,1,10) || ' 0' ||    substr(Order_Act_Date,length(order_act_Date) - 10 + 1,10)

        WHEN Order_Act_Date IS NULL
    THEN '01/01/1900 11:00:00 AM'           

        WHEN char_length(Order_Act_Date) = 22
    THEN Order_Act_Date

END as timestamp format 'DD/MM/YYYYBHH:MI:SSBT'

) as Order_Activ_Date
    from DB.Table
    Qualify
    Coalesce( max(Serv_Attrib) OVER (Partition By Serv_No ORDER     BY ORDER_ACTIV_DATE DESC ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING
                        ) ,Serv_Attrib || 'x') <> Serv_Attrib
                        ) as bb


Qualify rank() over (partition by Serv_No ORDER BY Order_Activ_Date        DESC) = 1

)

Upvotes: 0

Views: 5722

Answers (1)

dnoeth
dnoeth

Reputation: 60472

You probably run this in an ODBC connection, then it's due to the LENGTH function. LENGTH is an ODBC-function which is replaced by the ODBC-driver with correct Teradata SQL, but only for SELECTs and not within DDL.

So simply change the remaining LENGTH to CHAR_LENGTH, too.

Btw, if you're on TD14, you can utilize Oracle's TO_TIMESTAMP, which is more flexible regarding single digit day/hour/minute/second. Following will cast all cases without the need to add leading zeroes:

TO_TIMESTAMP(Order_Act_Date, 'DD/MM/YYYY HH:MI:SS AM')

Upvotes: 1

Related Questions