1

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

1 Answer 1

1

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

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

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.