Function to auto increment an id in a view

Question

I am creating a function for my database that will allow me to autoincrement a primary key in a view the following is my function

delimiter //
CREATE FUNCTION `func_inc_var_session`(reset BIT) RETURNS int
    NO SQL
    NOT DETERMINISTIC
     begin
      IF reset THEN
        SET @var := 0;
      ELSE
        SET @var := IFNULL(@var,0) + 1;
      END IF;
      return @var;
     end
     //
delimiter ;

How can i change the following function to avoid begining from the last auto incremented number every time i run the view.

I have also tried the following to create my view: i get the error that the select statement has a parameter and thus an error occurs

create view view_calvndt3 as
SELECT 
         @rownum := @rownum + 1  AS `id`,
        `adggtnz`.`lng02_rpt_b_calvedets`.`calvdatealv` AS `calvdate`,
        CONCAT('TZN',
                `adggtnz`.`lng02_rpt_b_calvedets`.`tagprefix`,
                `adggtnz`.`lng02_rpt_b_calvedets`.`tagsec`,
                '-',
                `adggtnz`.`lng02_rpt_b_calvedets`.`name`) AS `calfid`,
        `adggtnz`.`lng02_rpt_b_calvedets`.`damid` AS `damid`
    FROM
        `adggtnz`.`lng02_rpt_b_calvedets`, (SELECT @rownum := 0) r
    WHERE
        ((`adggtnz`.`lng02_rpt_b_calvedets`.`calvdatealv` <> 0)
            AND (NOT ('0000-00-00'))
            AND (CONCAT('TZN',
                `adggtnz`.`lng02_rpt_b_calvedets`.`tagprefix`,
                `adggtnz`.`lng02_rpt_b_calvedets`.`tagsec`,
                '-',
                `adggtnz`.`lng02_rpt_b_calvedets`.`name`) IS NOT NULL));

Why on earth would you increment the primary key yourself??? — juergen d, Oct 23 '18 at 12:40
I am trying to create a view with an auto incrementing number — Mirieri Mogaka, Oct 23 '18 at 12:42
Like a row number starting from 1? Then you could use this: https://stackoverflow.com/a/1895127/575376 — juergen d, Oct 23 '18 at 12:43
That's like building a trebuchet so you can pass the salt. @juergend's link is the way to go. — JNevill, Oct 23 '18 at 12:56
@juergen d I can not create the view because SELECT contains a variable or parameter — Mirieri Mogaka, Oct 23 '18 at 13:16

Function to auto increment an id in a view

0 Answers0