on 03-22-2024 12:58 AM
I am trying to fetch Distinct values into the For loop in SAP HANA using CURSOR and For Loop and I am not getting all the distinct values into the For Loop. Instead of getting 10 distinct names, I am getting only 5 names in my output. Could you please guide me with the correct SQL?
CREATE PROCEDURE DBADMIN.FetchDistinctStrings() LANGUAGE SQLSCRIPT AS BEGIN DECLARE STRING1 VARCHAR(255); DECLARE CURSOR MyTenIDFetch (STRING1 VARCHAR(255)) FOR SELECT DISTINCT EMPLOYEE_NAMES FROM "H_CPO_MAIN"."TABLE_NURO"; OPEN MyTenIDFetch(:STRING1); FETCH MyTenIDFetch INTO STRING1; FOR CUR_ROW AS MyTenIDFetch(STRING1) DO SELECT STRING1 as STRING_NAMES FROM DUMMY; END FOR; CLOSE MyTenIDFetch; END;
@RobertWaywell
I ran the SQL outside and I am getting 10 results. But anyway, I found that my logic is incorrect.
When using CURSOR, we shouldn't (no need) use OPEN, FETCH commands. Achieved the logic by below procedure.
CREATE PROCEDURE DBADMIN.FetchDistinctStrings(IN DAYS INT)
LANGUAGE SQLSCRIPT
AS
BEGIN
DECLARE STRING1 VARCHAR(255);
DECLARE CURSOR MyTenIDFetch (STRING1 VARCHAR(255)) FOR
SELECT DISTINCT EMPLOYEE_NAMES FROM "H_CPO_MAIN"."TABLE_NURO";
FOR CUR_ROW AS MyTenIDFetch(STRING1) DO
SELECT :CUR_ROW.STRING1 as STRING_NAMES FROM DUMMY;
END FOR;
END;
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
72 | |
9 | |
8 | |
7 | |
6 | |
5 | |
5 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.