DB2 Query Token SMALLINT was not valid

Question

Forum.

I am working with IBM System i Version 7.1.

I am having issues in the source code with the following merge statement so I copied it over to the database client to utilize the "Run SQL Scripts" functionality.

Rather than replacing the coded in @Variables in the statement I wanted to declare local variables so that I could test the statement as is.

The added the following 'declare and set' lines and I get the following error:

declare @groupId smallint
set @groupId = 99

declare @groupName varchar(40)
set @groupName = 'Sam'

declare @groupId smallint

SQL State: 42601
Vendor Code: -104
Message: [SQL0104] Token SMALLINT was not valid. Valid tokens: DYNAMIC SENSITIVE ASENSITIVE INSENSITIVE. Cause . . . . . : A syntax error was detected at token SMALLINT. Token SMALLINT is not a valid token. A partial list of valid tokens is DYNAMIC SENSITIVE ASENSITIVE INSENSITIVE. This list assumes that the statement is correct up to the token. The error may be earlier in the statement, but the syntax of the statement appears to be valid up to this point. Recovery . . . : Do one or more of the following and try the request again: -- Verify the SQL statement in the area of the token SMALLINT. Correct the statement. The error could be a missing comma or quotation mark, it could be a misspelled word, or it could be related to the order of clauses. -- If the error token is , correct the SQL statement because it does not end with a valid clause.

Processing ended because the highlighted statement did not complete >successfully

I have tried adding semicolons to the end of each line and begin and end statements and still no success.

Below is the whole statement I am trying to execute:

declare @groupId smallint
set @groupId = 99

declare @groupName varchar(40)
set @groupName = 'Sam'


merge into database.table as t 
                 using ( values( cast(@groupId as smallint) 
                 ,cast(@groupName as varchar(40)) 
                 )) 
                 as caz( group_id
    , group_name
    ) 
                 on t.group_id = caz.group_id 
                 when matched then update
                 set t.group_name = caz.group_name 
                 when not matched then 
                 insert (   group_id
    , group_name
    ) 
                 values (caz.group_id
    , caz.group_name
    );

Any help is appreciated.

Please let me know if I may provide anymore information.

It might be a good idea to read about the correct syntax of [compound statements in the manual](http://www-01.ibm.com/support/knowledgecenter/ssw_ibm_i_71/db2/rbafzcompoundstmt.htm?lang=en). — mustaccio, Jan 12 '16 at 15:43
Not only syntax. The [docs also say this](http://www-01.ibm.com/support/knowledgecenter/ssw_ibm_i_71/db2/rbafzdeclvar.htm?lang=en) about DECLARE VARIABLE: "**Invocation** This statement can only be embedded in an application program." — user2338816, Jan 14 '16 at 01:15

score 0 · Accepted Answer · edited May 23 '17 at 12:18

0

I may have found an answer here: https://stackoverflow.com/a/4451159/2272357

CREATE OR REPLACE VARIABLE variableName VARCHAR(50);
SET variableName = 'blah';
SELECT * FROM table WHERE column = variableName;
DROP VARIABLE variableName;

I have yet to verify it works successfully. I believe it may be a local issue though.

edited May 23 '17 at 12:18

Community

1
1

answered Jan 13 '16 at 22:53

HappyCoding

641
16
36

Silly to code a separate `SET` statement in the given example; just add the `DEFAULT` clause to assign the value during the `CREATE`. So was that verification done? If that was successful, then seems appropriate to accept the answer.? – CRPence Oct 12 '16 at 00:20

DB2 Query Token SMALLINT was not valid

1 Answers1