1

I'm having trouble getting the FETCH statement to work correctly in the code below. It's actually not putting any data into the variable registerName. registerName has the same value as it has before the FETCH statement. Thanks!

-- Declare variables/cursors needed for building pivot query
DECLARE qry VARCHAR(8000);
DECLARE registerName VARCHAR(128) DEFAULT '';
DECLARE done BOOLEAN DEFAULT 0;

DECLARE registers CURSOR
FOR
SELECT RegisterName
FROM Register r
INNER JOIN EgaugeDevice ed ON ed.id = r.EgaugeDeviceId
INNER JOIN Site s ON s.id = ed.SiteId
INNER JOIN Facility f ON f.id = s.FacilityId
WHERE ShowInSite = 1 AND FacilityName = FACILITY;

DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;

-- Use temporary table to get results from instantaneous view
CREATE TEMPORARY TABLE IF NOT EXISTS instData (
    id INT,
    RegisterId INT,
    InstantaneousValue BIGINT,
    Date_Time DATETIME,
    Time_Stamp BIGINT
);

TRUNCATE TABLE instData;

INSERT INTO instData(id, RegisterId, InstantaneousValue, Date_Time, Time_Stamp)
SELECT id, RegisterId, InstantaneousValue, Date_Time, Time_Stamp
FROM vRegisterDataInstantaneous
WHERE Date_Time >= now() - INTERVAL 1 DAY
ORDER BY Time_Stamp DESC;

-- build pivot query from registers listed in Register table
OPEN registers;

FETCH registers INTO registerName;

select registerName AS Test;

CLOSE registers;

1 Answer 1

1

Is it possible to not have the same name for the column and the variable RegisterName? This could be causing a conflict, Local Variable Scope and Resolution

Sign up to request clarification or add additional context in comments.

1 Comment

That was it. I changed registerName to reg and it's working now

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.