IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Insert combined with select-max-where


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
NielsDev

New User


Joined: 01 Sep 2021
Posts: 2
Location: Netherlands

PostPosted: Wed Sep 01, 2021 1:19 am
Reply with quote

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
View user's profile Send private message
sergeyken
Warnings : 2

Senior Member


Joined: 29 Apr 2008
Posts: 1231

PostPosted: Wed Sep 01, 2021 1:42 am
Reply with quote

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
View user's profile Send private message
NielsDev

New User


Joined: 01 Sep 2021
Posts: 2
Location: Netherlands

PostPosted: Wed Sep 01, 2021 1:47 am
Reply with quote

This is why i asked if it’s allowed to use a where clausule in the select max statement?
Back to top
View user's profile Send private message
sergeyken
Warnings : 2

Senior Member


Joined: 29 Apr 2008
Posts: 1231

PostPosted: Wed Sep 01, 2021 2:05 am
Reply with quote

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
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 2815
Location: NYC,USA

PostPosted: Wed Sep 01, 2021 9:48 pm
Reply with quote

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
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts problem in select max when executing ... IMS DB/DC 6
No new posts Can we Insert duplicates in Primary U... DB2 2
No new posts insert into session tables with UR DB2 0
No new posts to Insert Rows from File1 to File 2 ... DFSORT/ICETOOL 1
No new posts Need to select all records belonging ... DB2 2
Search our Forums:

Back to Top