Here are a few basic SQL queries to be used in SAP Business One. You can copy and paste the queries into the Query window of Business One. Simply copy between from “–begin…” to “…–end” and paste into your Query window.
SQL for Open Sales Orders
–begin
select t0.docnum, t0.cardcode, t0.cardname, t0.sysdoctotal
from ordr t0
where t0.docstatus = ‘O’
–end
By Date
–begin
select t0.docnum, t0.cardcode, t0.cardname, t0.sysdoctotal
from ordr t0
where t0,docstatus = ‘O’
and t0.docdate between [%0] and [%1]
order by t0.docdate
–end
SQL for Open Deliveries
–begin
select t0.docnum, t0.cardcode, t0.cardname, t0.sysdoctotal
from odln t0
where t0.docstatus = ‘O’
–end
SQL for Open Invoices
–begin
select t0.docnum, t0.cardcode, t0.cardname, t0.sysdoctotal
from oinv t0
where t0.docstatus = ‘O’
–end
