HOW TO PROCESS iN FOR LOOP?

laknar · #1 (**permalink**) 04-06-09, 01:36

DECLARE STRUNG VARCHAR(50);
DECLARE SGROUP VARCHAR(50);

SELECT PRODUCT,PRODUCTGROUP INTO STRUNG ,SGROUP FROM SCHEMA.PRODUCT WHERE STATUS='ACTIVE';

The above will many records

i have to process this by using for loop
Please provide sample syntax to proceed furthur.

tonkuma · #2 (**permalink**) 04-06-09, 02:51

The example of FOR statement in "SQL Reference Volume 2" may help you.

Quote:

Examples:

In the following example, the for-statement is used to iterate over the entire
employee table. For each row in the table, the SQL variable fullname is set to the
last name of the employee, followed by a comma, the first name, a blank space,
and the middle initial. Each value for fullname is inserted into table tnames.

Code:

  BEGIN ATOMIC
    DECLARE fullname CHAR(40);
    FOR vl AS
      SELECT firstnme, midinit, lastname FROM employee
        DO
        SET fullname = lastname CONCAT ’,’
          CONCAT firstnme CONCAT ’ ’ CONCAT midinit;
        INSERT INTO tnames VALUES (fullname);
    END FOR;
  END

stolze · #3 (**permalink**) 04-06-09, 06:55

I would like to challenge the statement "i have to process this by using for loop". Typically, that is not true and you can often write the procedural logic in a single, straight-forward SQL statement. So it would be helpful telling us what you finally want to do before you head of in the sub-optimal direction.

tonkuma · #4 (**permalink**) 04-06-09, 09:29

I agree with Stolze.

The example of FOR statement in "SQL Reference Volume 2"

Code:

  BEGIN ATOMIC
    DECLARE fullname CHAR(40);
    FOR vl AS
      SELECT firstnme, midinit, lastname FROM employee
        DO
        SET fullname = lastname CONCAT ’,’
          CONCAT firstnme CONCAT ’ ’ CONCAT midinit;
        INSERT INTO tnames VALUES (fullname);
    END FOR;
  END

can be written in an INSERT statement.

Code:

INSERT INTO tnames
  SELECT lastname CONCAT ’,’
    CONCAT firstnme CONCAT ’ ’ CONCAT midinit FROM employee;

Internet Services

Web Development

Digital Marketing

Consumer Tech