using variable in SQL query

Question

I am using MySql database. I have made following query and run at prompt, its running correctly.

SELECT 
    tr_date,
    tr_invno,
    particulars,
    received,
    issued, 
       @running_total:=@running_total + t.balance AS running_balance
FROM
(SELECT 
    srno,
    tr_date,
    tr_invno,
    tr_type,
    tr_accounthead as particulars,
    case when tr_type=1 then tr_qty else 0 end received,
    case when tr_type=2 then tr_qty else 0 end issued, 
    case when tr_type=1 then tr_qty else -(tr_qty) end balance 
    FROM tblinvtransaction where tr_date between '2021-09-01' and '2021-09-09' and tr_itemcode = '01') t
JOIN (SELECT @running_total:=(select sum(case when tr_type='1' then tr_qty else -(tr_qty) end) from tblinvtransaction where tr_date<'2021-09-01' and tr_itemcode = '01')
) r
ORDER BY t.tr_date, t.tr_type

but when i use it in C# its giving error "Fatal error encountered during command execution"

        string query = @"SELECT tr_date, tr_invno, particulars, received, issued, @running_total:= @running_total + t.balance AS running_balance 
            FROM(SELECT srno, tr_date, tr_invno, tr_type, tr_accounthead as particulars,
                case when tr_type = 1 then tr_qty else 0 end received,
                case when tr_type = 2 then tr_qty else 0 end issued,
                case when tr_type = 1 then tr_qty else -(tr_qty)end balance
                FROM tblinvtransaction where tr_date between '2021-09-01' and '2021-09-09' and tr_itemcode = '01') t
            JOIN(SELECT @running_total:= (select sum(case when tr_type = '1' then tr_qty else -(tr_qty)end) from tblinvtransaction where tr_date < '2021-09-01' and tr_itemcode = '01')
            ) r
            ORDER BY t.tr_date, t.tr_type)";
        string MySql = string.Concat(query);
        MySqlDataAdapter da = new MySqlDataAdapter(MySql, connection);
        DS_ItemLedger ds = new DS_ItemLedger();
        da.Fill(ds, "DT_ItemLedger");

Please guide.

It might work but it's not officially supported to use variables like this. If your MySQL is modern, like v8+ there are proper ways of doing a running total. Or you could just do it in c#.. — Caius Jard
– Caius Jard, Commented Sep 23, 2021 at 10:56
I am using v8+, please guide proper ways of doing a running total. — Muhammad Saleem
– Muhammad Saleem, Commented Sep 23, 2021 at 12:17
What is the full exception message and call stack? idownvotedbecau.se/noexceptiondetails — Bradley Grainger
– Bradley Grainger, Commented Sep 23, 2021 at 12:54

Bradley Grainger · Accepted Answer · 2021-09-23 12:56:01Z

1

MySqlCommand typically doesn't allow user variables in SQL statements by default. To enable this, you have to add Allow User Variables = true; to your connection string. See https://mysqlconnector.net/connection-options/#AllowUserVariables for more details.

It should work with MySql.Data, but you may have to switch from MySql.Data to MySqlConnector to fully get this working properly.

answered Sep 23, 2021 at 12:56

Bradley Grainger

28.3k4 gold badges95 silver badges117 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

pedram rankchian · Accepted Answer · 2021-09-23 11:00:18Z

0

you can't declare variable in raw query so, you should create a stored procedure, and call it in your code

answered Sep 23, 2021 at 11:00

pedram rankchian

395 bronze badges

Comments

MestreDosMagros · Accepted Answer · 2021-09-23 14:01:51Z

0

You can do this?

SELECT 
    tr_date,
    tr_invno,
    particulars,
    received,
    issued, 
    running_total + t.balance AS running_balance
FROM
(SELECT 
    srno,
    tr_date,
    tr_invno,
    tr_type,
    tr_accounthead as particulars,
    case when tr_type=1 then tr_qty else 0 end received,
    case when tr_type=2 then tr_qty else 0 end issued, 
    case when tr_type=1 then tr_qty else -(tr_qty) end balance,
    (select sum(case when tr_type='1' then tr_qty else -(tr_qty) end) from tblinvtransaction where tr_date<'2021-09-01' and tr_itemcode = '01') as running_total
    FROM tblinvtransaction where tr_date between '2021-09-01' and '2021-09-09' and tr_itemcode = '01') t
ORDER BY t.tr_date, t.tr_type

edited Sep 23, 2021 at 14:01

answered Sep 23, 2021 at 11:54

MestreDosMagros

1,0288 silver badges19 bronze badges

3 Comments

Muhammad Saleem Over a year ago

unknown column running_total error occur.

MestreDosMagros Over a year ago

dont do a join, call the (select sum(case when tr_type='1' then tr_qty else -(tr_qty) end) from tblinvtransaction where tr_date<'2021-09-01' and tr_itemcode = '01') as running_total on your second query directly, i edited the answear

Muhammad Saleem Over a year ago

Now error has been removed but running total calculation goes wrong. Its not adding previous line total. Just add second query result in all rows.

Kenly · Accepted Answer · 2022-09-22 14:41:32Z

0

I have found an alternate solutions as;

Solution 1.

SELECT 
    srno,
    tr_date,
    tr_invno,
    tr_type,
    tr_accounthead as particulars,
    case when tr_type=1 then tr_qty else 0 end received,
    case when tr_type=2 then tr_qty else 0 end issued, 
    **sum(case when tr_type=1 then tr_qty else -(tr_qty) end) over (order by tr_date, srno rows unbounded preceding)+4 as running_total**
    FROM tblinvtransaction where tr_date between '2021-09-01' and '2021-09-09' and tr_itemcode = '02'
ORDER BY tr_date, tr_type

Solution 2.

SELECT 
    srno,
    tr_date,
    tr_invno,
    tr_type,
    tr_accounthead as particulars,
    case when tr_type=1 then tr_qty else 0 end received,
    case when tr_type=2 then tr_qty else 0 end issued
    ,**(
    SELECT SUM(case when tr_type=1 then tr_qty else -(tr_qty) end)
        FROM tblinvtransaction AS A
        WHERE A.srno <= B.srno and  A.tr_itemcode ='01'
    ) AS CumulativeSum**
FROM tblinvtransaction AS B
WHERE B.tr_itemcode ='01'
ORDER BY B.srno

edited Sep 22, 2022 at 14:41

Kenly

27k7 gold badges50 silver badges67 bronze badges

answered Sep 24, 2021 at 12:03

Muhammad Saleem

1078 bronze badges

1 Comment

Community Over a year ago

As it’s currently written, your answer is unclear. Please edit to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers in the help center.

Collectives™ on Stack Overflow

using variable in SQL query

4 Answers 4

Comments

Comments

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related