CODEWITHSUNDEEP
mysql
user3128717
user3128717

Reputation: 27

how to make empty first row of particular column value while selecting rows from MySQL Database Table

NOTE: I want of make empty first row TParent_Group_ID is zero or null, while selecting rows from database table

BEGIN

    DECLARE done INT DEFAULT 0;
    DECLARE next_id INT;
    -- CURSOR TO LOOP THROUGH RESULTS --
    DECLARE cur1 CURSOR FOR SELECT GID FROM civicsoft_group WHERE Parent_Group_ID = GivenID;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
    -- CREATE A TEMPORARY TABLE TO HOLD RESULTS --
    IF initial=1 THEN
        -- MAKE SURE TABLE DOESN'T CONTAIN OUTDATED INFO IF IT EXISTS (USUALLY ON ERROR) --
        DROP TABLE IF EXISTS OUT_TEMP; 
        CREATE TEMPORARY TABLE OUT_TEMP (TGID int,TParent_Group_ID int, TGroup_Name varchar(32),TGroup_Type varchar(1),TProject_Status tinyint(1),TEID int,TEmp_Group_ID int,TEmp_Name varchar(100),TEmp_Type varchar(2));
    END IF;

    -- ADD OURSELF TO THE TEMPORARY TABLE --
    INSERT INTO OUT_TEMP SELECT cg.GID,cg.Parent_Group_ID,cg.Group_Name,cg.Group_Type,cg.Project_Status,ce.EID,ce.Emp_Group_ID,ce.Emp_Name,ce.Emp_Type FROM civicsoft_group cg LEFT JOIN civicsoft_employee ce ON cg.GID = ce.Emp_Group_ID  WHERE cg.GID = GivenID;

    -- AND LOOP THROUGH THE CURSOR --
    OPEN cur1;
    read_loop: LOOP
        FETCH cur1 INTO next_id;

        -- NO ROWS FOUND, LEAVE LOOP --
        IF done THEN
        LEAVE read_loop;
        END IF;

        -- NEXT ROUND --

    SET @@GLOBAL.max_sp_recursion_depth = 255;
    SET @@session.max_sp_recursion_depth = 255; 

        CALL Hierarchy(next_id, 0);     
    END LOOP;

    CLOSE cur1;

    -- THIS IS THE INITIAL CALL, LET'S GET THE RESULTS --
    IF initial=1 THEN



         SELECT * FROM OUT_TEMP;  -- Producing Final Temporary Table output

        -- CLEAN UP AFTER OURSELVES --
        DROP TABLE OUT_TEMP; 
    END IF;
END

Upvotes: 0

Views: 387

Answers (1)

Bohemian
Bohemian

Reputation: 424983

The general solution to creating a special first row is:

select 0, null, null, ...
union all
select *
from ...
where ...

The first row must have exactly the same number and type if columns as the second select. You can choose whatever special values you like for the first row, as long as the value coded is compatible with the column type.

Upvotes: 1

Related Questions