|
View previous topic :: View next topic
|
| Author |
Message |
NielsDev
New User
Joined: 01 Sep 2021
Posts: 2
Location: Netherlands
|
|
|
|
Hi all,
I want to execute the following query:
| Code: |
INSERT
INTO tableA
(id, seq_nb, msg_txt)
VALUES ( DCLPTABA.id
, (Select Max(seq_nb)+1 from tableA where id = DCLPTABA.id)
, DCLPTABA.msg_txt ) |
Now I get sql error code -312 (on the variable in the where clausule)
How can i solve this issue?
Coded for you |
|
| Back to top |
|
 |
sergeyken
Senior Member
Joined: 29 Apr 2008
Posts: 2141
Location: USA
|
|
|
|
Check which of the following situations is applicable to your case?
Nobody at this forum can do it except yourself.
| Quote: |
Short Description: VARIABLE IS NOT DEFINED OR NOT USABLE
The variable variable-name appears in the SQL statement, but one of the following conditions exists:
v No declaration for the variable exists.
v The attributes of the variable are inconsistent with its usage in the SQL statement.
v The host variable was specified in a dynamic SQL statement. Host variables are not valid in dynamic SQL statements.
v A host variable array is used in a context in which it is not allowed. See DB2 SQL Reference for information on where host variable arrays are allowed.
v The variable appeared in one of the triggered SQL statements in a CREATE TRIGGER statement. An OLD transition variable cannot be modified in a trigger body.
v The variable name is used in the body of an SQL procedure (CREATE or ALTER PROCEDURE statement), but the variable is not declared as an SQL variable or parameter. The scope of the SQL variable is the compound statement that contains the declaration, including any compound statements that are nested within that compound statement.
v The variable appeared in one of the key-expressions of an index definition.
System action: The statement cannot be processed.
Programmer response: Do one of the following solutions and try the request again:
v Verify that the program or SQL routine contains a declaration for the variable. Check to see that the variable name is spelled correctly in the SQL statement.
v Verify that the attributes of the variable are compatible with its use in the statement.
v Use parameter markers in a dynamic SQL statement instead of host variables.
v Specify a NEW transition variable when the variable is modified in a trigger.
v Declare the variable as an SQL variable or parameter in the SQL procedure.
v Check the definition of the index and remove any host variables that are specified. |
|
|
| Back to top |
|
|
NielsDev
New User
Joined: 01 Sep 2021
Posts: 2
Location: Netherlands
|
|
|
|
| This is why i asked if it’s allowed to use a where clausule in the select max statement? |
|
| Back to top |
|
|
sergeyken
Senior Member
Joined: 29 Apr 2008
Posts: 2141
Location: USA
|
|
|
|
| NielsDev wrote: |
| This is why i asked if it’s allowed to use a where clausule in the select max statement? |
You can easily check it:
1) by running your internal SELECT as a separate SQL statement, or
2) by reading any SQL reference.
No need to ask the forum. |
|
| Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3076
Location: NYC,USA
|
|
|
|
| NielsDev wrote: |
Hi all,
I want to execute the following query:
INSERT
INTO tableA
(id, seq_nb, msg_txt)
VALUES ( DCLPTABA.id
, (Select Max(seq_nb)+1 from tableA where id = DCLPTABA.id)
, DCLPTABA.msg_txt )
Now I get sql error code -312 (on the variable in the where clausule)
How can i solve this issue? |
Welcome!
1. Please use code tags when you represent code or any data going forward.
2. Where are you running this sql ?
3. If you are running in COBOL program then : is missing across first , third and second ( in where cond).
| NielsDev wrote: |
| This is why i asked if it’s allowed to use a where clausule in the select max statement? |
Yes. Besides , I would suggest to use db2 seq objects to get always unique numbers for every insert than what you are doing. It will save you lot of troubles down the lines. |
|
| Back to top |
|
|
|
|
