MySQL having variable in view I have a query that I want to save as a view because it will be used on a different system that we don't have access to. The query basically gets running balances on debit and credit columns from a table.

The query works fine,here is the query

SELECT id,deb_id,trans_date,trans_ref,dr,cr,
    @bal := @bal + (dr -cr) AS `Balance`
FROM debtor_transactions a , (SELECT @bal := 0) var    
ORDER BY a.id ASC


But I cannot create a view from the above query, I keep getting this error.

Error Code: 1351
View's SELECT contains a variable or parameter


How can I get around this, or is there a way I can rewrite my query without the variable and still achieve the same result?
It should be possible to write this as a query without using the variable.  Assuming the following setup (slightly simplified - column "trans_ref" omitted), tested with MySQL 5.7 and MariaDB 10.2, respectively:

create table dt (
  id int(11) not null primary key
, debtor_id int(11)
, dr decimal(10,2)
, cr decimal(10,2)
, trans_date date 
) ;

insert into dt (id, debtor_id, dr, cr, trans_date) values 
(1,100,8100.00,0.00,'2017-08-03'),(2,100,15100.00,0.00,'2017-08-08'),
(3,222,1200.00,0.00,'2017-08-11'),(4,222,3300.00,0.00,'2017-08-19'),
(5,300,4220.00,0.00,'2017-09-06'),(6,100,10100.00,0.00,'2017-09-25'),
(7,100,0.00,10000.00,'2017-09-25'),(8,222,10200.00,0.00,'2017-10-15'),
(9,300,10300.00,0.00,'2017-10-16'),(10,222,0.00,11000.00,'2017-10-26'),
(11,100,10400.00,0.00,'2017-10-26'),(12,300,0.00,15000.00,'2017-10-26'),
(13,222,10500.00,0.00,'2017-10-27'),(14,222,20000.00,0.00,'2017-10-27'),
(15,100,0.00,30000.00,'2017-11-01'),(16,222,0.00,22000.00,'2017-11-02'),
(17,300,0.00,13500.00,'2017-11-12'),(18,100,11000.00,0.00,'2017-11-15'),
(19,222,22000.00,0.00,'2017-11-22'),(20,300,33000.00,0.00,'2017-11-24');


Using your approach (with the variable) we get:

mysql&gt; set @bal := 0;
Query OK, 0 rows affected (0.00 sec)

mysql&gt; 
mysql&gt; SELECT id,debtor_id,trans_date,dr,cr,
    -&gt;     @bal := @bal + (dr -cr) AS `Balance`
    -&gt; FROM dt a , (SELECT @bal := 0) var    
    -&gt; ORDER BY a.id ASC;
+----+-----------+------------+----------+----------+----------+
| id | debtor_id | trans_date | dr       | cr       | Balance  |
+----+-----------+------------+----------+----------+----------+
|  1 |       100 | 2017-08-03 |  8100.00 |     0.00 |  8100.00 |
|  2 |       100 | 2017-08-08 | 15100.00 |     0.00 | 23200.00 |
|  3 |       222 | 2017-08-11 |  1200.00 |     0.00 | 24400.00 |
|  4 |       222 | 2017-08-19 |  3300.00 |     0.00 | 27700.00 |
|  5 |       300 | 2017-09-06 |  4220.00 |     0.00 | 31920.00 |
|  6 |       100 | 2017-09-25 | 10100.00 |     0.00 | 42020.00 |
|  7 |       100 | 2017-09-25 |     0.00 | 10000.00 | 32020.00 |
|  8 |       222 | 2017-10-15 | 10200.00 |     0.00 | 42220.00 |
|  9 |       300 | 2017-10-16 | 10300.00 |     0.00 | 52520.00 |
| 10 |       222 | 2017-10-26 |     0.00 | 11000.00 | 41520.00 |
| 11 |       100 | 2017-10-26 | 10400.00 |     0.00 | 51920.00 |
| 12 |       300 | 2017-10-26 |     0.00 | 15000.00 | 36920.00 |
| 13 |       222 | 2017-10-27 | 10500.00 |     0.00 | 47420.00 |
| 14 |       222 | 2017-10-27 | 20000.00 |     0.00 | 67420.00 |
| 15 |       100 | 2017-11-01 |     0.00 | 30000.00 | 37420.00 |
| 16 |       222 | 2017-11-02 |     0.00 | 22000.00 | 15420.00 |
| 17 |       300 | 2017-11-12 |     0.00 | 13500.00 |  1920.00 |
| 18 |       100 | 2017-11-15 | 11000.00 |     0.00 | 12920.00 |
| 19 |       222 | 2017-11-22 | 22000.00 |     0.00 | 34920.00 |
| 20 |       300 | 2017-11-24 | 33000.00 |     0.00 | 67920.00 |
+----+-----------+------------+----------+----------+----------+
20 rows in set (0.00 sec)


Suggestion: create 2 views, one for getting the "running totals" encompassing all debtor_ids, and another one for obtaining "specific" balances for one or more debtor_ids.  

1 running totals: 

create view dtview_all
as
select 
  T.id id
, T.debtor_id
, T.trans_date
, T.dr
, T.cr
, ( select sum(dr - cr)
    from dt 
    where id &lt;= T.id 
  ) _balance_
from  dt T; 


Testing the view:

mysql&gt; select * from dtview_all order by id;
+----+-----------+------------+----------+----------+-----------+
| id | debtor_id | trans_date | dr       | cr       | _balance_ |
+----+-----------+------------+----------+----------+-----------+
|  1 |       100 | 2017-08-03 |  8100.00 |     0.00 |   8100.00 |
|  2 |       100 | 2017-08-08 | 15100.00 |     0.00 |  23200.00 |
|  3 |       222 | 2017-08-11 |  1200.00 |     0.00 |  24400.00 |
|  4 |       222 | 2017-08-19 |  3300.00 |     0.00 |  27700.00 |
|  5 |       300 | 2017-09-06 |  4220.00 |     0.00 |  31920.00 |
|  6 |       100 | 2017-09-25 | 10100.00 |     0.00 |  42020.00 |
|  7 |       100 | 2017-09-25 |     0.00 | 10000.00 |  32020.00 |
|  8 |       222 | 2017-10-15 | 10200.00 |     0.00 |  42220.00 |
|  9 |       300 | 2017-10-16 | 10300.00 |     0.00 |  52520.00 |
| 10 |       222 | 2017-10-26 |     0.00 | 11000.00 |  41520.00 |
| 11 |       100 | 2017-10-26 | 10400.00 |     0.00 |  51920.00 |
| 12 |       300 | 2017-10-26 |     0.00 | 15000.00 |  36920.00 |
| 13 |       222 | 2017-10-27 | 10500.00 |     0.00 |  47420.00 |
| 14 |       222 | 2017-10-27 | 20000.00 |     0.00 |  67420.00 |
| 15 |       100 | 2017-11-01 |     0.00 | 30000.00 |  37420.00 |
| 16 |       222 | 2017-11-02 |     0.00 | 22000.00 |  15420.00 |
| 17 |       300 | 2017-11-12 |     0.00 | 13500.00 |   1920.00 |
| 18 |       100 | 2017-11-15 | 11000.00 |     0.00 |  12920.00 |
| 19 |       222 | 2017-11-22 | 22000.00 |     0.00 |  34920.00 |
| 20 |       300 | 2017-11-24 | 33000.00 |     0.00 |  67920.00 |
+----+-----------+------------+----------+----------+-----------+
20 rows in set (0.00 sec)


2 view for single/multiple debtor_ids

create view dtview_debtors
as
select 
  T.id id
, T.debtor_id
, T.trans_date
, T.dr
, T.cr
, ( select sum(dr - cr)  
    from dt 
    where id &lt;= T.id and debtor_id = T.debtor_id
  ) _dtor_balance_
from  dt T; 


Testing (single debtor_id):

mysql&gt; select * from dtview_debtors 
    -&gt; where debtor_id = 100 order by id;
+----+-----------+------------+----------+----------+----------+
| id | debtor_id | trans_date | dr       | cr       | _dtor_   |
+----+-----------+------------+----------+----------+----------+
|  1 |       100 | 2017-08-03 |  8100.00 |     0.00 |  8100.00 |
|  2 |       100 | 2017-08-08 | 15100.00 |     0.00 | 23200.00 |
|  6 |       100 | 2017-09-25 | 10100.00 |     0.00 | 33300.00 |
|  7 |       100 | 2017-09-25 |     0.00 | 10000.00 | 23300.00 |
| 11 |       100 | 2017-10-26 | 10400.00 |     0.00 | 33700.00 |
| 15 |       100 | 2017-11-01 |     0.00 | 30000.00 |  3700.00 |
| 18 |       100 | 2017-11-15 | 11000.00 |     0.00 | 14700.00 |
+----+-----------+------------+----------+----------+----------+
7 rows in set (0.01 sec)


Testing (multiple debtor_ids):

mysql&gt; select * from dtview_debtors 
    -&gt; where debtor_id between 100 and 300 
    -&gt; order by debtor_id, id;
+----+-----------+------------+----------+----------+-----------+
| id | debtor_id | trans_date | dr       | cr       | _dtor_    |
+----+-----------+------------+----------+----------+-----------+
|  1 |       100 | 2017-08-03 |  8100.00 |     0.00 |   8100.00 |
|  2 |       100 | 2017-08-08 | 15100.00 |     0.00 |  23200.00 |
|  6 |       100 | 2017-09-25 | 10100.00 |     0.00 |  33300.00 |
|  7 |       100 | 2017-09-25 |     0.00 | 10000.00 |  23300.00 |
| 11 |       100 | 2017-10-26 | 10400.00 |     0.00 |  33700.00 |
| 15 |       100 | 2017-11-01 |     0.00 | 30000.00 |   3700.00 |
| 18 |       100 | 2017-11-15 | 11000.00 |     0.00 |  14700.00 |
|  3 |       222 | 2017-08-11 |  1200.00 |     0.00 |   1200.00 |
|  4 |       222 | 2017-08-19 |  3300.00 |     0.00 |   4500.00 |
|  8 |       222 | 2017-10-15 | 10200.00 |     0.00 |  14700.00 |
| 10 |       222 | 2017-10-26 |     0.00 | 11000.00 |   3700.00 |
| 13 |       222 | 2017-10-27 | 10500.00 |     0.00 |  14200.00 |
| 14 |       222 | 2017-10-27 | 20000.00 |     0.00 |  34200.00 |
| 16 |       222 | 2017-11-02 |     0.00 | 22000.00 |  12200.00 |
| 19 |       222 | 2017-11-22 | 22000.00 |     0.00 |  34200.00 |
|  5 |       300 | 2017-09-06 |  4220.00 |     0.00 |   4220.00 |
|  9 |       300 | 2017-10-16 | 10300.00 |     0.00 |  14520.00 |
| 12 |       300 | 2017-10-26 |     0.00 | 15000.00 |   -480.00 |
| 17 |       300 | 2017-11-12 |     0.00 | 13500.00 | -13980.00 |
| 20 |       300 | 2017-11-24 | 33000.00 |     0.00 |  19020.00 |
+----+-----------+------------+----------+----------+-----------+
20 rows in set (0.00 sec)


See dbfiddle here.