Union All not possible in Database View?

Database
Junior Supporter

Union All not possible in Database View?

When I union all two simiple sql statements the first one gets the results for one OS_Type and the second gets the results for the other OS_Type, which is exactly the results I want.

 

When I try to put the exact same SQL in a database view I get the error "Syntax error, expected something like a 'SUCCEEDS' keyword or a 'MEETS' keyword or a 'PRECEDES' keyword or an 'IN' keyword or a 'CONTAINS' keyword between the word 'OS_TYPE' and '|'.

 

Is it not possible to use a union all in a view?


Accepted Solutions
Junior Supporter

Re: Union All not possible in Database View?

I figured out the issue, the view doesn't like me using the exclamation mark for checking a field.

 

FAILS:

WHERE OS_TYPE !='Android'

 

WORKS:

WHERE OS_TYPE <>'Android'

 

Can Teradata please make a better error message or allow the exclamation mark for checking a field?

1 ACCEPTED SOLUTION
7 REPLIES
Teradata Employee

Re: Union All not possible in Database View?

It has always been possible to do union all in a view.  The details of the message might be significant - how is the '|' used?  It is likely a syntax issue.  If you share the DDL someone might be able to spot the problem.

Junior Supporter

Re: Union All not possible in Database View?

Here is the DDL:

 

CREATE SET TABLE myDatabase.myTable
     (
       TEST_DATE TIMESTAMP(0)
      ,OS_TYPE VARCHAR(7)
     )
PRIMARY INDEX ( TEST_DATE, OS_TYPE );

Teradata Employee

Re: Union All not possible in Database View?

And the Create View DDL?

Junior Supporter

Re: Union All not possible in Database View?

I figured out the issue, the view doesn't like me using the exclamation mark for checking a field.

 

FAILS:

WHERE OS_TYPE !='Android'

 

WORKS:

WHERE OS_TYPE <>'Android'

 

Can Teradata please make a better error message or allow the exclamation mark for checking a field?

Teradata Employee

Re: Union All not possible in Database View?

Good idea! You should open an incident on tays.teradata.com to this effect and request a change.

Junior Supporter

Re: Union All not possible in Database View?

Junior Contributor

Re: Union All not possible in Database View?

!= is not valid Teradata/Standard SQL, you probably used an ODBC connection which automatically (based on some settings) modifies it to valid <>.

But this is done only for standalone Selects, but not within DDL statements.

 

See ODBC SQL Grammar and Teradata ODBC Specific Comparison Operators (deprecated in 14.10)