Category: SAP Business One SQL

SAP Business One useful SQL Reports. If you are looking for hints and tips on how to write SQL reports for SAP B1 you have found the right site.

If you are interested in writing your own SQL queries for SAP Business One the following will assist you. Basics of SQL Writing for SAP Business One SQL query and related terms Before going into the details of SQL query, I would like to briefly introduce some basic database concepts because SQL is a database language for managing data in Relational Database Management Systems(RDBMS). RDBMS RDBMS is a Database Management System that is based on the relation model. Relational here is a key word for RDBMS. You will find that data is stored in the form of Tables and the relationship among the data is also stored in the form of tables for RDBMS. Table Table is a key component within a database.…

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…