0

i have created a view using the following query:

CREATE 
    VIEW `localmysql`.`view_system_property` AS
    SELECT 
        `t`.`ID` as `ID`,
        `t`.`PROPERTY_NAME` AS `PROPERTY_NAME`,
        `t`.`PROP_VALUE` AS `PROP_VALUE`,
        `t`.`PROJECT_GROUP_CODE` AS `PROJECT_GROUP_CODE`,
    FROM
        (SELECT func_inc_id() as `ID`,
            `sp`.`PROPERTY_NAME` AS `PROPERTY_NAME`,
                `sp`.`PROP_VALUE` AS `PROP_VALUE`,
                `sp`.`PROJECT_GROUP_CODE` AS `PROJECT_GROUP_CODE`,
                `sp`.`PROJECT_CODE` AS `PROJECT_CODE`,
        FROM
            `localmysql`.`system_property` `sp` 
        UNION 
        SELECT func_inc_id() as `ID`,
            `sps`.`PROPERTY_NAME` AS `PROPERTY_NAME`,
                `sps`.`PROP_VALUE` AS `PROP_VALUE`,
                `sps`.`PROJECT_GROUP_CODE` AS `PROJECT_GROUP_CODE`,
                `sps`.`PROJECT_CODE` AS `PROJECT_CODE`,
        FROM
            `localmysql`.`system_property_secured` `sps`) `t`;

the function used is created as follows :

CREATE FUNCTION `func_inc_id`() RETURNS int
    NO SQL
    NOT DETERMINISTIC
     begin
      SET @var := @var + 1;
      return @var;
     end
     //

then i set the variable value as SET @var=0;

everytime i do a select * fromview_system_property; the values of the id column are incremented. For instance if the entire view has 100 rows..first time when i do a select * the value of id starts from 1 to 100, second call gives me id from 101 to 200 and so on...

The problem is that the view should have a fixed set of records, why does the value get incremented on doing a select * from ? the view is already created, then why does the values change on calling the view

raina77ow
  • 103,633
  • 15
  • 192
  • 229
NehalM
  • 43
  • 1
  • 8
  • I'd surmise the function cache's the var value for the duration of the uptime of the server. Thus the var persists. Instead if you were to use a cross join, each time the query runs I believe the variable would be reset.http://stackoverflow.com/questions/1895110/row-number-in-mysql – xQbert Mar 17 '17 at 13:53
  • @xQbert : using a cross join will get me data horizontally across the tables...but i am trying to get data vertically across the tables...for example system_property table has 100 rows and system_property_secured has 50 rows, the view should contain 150 rows...the columns of both the tables are same... – NehalM Mar 17 '17 at 14:42
  • I guess I should have said, use a cross join (select var declaration) instead of the function call. – xQbert Mar 17 '17 at 14:46

0 Answers0