using variable in SQL query-CodePudding

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.

CodePudding user response：

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

CodePudding user response：

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,
    running_total  
    FROM tblinvtransaction where tr_date between '2021-09-01' and '2021-09-09' and tr_itemcode = '01') t
    JOIN (
        SELECT (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 dual
) r
ORDER BY t.tr_date, t.tr_type