Auto Incremental serial number for MySQL View

Question

Having a issue with my project need to insert an auto incremental value for my MySQL view, I would be nice if you guys help in solving this obstacle, Here is the code in which I wanna have auto incremental serial number (say S.No) as the first column.

    CREATE 
    ALGORITHM = UNDEFINED 
    DEFINER = `srems_admin`@`localhost` 
    SQL SECURITY DEFINER
VIEW `emp_elec_consumption_view` AS
    SELECT 
        `t1`.`PFNUMBER` AS `PFNUMBER`,
        `emp`.`EMPNAME` AS `EMPNAME`,
        `t1`.`MonthAndYear` AS `MonthAndYear`,
        `qt`.`QTRSCODE` AS `QTRSCODE`,
        `t1`.`UNITS_CONSUMED` AS `UNITS_CONSUMED`,
        (`t2`.`FIXED_COMPONENT` + (`t1`.`UNITS_CONSUMED` * `t2`.`RATE_COMPONENT`)) AS `Amount`
    FROM
        (((`srems`.`mstqtroccu` `qt`
        JOIN `srems`.`mstemp` `emp`)
        JOIN `srems`.`msttariffrate` `t2`)
        JOIN (SELECT 
            `srems`.`tranmeterreading`.`PFNUMBER` AS `PFNUMBER`,
                (`srems`.`tranmeterreading`.`CLOSINGREADING` - `srems`.`tranmeterreading`.`OPENINGREADING`) AS `UNITS_CONSUMED`,
                CONCAT(CONVERT( IF((LENGTH(MONTH(`srems`.`tranmeterreading`.`READINGDATE`)) > 1), MONTH(`srems`.`tranmeterreading`.`READINGDATE`), CONCAT('0', MONTH(`srems`.`tranmeterreading`.`READINGDATE`))) USING UTF8), '/', RIGHT(YEAR(`srems`.`tranmeterreading`.`READINGDATE`), 2)) AS `MonthAndYear`,
                (SELECT 
                        `t`.`TRANSACTIONID`
                    FROM
                        `srems`.`msttariffrate` `t`
                    WHERE
                        (`t`.`TORANGE` > (`srems`.`tranmeterreading`.`CLOSINGREADING` - `srems`.`tranmeterreading`.`OPENINGREADING`))
                    LIMIT 1) AS `tariffplanid`
        FROM
            `srems`.`tranmeterreading`) `t1`)
    WHERE
        ((`t1`.`tariffplanid` = `t2`.`TRANSACTIONID`)
            AND (`t1`.`PFNUMBER` = `qt`.`PFNUMBER`)
            AND (`t1`.`PFNUMBER` = `emp`.`PFNUMBER`))

Pls insert the things at the correct place and post it as an comment to get S.No which should be auto-incremental starting from 1 and also it should be the first column, ty in advance

score 0 · Answer 1 · answered Mar 23 '17 at 14:51

0

Your view has no chance of working in MySQL anyway so you might as well give up.

MySQL does not allow subqueries in the FROM clause. And your query is pretty complicated with lots of subqueries.

It also does not allow variables, so getting a row number is rather complicated.

answered Mar 23 '17 at 14:51

Gordon Linoff

1,242,037
58
646
786

Ups, why can't use variables in the view? – Juan Carlos Oropeza Mar 23 '17 at 14:53
@JuanCarlosOropeza . . . I don't know. Ask the people who wrote MySQL. – Gordon Linoff Mar 24 '17 at 01:53

Auto Incremental serial number for MySQL View

1 Answers1