|
'FROM ' || trim(v_LPSERVER) || '.qsys2.asp_info'seems to match the syntax you provided last week:
from ' concat trim(server_name) concat '.loglib.logtable L where ...'
My first time creating an SQL procedure using SQL logic. Can anyone*** CONFIDENTIALITY NOTICE: The information contained in this communication may be confidential, and is intended only for the use of the recipients named above. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution, or copying of this communication, or any of its contents, is strictly prohibited. If you have received this communication in error, please return it to the sender immediately and delete the original message and any copy of it from your computer system. If you have any questions concerning this message, please contact the sender. ***
help me determine where this is going south? The full error:
SQL State: 42703
Vendor Code: -206
Message: [SQL0206] Column or global variable V_LPSERVER not found. Cause .
. . . . : V_LPSERVER was not found as a column of table *N in *N and was
not found as a global variable in *N. If the table is *N, V_LPSERVER
is not a column of any table or view that can be referenced, or
V_LPSERVER is a special register that cannot be set in an atomic compound statement.
Recovery . . . : Do one of the following and try the request again: --
Ensure that the column and table names are specified correctly in the
statement. -- If this is a SELECT statement, ensure that all the
required tables were named in the FROM clause. -- If the column was
intended to be a correlated reference, qualify the column with the correct table designator.
-- If the column was intended to be a global variable, qualify the
name with the schema where the global variable exists or ensure the
schema is in the path. -- If this is a SET statement for a special
register within an atomic compound dynamic statement, remo ve the
statement or remove the ATOMIC keyword.
I'm pretty sure I properly defined global variable V_LPSERVER.
CREATE or REPLACE PROCEDURE Read_LPARS_loop() modifies SQL data not
deterministic language SQL specific AC_WRKASP set option dbgview =
*SOURCE
BEGIN
DECLARE v_LPSERVER VARCHAR(18);
DECLARE v_LPSEQUENCE INT;
DECLARE done INT DEFAULT 0;
DECLARE SQLstatement VARCHAR(1024);
-- Declare a cursor to fetch rows from the LPARS table
DECLARE c_LPARS CURSOR FOR
SELECT LPSERVER, LPSEQUENCE
FROM LPARS
order by LPSEQUENCE;
-- Declare a CONTINUE HANDLER for NOT FOUND condition
DECLARE CONTINUE HANDLER FOR NOT FOUND
SET done = 1;
-- Open the cursor
OPEN c_LPARS;
-- Loop through the LPARS rows
read_loop: LOOP
FETCH c_LPARS INTO v_LPSERVER, v_LPSEQUENCE;
IF done = 1 THEN
LEAVE read_loop;
END IF;
-- Execute the query for each LPARS row
Set SQLstatement = 'INSERT INTO qtemp/WRKASP (SELECT
v_LPSEQUENCE as Seq, v_LPSERVER as Server, ASP_NUMBER, DEVD_NAME, ' ||
'100 - ( Round( DEC(DEC(TOTAL_CAPACITY_AVAILABLE, 21, 3) /
DEC(TOTAL_CAPACITY, 21, 3) * 100, 21, 3), 2) ) as Pct_Used ' ||
'FROM ' || trim(v_LPSERVER) || '.qsys2.asp_info ORDER BY
Asp_Number)';
EXECUTE IMMEDIATE SQLstatement ;
END LOOP;
-- Close the cursor
CLOSE c_LPARS;
END;
As an Amazon Associate we earn from qualifying purchases.
This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].
Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.
midrange.com
