CODEWITHSUNDEEP
c#mysql
AFZAL KHAN
AFZAL KHAN

Reputation: 33

Using query with variables in c# and mysql

I need a following set of statements executed , currently it gives Fatal Error I need the query output of following query which uses two variables

Database is MySQL and language is C# I am trying to use this in c# code using MyReader=new MySQLCommand(this query ,connection object).ExecuteReader()

SET @lastItem := 0, @lastValue := 0; 
SELECT CONCAT (
  DATE (t5.InventoryDate)
  ,t5.SKUorItem
  ) AS PK,
    t5.CustomerID, t5.entityID, t5.inventoryDate, t5.SKUorItem, t5.Category, t5.inventory FROM
(
 SELECT 
 '3' as CustomerID,
 '90' as entityID,
 t1.InventoryDate as inventoryDate, 
 t1.idItem as SKUorItem, 
 t4.categoryInventary as Category,
 ifnull(t1.itemQty,0) as sales, ifnull(t2.buyQty,0) as StockMove,
 @lastValue := if( @lastItem = t1.idItem, @lastValue + ifnull(t2.buyQty,0) - ifnull(t1.itemQty,0), ifnull(t2.buyQty,0) - ifnull(t1.itemQty,0) ) as inventory,
 @lastItem := t1.idItem
 FROM
 (
 select date(date) as inventorydate, idItem, sum(quantity) as itemqty 
 from subway.saleitem 
 group by idItem, date(date)
 ) as t1
 LEFT OUTER JOIN
 (
 select date(date) as InventoryDate, idItem, Sum(initialBuyQuantity) as buyqty
 FROM Subway.InvoiceStock
 GROUP BY idItem, date(date)
 ) as t2
 ON t1.InventoryDate = t2.InventoryDate and t1.idItem = t2.idItem
 INNER JOIN subway.Item as t3 on t1.iditem = t3.iditem
 LEFT JOIN subway.CategoryInventary as t4 on t3.idCategoryInventary = t4.idCategoryInventary
 order by t1.iditem, t1.InventoryDate
) t5

Upvotes: 1

Views: 82

Answers (1)

Romil Kumar Jain
Romil Kumar Jain

Reputation: 20745

Wrap these queries into an Stored Procedure or UDF. Simply call that from c# code.

Upvotes: 1

Related Questions