Help with error Syntax error: expected something between the beginning of the request and the 'DECLARE' keyword"

Database
Enthusiast

Help with error Syntax error: expected something between the beginning of the request and the 'DECLARE' keyword"

Error "Syntax error: expected something between the beginning of the request and the 'DECLARE' keyword", making me go mad.

Hi all, 

I am trying to execute the below script, but i am getting the above error. Can someone pls, let me know as to what is wrong here? Does Teradata support "SmallDateTime" datatype.

DECLARE @START AS SmallDateTime
DECLARE @END AS SmallDateTime
SET @START = CONVERT(VARCHAR(10), GETDATE() - 1 , 101) + ' 00:00:00' --'04/04/2012 00:00:00'
SET @END = CONVERT(VARCHAR(10), GETDATE() - 1 , 101) + ' 23:59:59' --'04/09/2012 23:59:59'

SELECT * FROM R_C_D WHERE (1=1)
AND DATETIME >= DATEADD(HH,-5,@START)
AND DATETIME <= DATEADD(HH,5,@END);

Appreciate any feedback..

Thanks!

6 REPLIES
Enthusiast

Re: Help with error Syntax error: expected something between the beginning of the request and the 'DECLARE' keyword"

Hi,

Your script is looking as Ms SQL Server script. Teradata has another SQL syntax.

For example, Teradata hasn't SmallDateTime type. You can use Timestamp instead of it.

Teradata has CAST function instead of CONVERT, etc.

Enthusiast

Re: Help with error Syntax error: expected something between the beginning of the request and the 'DECLARE' keyword"

Thanks for the reply back Darych.   I tried doing that too, but getting the same error.

DECLARE @START AS TIMESTAMP
DECLARE @END AS TIMESTAMP

SET @START = CAST(VARCHAR(10), GETDATE() - 1 , 101) + ' 00:00:00' --'04/04/2012 00:00:00'
SET @END = CAST(VARCHAR(10), GETDATE() - 1 , 101) + ' 23:59:59' --'04/09/2012 23:59:59'

SELECT * FROM R_C_D WHERE (1=1)
AND DATETIME >= DATEADD(HH,-5,@START)
AND DATETIME <= DATEADD(HH,5,@END);
Enthusiast

Re: Help with error Syntax error: expected something between the beginning of the request and the 'DECLARE' keyword"

You can't write scripts with variables outside of stored procedures in Teradata.

Please read some forum posts and rewrite your script.

https://forums.teradata.com/forum/database/user-defined-variables

http://forums.teradata.com/forum/database/how-do-you-declare-a-variable-in-teradata-sql

Enthusiast

Re: Help with error Syntax error: expected something between the beginning of the request and the 'DECLARE' keyword"

Thanks for the updae Darych. But fyi. this is not a Stored procedure. I am writing a simple query to fetch the data from the tables, and display on board. I am sure that we can use variables in the script as well, apart from SP?

Junior Contributor

Re: Help with error Syntax error: expected something between the beginning of the request and the 'DECLARE' keyword"

No, Teradata doesn't allow variables outside of Stored Procedures and most DBAs don't allow end users to create SPs (due to possible usage of cursors and loops, which are really bad (= processed serial) in a parallel DBMS).

And the Standard SQL CAST syntax is different from SQL Server's proprietary CONVERT:

CAST(column AS new_datatype)

 I would rewrite you query as

SELECT * FROM R_C_D
WHERE DATETIME >= CAST(CURRENT_DATE AS TIMESTAMP(0)) - INTERVAL '5' HOUR
AND DATETIME < CAST(CURRENT_DATE AS TIMESTAMP(0)) + INTERVAL '29' HOUR

Btw, Teradata has 5 datatypes for DateTime:

DATE = date only

TIME = time only

TIMESTAMP = date + time

TIME and TIMESTAMP are also avalable WITH TIME ZONE

Enthusiast

Re: Help with error Syntax error: expected something between the beginning of the request and the 'DECLARE' keyword"

Thanks for the update Dieter.