• alvin
  • Senior Member
  • Offline
  • From: USA, Guatemala, Honduras
  • Registered: 05/20/2008
  • Posts: 301

Topic: help with a SQL statement

I would like some help with a SQL statement.

I know that the PO system is being worked on and will get better with time, but if I could just do a query on the database for now it would be a big help.

I would like to display columns 0_item_codes.stock_id, 0_item_codes.description,  and maybe more later.

Where
0_loc_stock.loc_code =X and
0_purch_data.supplier_id =X and
0_loc_stock.reorder_level is => 0_stock_moves.qty

Something like that...

If anyone out there can help with this or has put a query together for PO's it would be a great help?

Thanks

"The roots of education are bitter, but the fruit is sweet."  - Aristotle.
  • From: USA
  • Registered: 06/30/2009
  • Posts: 39

Re: help with a SQL statement

Hi Alvin,

  I'm a little rusty on my joins and I don't particularly use the Items and Inventory module, but is this something like what you're looking for?  (I'm assuming those Xs are actually referring to different values, that is, X and Y.)

select distinct codes.stock_id, codes.description
  from 0_item_codes as codes
       left join 0_loc_stock as stock on (codes.stock_id = stock.stock_id)
       left join 0_purch_data as purch on (stock.stock_id = purch.stock_id)
       left join 0_stock_moves as moves on (purch.stock_id = moves.stock_id)
where stock.loc_code LIKE 'X' and
       purch.supplier_id = Y and
       stock.reorder_level >= moves.qty;

~Tom

  • alvin
  • Senior Member
  • Offline
  • From: USA, Guatemala, Honduras
  • Registered: 05/20/2008
  • Posts: 301

Re: help with a SQL statement

Hello Tom, Thanks for your help!!

I need to add, where stock.reorder_level >= moves.qty; (location,qty) I need to sort be Location Qty, not all location's qty's,  did I say that right?

Thanks for all the help from the SQL Dept.

Alvin

"The roots of education are bitter, but the fruit is sweet."  - Aristotle.
  • From: USA
  • Registered: 06/30/2009
  • Posts: 39

Re: help with a SQL statement

Hi Alvin,

  I'm not totally sure I follow.  Could you clarify your needs?

Tom

5 Reply by alvin (edited by alvin 12/05/2009 02:29:34 am)

  • alvin
  • Senior Member
  • Offline
  • From: USA, Guatemala, Honduras
  • Registered: 05/20/2008
  • Posts: 301

Re: help with a SQL statement

Ok forget what I just said about location.. I just not getting result I need. I need "codes.stock_id, codes.description, stock.reorder_qty" and more later.

Where "stock.reorder_level < (Re-Order Level)" for Location X.

Tom, I guess it is a date issue, what I need to add is Quantity On Hand, for today, stock.reorder_level < moves.qty; needs to be for todays date, for that stock_id.  Something like the function get_qoh_on_date.

Sorry I don't know much about this...  what do ya think?

AM

"The roots of education are bitter, but the fruit is sweet."  - Aristotle.