I am trying to save the result of a query into a variable. I am using IBM DB2, but I can only store the result if I am declaring the variable inside a procedure.
My code is:
DECLARE @myvar INTEGER;
SET @myvar = (SELECT MAX(ID) FROM S0SCSQMS.S0SRPTCNAME);
and I receive the following errors: For the first line:"SQL0104N An unexpected token "INTEGER" was found following "DECLARE @myvar ". Expected tokens may include: "END-OF-STATEMENT". LINE NUMBER=1. SQLSTATE=42601"
The error code does not tell me much. I looked for it on the IBM documentation.
Looking forward for an answer.
Thank you.
The syntax for assigning a value to a SQL variable within a SELECT query is @ var_name := value , where var_name is the variable name and value is a value that you're retrieving. The variable may be used in subsequent queries wherever an expression is allowed, such as in a WHERE clause or in an INSERT statement.
Answer. You can store result of one query in a variable and then use it in the next stage of stored procedure. The following example shows how you can store the result of the query in the QRY_STRING variable and then retrieve the value from the query result. --DECLARING RECORDS, VARIABLES.
As the name implies, SYSIBM.SYSDUMMY1 is a dummy table. It contains one row and one column, which is named IBMREQD and contains a value of 'Y'. SYSIBM.SYSDUMMY1 is used extensively in SQL examples on the web and in the Knowledge Center.
The fetch-clause sets a maximum number of rows that can be retrieved. It specifies that an application does not want to retrieve more than fetch-row-count rows, regardless of how many rows there might be in the intermediate result table when this clause is not specified.
try this (work on iseries db2 v7r1)
CREATE OR REPLACE VARIABLE myvar INTEGER ;
SET myvar = (SELECT max( id_xp_dossier) FROM cilgprod.xp_dossier);
DROP VARIABLE myvar;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With