Reg Syntax error 3706

Database
Enthusiast

Reg Syntax error 3706

Hi,

For one of our requirement, following query is being generated automatically by Business Objects:

Select A.XX_ID

From TABLE1 as A

Where A.XX_ID IN

(

Select XX_ID From TABLE1 Where E _DT >= {d '2007-11-03'}

Minus

Select XX_ID From TABLE2 Where E_DT >= {d '2007-11-03'}

)

On running the above query we are getting the following syntax error:

3706: Syntax error: expected something between '">="' and the end of the request.

Output directed to Answerset window

But while running the sub query, the result set is been generated

Select XX_ID From TABLE1 Where E _DT >= {d '2007-11-03'}

Minus

Select XX_ID From TABLE2 Where E_DT >= {d '2007-11-03'}

Kindly let me know your suggestion to overcome this issue.

Regards,

Balamurugan
3 REPLIES
Teradata Employee

Re: Reg Syntax error 3706

Try replacing curly braces with () and also replace d 'yyyy-mm-dd' with date 'yyyy-mm-dd'
Enthusiast

Re: Reg Syntax error 3706

Hi,

We will not be able to overwrite the query, since it is automatically generated by BO.

Is there any other option?

Regards,
Balamurugan
Teradata Employee

Re: Reg Syntax error 3706

Those are ODBC escape sequences for date constants. The DBMS does not recognize that syntax, but if you are using a tool that requires its use, the ODBC driver can parse the query and translate it to supported Teradata syntax. Parsing is enabled by default when you install the driver. Generally I advise people to disable it, because it can have other side effects - like modifying DDL as well as DML - and can cause some confusion since the SQL the database receives isn't always exactly what you thought you sent.

But since it appears you need this feature, double check your ODBC setup to be sure it is enabled:

In a UNIX/Linux .ini file, the option is called NoScan; you can just be sure NoScan is omitted or you can explicitly set NoScan=No. I recommend you also set DateTimeFormat=AAA (not the default III).

In Windows, the option is called Disable Parsing; you should make sure the box is un-checked, and again I'd recommend setting DateTimeFormat=AAA.

In SQL Assistant, the driver setting is overridden by the Query option "Allow use of ODBC SQL Extensions in queries"; be sure the box is checked if you need to submit ODBC escape sequences.