4 Replies - 227 Views - Last Post: 11 March 2019 - 09:02 AM

#1 BassemK   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 31
  • Joined: 06-March 19

What is the problem in view code(mysql)

Posted 10 March 2019 - 05:06 AM

hi, i use this code to create Mysql View that sum the items stock ..

CREATE OR REPLACE VIEW `view_items_defin` as
                                                          SELECT items.stitems_ID,
                                                          items.stitems_Status,
                                                          items.stitems_Name,
                                                          items.stitems_Pharma_ActiveIngredient,
                                                          items.stitems_Code,
                                                          manuf.manu_Name,
                                                          COALESCE(ca.Amount, 0) + COALESCE(pa.Amount,0) AS Stock
                                                   FROM st_items items
                                                   LEFT JOIN (SELECT sns.stitems_ID,
                                                                     SUM(sns.StockQnty) AS Amount
                                                              FROM stock_noserials sns
                                                              GROUP BY sns.stitems_ID) ca
                                                   ON ca.stitems_ID = items.stitems_ID
                                                   LEFT JOIN (SELECT pis.stitems_ID,
                                                                     COUNT(*) AS Amount
                                                              FROM purchases_item_seriels pis
                                                              WHERE pis.pis_Statues IN (0, 5, 6)
                                                              GROUP BY pis.stitems_ID) pa
                                                   ON pa.stitems_ID = items.stitems_ID
                                                   
                                                   INNER JOIN `st_plug_manufacturer` AS manuf 
                                                   ON items.`stitems_Manufacturer` = manuf.`manu_ID`;



this are tables data :
1- stock_noserials table

---------------------------------
id | stitems_ID     | StockQnty | ---
1  | 15078          | -6        | ---
2  | 15078          | 0         | ---
3  | 15078          | 0         | ---
---------------------------------



and there is no data in purchases_item_seriels table

but the result of the view code :Stock = -2 not -6

What I have tried:

i tried to follow many items but the same problem ..

Is This A Good Question/Topic? 0
  • +

Replies To: What is the problem in view code(mysql)

#2 Skydiver   User is offline

  • Code herder
  • member icon

Reputation: 6914
  • View blog
  • Posts: 23,508
  • Joined: 05-May 12

Re: What is the problem in view code(mysql)

Posted 10 March 2019 - 06:28 AM

Looks like a SQL question, not C# question. Moving...
Was This Post Helpful? 0
  • +
  • -

#3 BassemK   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 31
  • Joined: 06-March 19

Re: What is the problem in view code(mysql)

Posted 10 March 2019 - 06:32 AM

yes i'm sorry ..
Was This Post Helpful? 0
  • +
  • -

#4 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 15063
  • View blog
  • Posts: 60,150
  • Joined: 12-June 08

Re: What is the problem in view code(mysql)

Posted 10 March 2019 - 08:07 AM

First- how is it that you have negative stock?

What are the values in 'purchases_item_seriels ?
Was This Post Helpful? 0
  • +
  • -

#5 rgfirefly24   User is offline

  • D.I.C Lover
  • member icon


Reputation: 466
  • View blog
  • Posts: 2,208
  • Joined: 07-April 08

Re: What is the problem in view code(mysql)

Posted 11 March 2019 - 09:02 AM

I would put money on this being your problem even though you say there are no records:
SELECT pis.stitems_ID,
COUNT(*) AS Amount
FROM purchases_item_seriels pis
WHERE pis.pis_Statues IN (0, 5, 6)
GROUP BY pis.stitems_ID



I would also say when debugging sql, take each query individually and see what their results are. So because you're using sub querys as your tables, run each sub query on it's own and see if it gives you the results you expect it to. Then start bringing everything back together.

Also, remove the SUM and just pull all the records that go into the SUMs in the sub queries. That way you'll see the exact records behind the aggregates.

This post has been edited by rgfirefly24: 11 March 2019 - 09:03 AM

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1