Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to store the result of a select query into a variable(IBM DB2)?

Tags:

db2

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.

like image 308
Alex Avatar asked Nov 01 '16 08:11

Alex


People also ask

How will you store select query result in variable?

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.

How you will store result of query?

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.

What is Sysibm SYSDUMMY1 in db2?

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.

What is fetch in db2?

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.


1 Answers

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;
like image 170
Esperento57 Avatar answered Nov 18 '22 19:11

Esperento57