Sage 300 Queries: Difference between revisions
WikiSysopdi (talk | contribs) No edit summary |
WikiSysopdi (talk | contribs) No edit summary |
||
| Line 22: | Line 22: | ||
select ORDUNIQ, ORDNUMBER,CUSTOMER, BILNAME,BILADDR1,BILCITY,BILSTATE,BILZIP,BILCOUNTRY,BILPHONE,BILCONTACT,SHIPTO,SHPNAME,SHPADDR1,SHPADDR2,SHPCITY,SHPSTATE,SHPZIP,SHPCOUNTRY,SHPPHONE,TERMTTLDUE, REFERENCE, ORDDATE,EXPDATE,ORDFISCYR, INVSUBTOT,INVNET,INVETAXTOT,INVNETWTX,INVAMTDUE from OEORDH with (index(KEY_1)) where CUSTOMER='1200' | select ORDUNIQ, ORDNUMBER,CUSTOMER, BILNAME,BILADDR1,BILCITY,BILSTATE,BILZIP,BILCOUNTRY,BILPHONE,BILCONTACT,SHIPTO,SHPNAME,SHPADDR1,SHPADDR2,SHPCITY,SHPSTATE,SHPZIP,SHPCOUNTRY,SHPPHONE,TERMTTLDUE, REFERENCE, ORDDATE,EXPDATE,ORDFISCYR, INVSUBTOT,INVNET,INVETAXTOT,INVNETWTX,INVAMTDUE from OEORDH with (index(KEY_1)) where CUSTOMER='1200' | ||
--get all customer orders itemsselect ORDUNIQ,LINENUM, LINETYPE,ITEM,[DESC],QTYORDERED,QTYSHIPPED, QTYBACKORD, ORDUNIT,UNITPRICE, TAMOUNT1,TAMOUNT2,TRATE1,TRATE2 from OEORDD where ORDUNIQ in ( select ORDUNIQ from OEORDH with (index(KEY_1)) where CUSTOMER='1200' ) AND ((LINENUM >= 0)) order by ORDUNIQ,LINENUM option(fast 10) | |||
from | |||
where | |||
from | |||
where CUSTOMER='1200' ) AND (( | |||
order by | |||
Revision as of 14:47, 29 August 2022
Contained here are some recommended queries for Sage 300 to get only the data needed for Owners and customers.
Owners:
Single Customer queries:
CUSTOMER DETAILS
select AUDTORG,TEXTSNAM, NAMECUST as 'Customer', TEXTSTRE1 as 'Address 1',TEXTSTRE2,TEXTSTRE3,TEXTSTRE4,NAMECITY as 'City',CODESTTE,CODEPSTL,CODECTRY,NAMECTAC,TEXTPHON1,TEXTPHON2,CODETERR,IDACCTSET,AMTCRLIMT AS 'Credit Limit',AMTBALDUET,DAYSTOPAY,EMAIL1 from ARCUS where IDCUST='1200'
CUSTOMER ORDERS
--get all customer orders
select ORDUNIQ, ORDNUMBER,CUSTOMER, BILNAME,BILADDR1,BILCITY,BILSTATE,BILZIP,BILCOUNTRY,BILPHONE,BILCONTACT,SHIPTO,SHPNAME,SHPADDR1,SHPADDR2,SHPCITY,SHPSTATE,SHPZIP,SHPCOUNTRY,SHPPHONE,TERMTTLDUE, REFERENCE, ORDDATE,EXPDATE,ORDFISCYR, INVSUBTOT,INVNET,INVETAXTOT,INVNETWTX,INVAMTDUE from OEORDH with (index(KEY_1)) where CUSTOMER='1200'
--get all customer orders itemsselect ORDUNIQ,LINENUM, LINETYPE,ITEM,[DESC],QTYORDERED,QTYSHIPPED, QTYBACKORD, ORDUNIT,UNITPRICE, TAMOUNT1,TAMOUNT2,TRATE1,TRATE2 from OEORDD where ORDUNIQ in ( select ORDUNIQ from OEORDH with (index(KEY_1)) where CUSTOMER='1200' ) AND ((LINENUM >= 0)) order by ORDUNIQ,LINENUM option(fast 10)
