CODEWITHSUNDEEP
mysqlvariablesview
Smudger
Smudger

Reputation: 10771

mysql create view with mysql query that contains variable

I have the below syntax that I want to use to create a MySQL view:

create view `ViewName` as (select
              v_starting.callingname,
              v_starting.geofence,
              v_starting.`updatetime`,
              @lastGroup := @lastGroup + if( @lastAddress = v_starting.geofence 
                                         AND @lastVehicle = v_starting.callingname, 0, 1 ) as GroupSeq,
              @lastVehicle := v_starting.callingname as justVarVehicleChange,
              @lastAddress := v_starting.geofence as justVarAddressChange
           from
              v_starting,
              ( select @lastVehicle := '',
                       @lastAddress := '',
                       @lastGroup := 0 ) SQLVars

           order by
              v_starting.`updatetime` )

This fails with error:

#1351 - View's SELECT contains a variable or parameter

How can I get around this? Thanks a million.

Upvotes: 2

Views: 5876

Answers (3)

King Saozer
King Saozer

Reputation: 103

for variable using, try to use a stored procedure or a custom function

Upvotes: 0

Kickstart
Kickstart

Reputation: 21513

Think you would need to do this using a subselect to count the records.

Something like this:-

SELECT v_starting.callingname,
              v_starting.geofence,
              v_starting.`updatetime`,
              Sub1.PrevRecCount ASGroupSeq
FROM v_starting
INNER JOIN (SELECT a.updatetime, COUNT(DISTINCT b.callingname, b.geofence ) AS PrevRecCount
            FROM v_starting a 
            LEFT OUTER JOIN v_starting b
            ON a.updatetime > b.updatetime
            AND a.callingname != b.callingname
            AND a.geofence != b.geofence
            GROUP BY a.updatetime) Sub1

Not 100% sure about this as I am dubious about how you are ordering things to get your count presently.

Upvotes: 1

eggyal
eggyal

Reputation: 125835

As documented under CREATE VIEW Syntax:

A view definition is subject to the following restrictions:

[ deletia ]

  • The SELECT statement cannot refer to system or user variables.

Upvotes: 4

Related Questions