SQL Query Training for SAP Business One

Where to start building a query, how I design queries and how to scope a query.

How to find data in SAP Business One.  Using "System Information" to identify tables and columns and using Query Generator to track down table joins.  I also describe my process for planning and show a simple example of a query map.

Learn the tools I use and the process I follow to build queries quickly and effectively. Staying organized and using the best tools will increase development speed dramatically.

Get started with step-by-step instructions on downloading and setting up your tools.

Learn some powerful introductory tips and tricks to accelerate your learning of SQL.

Learn the SELECT, FROM, WHERE and ORDER clause format and how to use each section.  How to do a basic query format and my pro-tips for making an easy to read query.

More detail on the FROM clause.  Learn how to join different tables together and how the structure of your query will affect your output.

More detail on the WHERE clause.  Learn how to filter your query and how to use SQL operators (AND, OR, LIKE, etc.).

Filtering dates is a common request for SQL queries. This lecture goes deeper into the techniques for handing a WHERE clause with date functions.

SAP Business One uses a specific format for adding parameters.  Learn my pro tips for interacting with these parameters and my trick for pulling dates properly.

Save queries into the Query Manager.  Learn how to set authorizations and how to filter and export your data.

Every loves to manually manipulate cells in a spreadsheet.  This lecture covers various methods to do this in SAP Business One.

If you enjoy using this course, please consider leaving a review... (Read More)

Learn about how to structure alert queries.  **WARNING** do not put SAP parameters into alerts, it will break your alert system.  You must use a query that can be written with no human input.  See my lecture for more.

Add custom coding for most fields in SAP Business One.  Allows you to perform calculations to output various dollar amounts, strings or other pieces of information dynamically on-screen.  See how it's done!

I included this lecture to further expand on the formatted search topic.  FMS queries are fairly complex and if you would like more resources please send me a message on LinkedIn (http://linkedin.battleshipcobra.com/).  I have even more topics on my YouTube Channel: http://youtube.battleshipcobra.com/

IF statements are not used inline for normal SELECT statements.  CASE is how you would evaluate different conditions.  It's very useful.

Use SUM, MAX, COUNT and learn how to group queries to get totals.

Want to learn more about aggregate functions?  This short article explains a little more on the topic.

Be a more efficient "Script Kitty" by taking queries from the SAP Business One forum.  Learn how to understand them for yourself, adapt them and make them work for your purposes.  Tips and tricks for getting started learning from more advanced queries.

The COALESCE() function will save your day.  Coalesce will avoid many mistakes and headaches as you learn to build queries and is used in almost every one of my queries.

Step-by-step activity to practice the material from the intermediate lectures.

Learn how to avoid messy joins by using subqueries.  And learn how to build your own tables with a derived table structure.  Two very useful functions with regards to building SAP Business One queries.

How to create and deploy an SQL view.  How to use UNION ALL to make stacks of queries.

Declare and use variables to simplify your query structure.

The king of complex derived and custom functions.  This technique will give you the ultimate in query building excitement.  Have your cake and eat it too.

Learn how to deploy SQL views and StorProcs (Stored Procedures) with your Crystal Reports.  This powerful technique works around some table linking limitations and gives you maximum control over the results pulled and presented in your reports.

Why you should use B1UP to create all of your queries.  It solves all the common SAP Business One out-of-the-box limitations and provides convenient and logical enhancements for all SQL query building.

The logic between SQL and HANA are very similar, some of the syntax changes slightly.  This lecture contains resources on the difference between SQL and HANA syntax including 130 SQL --> HANA converted query examples.

A list of course resources and examples.

This one is a great general purpose query.  It allows the user to get basically any typical piece of BP information out of the system.  Contains Customers and Leads.

Everyone occasionally needs a raw list of their items.  I include the most important fields, you could always add to it but it should give you 90% of the likely needed columns including prices for up to two price lists (just duplicate the subqueries for more price lists, includes a price list code finder query).

A query to pull invoice and credit note information for a specific date range.  Works within SAP using the date parameter hack.

Simulates the inventory audit report functionality.  Perfect to match your audit report or add more fields. Great for understanding back dating of inventory principles.  If you need to run stock for last week, last month or last year this query is a good start.

Pull stock and availability information for items currently in your warehouse.