SELECT statement works, but can't create view based on it

Database

SELECT statement works, but can't create view based on it

Hi, 

 

I have this simple statement:

 

SyntaxEditor Code Snippet

select
yearmonth,customer
from DB.TB
where yearmonth >= cast(trim(otranslate(year(add_months(current_date, -12)) || case when character_length(cast(month(add_months(current_date, -12)) as varchar(2))) = 1 then oreplace('0'|| month(add_months(current_date, -12)),' ','') else month(add_months(current_date, -12)) end,' ','')) as integer)and customer = 0001

Which runs fine as a select statement. I want to use the current date to extract the yearmonth 'YYYYMM', and use that in the WHERE clause, so it changes month automatically based on the current date (in this case the month 12 months ago).

 

However, when I try to create a view based on this query it fails with the error code  "Failed. 3706:  Syntax error: expected something between '(' and the 'year' keyword.". If I change the WHERE clause to yearmonth >= 201610 for example it works fine.

 

Does anyone have an explanation as to why I get this error, or perhaps a solution? 

 

Thank you in advance!


Accepted Solutions
Teradata Employee

Re: SELECT statement works, but can't create view based on it

I've never heard of the Year() or Month() function, and I can't find it in TD 16.10.  You can Extract(month from current_date).

But this seems far too convoluted anyway.  Have you tried simply:

    where yearmonth >= 190000+add_months(current_date ,-12)/100

1 ACCEPTED SOLUTION
6 REPLIES
Teradata Employee

Re: SELECT statement works, but can't create view based on it

I've never heard of the Year() or Month() function, and I can't find it in TD 16.10.  You can Extract(month from current_date).

But this seems far too convoluted anyway.  Have you tried simply:

    where yearmonth >= 190000+add_months(current_date ,-12)/100

Re: SELECT statement works, but can't create view based on it

Yes I agree it was way too convoluted, but as I said the query worked fine as a select statement only.

 

However, your solution also worked with creating a view, so problem solved. Thank you so much!

Senior Apprentice

Re: SELECT statement works, but can't create view based on it

Hi,

You've got a working solution (which is the important bit) but I think your original "why does this work as a simple select but not when in a view" is possibly because the original select was run via odbc. if that is the case then the odbc driver is translating your code as entered into a Teradata equivalent, I've seen that in a number of customer sites. This may also happen with jdbc and .net but I haven't seen that.

 

However, when you run the 'create view' statement that doesn't happen, and the Teradata database sees the 'year' function and throws the error.

 

If you wanted to prove (or disprove!) this, run the original select and then go into query log to see what was actually received by the dbms. It probably won't be what you entered.

 

Cheers,

Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com

Re: SELECT statement works, but can't create view based on it

Thank you for your answer. I would like to test this, but am unsure as to where you want me to look. By query log I assume you mean the "History", i run my select statement and open the query from the history, however I cannot figure out where the information is.

 

Top box shows the query again, the same way I wrote it, and beneath is stats on the query on the left side, and notes/result message on the right side. I cannot see from this "what was actually received" by the dbms, unless you mean the output itself, which returns what I expected it to return.

 

 

Junior Contributor

Re: SELECT statement works, but can't create view based on it

You need to retrive information from Teradata's QueryLog, when you don't know about it, you probably don't have access.

 

Regarding translation by the ODBC driver, this is only done for SELECT, no DELETE, INSERT etc. and no DDL like CREATE VIEW or CREATE TABLE AS SELECT.

Re: SELECT statement works, but can't create view based on it

Yeah, you are probably right. I'm on the business end of things and have limited access to some of the back-end stuff, and limited knowledge for that matter.

 

We'll let this one sit abit :)