SQL differences between BTEQ and Queryman 1

[CVENOM]

Maybe someone will know the answers to these questions. We've found recently that there are a few differences in syntax between Queryman and BTEQ. The two most recent ones being:
1. Queryman does not support a "with" clause:

examp:
sel some_nbr
from some_tbl
with sum(some_nbr);

Queryman returns an error <WITH clause not supported. Ignored statement 1>
BTEQ runs the query no problems

2. substring function

examp:
sel substring(some_string,1,5)
from some_tbl;

queryman runs this query no problem
BTEQ will not run until the sel statement is changed to -
sel substring(some_string from 1 for 5)

These are the recent one's we've found. Does anyone know why this happens? (different parsers is my guess) Is there a list which contains the syntax differences between the two products?

(The problem we're running into, is we test individual modules in Queryman, due to ease of use and speed, then combine modules that have run successfully into a batch job to run under BTEQ. Some of these jobs run in excess of 12 hours, so if the job &quot;blows up&quot; at any point during the batch run, the whole process must be reaccomplished)

Any help or suggestions would be greatly appreciated.

 

[tdgulbra]

The parser is the same parser, you are just supplying 2 different parameters. From your examples, it looks like you have Queryman set up to not allow Teradata extensions (&quot;WITH&quot; is just such an extension).

For the substring example, the ODBC driver behind Queryman is probably changing the syntax to the ANSI syntax which is what &quot;works&quot; in BTEQ.

Bottom line: It looks like you have the database set up as being only ANSI compliant, but you are trying to use Teradata extensions.

 

[dnoeth]

&quot;1. Queryman does not support a &quot;with&quot; clause&quot;

WITH is only supported by BTEQ, it returns data in a non-relational way, which can't be handled by Queryman. So there's no way to use it in QueryMan.


&quot;2. substring(some_string,1,5)&quot;

This is no Teradata SQL, it's ODBC SQL, it only works in QueryMan when you enable
Option -> Query -> &quot;Allow use of ODBC SQL Extensions in queries&quot;
Then the ODBC driver replaces it with an Teradata SQL equivalent funtion &quot;substring(some_string from 1 for 5)&quot;.
Please check the ODBC User Guide manual, table 4-16 for supported ODBC functions.
But as you already noticed, i wouldn'd use it, because the query won't run in a different environment, even in QueryMan when the option is changed.

Dieter

 

[CVENOM]

Thanks, to both of you.