Thursday, March 02, 2006

Select Statements Part I

A favorite sql script of mine is the 'Select Count(*)' statement. You can use it to get the number of items, customers or vendors in Great Plains by simply running:

Select Count(*) from IV00101 (item master)
Select Count(*) from RM00101 (customer master)
Select Count(*) from PM00200 (vendor master)

You can also use it to check documents in sales or purchasing and include a where clause.

Select Count(*) from SOP10100
where soptype = '2' (to check count for open sales orders)

Select Count(*) from SOP30200
where soptype = '3' and
sopnumbe LIKE 'ORD%' (to check for posted sales invoices)