Yesterday our DBA's completed the update of our Teradata test environment to version 14. The PDF document Teradata Database - Release Summary - Release 14.0 states, "Multiple WITH/WITH RECURSIVE Clauses: The number of WITH or WITH RECURSIVE request modifiers that can be specified with a Data Manipulation Language (DML) request increases from one to any number."
I currently have a few reports that use multiple recursive views within the SQL. We are not approved to use views we create in production reports, though not prevented. (Our personal database objects can be wiped out by the DBAs at any time without notice.) So, I am trying to take advantage of this new feature. However, I can't get the syntax to work. The query will run when any one of the with recursive statements is included. I can't get it to work when I use more than one. I couldn't find any information about how to use the feature in the PDF document Teradata Database - SQL Data Manipulation Language - Release 14.0. I have done a lot of searching on this forum and the Internet.
I am using SQL Assistant, which is the only tool I am given (besides Crystal Reports.) The error message I am getting when I just list the With Recursive statements sequentially is, "3707: Syntax error, expected something like a 'SELECT' keyword or '(' or a 'NONTEMPORAL' keyword or 'AS' keyword or '(' between ')' and the 'WITH' keyword." I have tried a lot of variations in my syntax based on that error. (When I add the NONTEMPORAL keyword I get, "3706: Syntax error: Temporal operations are not supported on this system.")
Does anyone know how to get this feature to work?
I did finally figure out how this works. A WITH statement will allow multiple sub-statements, including multiple RECURSIVE sub-statements. I had even worked with a support tech at Teradata and he finally concluded that what I wanted to do could not be done. But here is how it works: A WITH statement can have multiple named sub-statements. Each of these sub-statements must be separated by a comma. The word WITH appears only once at the beginning. I was able to do multiple recursive sub-statements in one query. I was able to have one recursive sub-statement call another one that was also recursive. If one of the sub-statements, whether recursive or not, is dependent on another in the WITH statement, the one with the dependency is listed first, and the one upon which it is dependent is listed afterward.