The Secret for Writing Good SQL Queries in SAP B1

Note: Head to the bottom of this article for a special announcement about my appearing live online for ASUG.

Last time, I explained why having a program or SQL Query fail is good. This time, let's look at building it right from the beginning and one technique I find valuable to do that.

If you followed any of my LinkedIn Learning courses on programming, such as Sap Business One Reporting and Customization or SwiftUI Essential training, I've been doing this to you. I do it here in Bizoness. I'm writing the next course For iOS for LinkedIn on persistent data, and was reminded of this when I didn't do it, and everything stopped working. It is the core element of everything I Do: Play with the simple stuff.

Talking about play might not seem professional, but nothing is further from the truth in development and innovation. In his book Built to Last, Jim Collins gives a metaphor for this. Imagine you are an old pirate before radar or sonar. You are stuck in a fog with a treasure ship somewhere out there. How do you find and attack the treasure ship? 

The answer is to fire bullets out into the fog. Bullets are very cheap and fast-loading compared to your cannons. If they hit something, you've probably found your treasure ship and can invest in the more expensive, slow-loading cannonballs and take the ship. 

Instead of bullets, I frame this as play. Play is when you make small things, see if they work, and then use those small things in bigger things. Each small thing takes little time, and then you can build on that small thing or make more small things to put together. This is known as Bottom-up development I get my most creative ideas by playing, so bottom-up development goes smoothly. 

On the other hand, there is Top-Down development which I also need to consider. Top-down is the assignment, the question, or that support ticket I get from a user. I have to break a task into smaller parts to get to the point where I can code. It might seem this is the opposite of bottom-up, but they are compliments. 

I plan top Down but build bottom up.

Let's go through an example report I did recently in SAP B1. I needed the percentage of sales by customer for an inventory item over the lifetime of a sales item.

How do I do that?

The top-down approach here is simple: What tables do I need for the report? And since this is a summary of items in sales orders, that is RDR1. For customer information, I can use the ORDR table. 

First, I want to get the raw data I'll use in the report. What do I need? For RDR1, the item and its description and the quantity and line item amount depend on whether I want the units moved or the total sales. 

 SELECT 
    T0.ItemCode,
    T0.Dscription,
    T0.Quantity,
    T0.LineTotal 
 FROM RDR1 T0

Run that, and I get data. 

Then I can add a INNER JOIN to add the Customer Information. 

 SELECT 
   T1.CardCode,    
   T1.CardName,
   T0.ItemCode,
   T0.Dscription,
   T0.Quantity,
   T0.LineTotal 
FROM ORDR T1 
INNER JOIN RDR1 T0 ON T1.DocEntry = T0.DocEntry 

That also works. 

I want data for a specific item. To start, I'll use a literal value. 

 SELECT 
   T1.CardCode,    
   T1.CardName,
   T0.ItemCode,
   T0.Dscription,
   T0.Quantity,
   T0.LineTotal 
FROM 
   ORDR T1 
   INNER JOIN RDR1 T0 ON T1.DocEntry = T0.DocEntry
WHERE T0.Itemcode = 'LM4029'

I'll want aggregate values grouped by customer, so I'll do this:

 SELECT 
   T1.CardCode,    
   T1.CardName,
   T0.ItemCode,
   T0.Dscription,
   SUM(T0.Quantity),
   SUM(T0.LineTotal) 
FROM 
   ORDR T1 
   INNER JOIN RDR1 T0 ON T1.DocEntry = T0.DocEntry
WHERE T0.Itemcode = 'LM4029'
GROUP BY T1.CardCode

And get an error. In my excitement, I forgot to deal with the CardName, ItemCode, and Dscription. I'll get rid of these columns.

SELECT 
   T1.CardCode,    
   SUM(T0.Quantity),
   SUM(T0.LineTotal) 
FROM 
   ORDR T1 
   INNER JOIN RDR1 T0 ON T1.DocEntry = T0.DocEntry
WHERE T0.Itemcode = 'LM4029'
GROUP BY T1.CardCode

And that gives me the numbers I want. Now I run into a problem: I want a percentage of total sales. Total sales is the sales by a company divided by the total of all company sales. 

The problem is GROUP BY. I have to use GROUP BY to get a sum by company, but it prohibits me from getting a grand total. Instead of messing with what I got, I save it and then work on a new, simpler case. This is what I mean by playing. I'll start with just Quantity and CardCode and repeat some of what I learned. 

SELECT 
   T0.CardCode,
   Sum(T1.Quantity)
FROM 
   ORDR T0
   INNER JOIN RDR1 T1 ON T0.DocEntry = T1.DocEntry
WHERE T1.ItemCode ='LM4029'
GROUP BY CardCode

I need two tables, one with the sum of quantity by customer and one with the grand total for quantity. Then, I can join those tables together to get my results.

A WITH clause will help make those two tables. When making these tables, I add keys and columns for WHERE Clauses I'll be using. My current table will use a CardCode and an ItemCode. I'll group first by ItemCode so I can search for those, and then CardCode since that's what I want to total. 

WITH Totals AS(
   SELECT 
      T0.CardCode,
      T1.ItemCode,
      SUM(T1.Quantity) AS TQuantity
   FROM 
      ORDR T0
      INNER JOIN RDR1 T1 ON T0.DocEntry = T1.DocEntry
   GROUP BY T1.ItemCode, T0.CardCode
)

SELECT 
   *
FROM Totals T0
WHERE T0.ItemCode ='LM4029'

I'll test this, see if it works, and then add the grand total table. Here, I'll need an ItemCode as a key and grouping and my Summary of quantity, so I'll add this to the WITH

GrandTotal AS(
   SELECT
     T1.ItemCode AS ItemCode,
     SUM(T1.Quantity) AS GTQuantity
   FROM RDR1 T1
GROUP BY T1.ItemCode
) 

I'll INNER JOIN this to my table and list out my columns:

WITH Totals AS(
SELECT 
   T0.CardCode,
   T1.ItemCode,
   Sum(T1.Quantity) AS TQuantity
FROM 
   ORDR T0
   INNER JOIN RDR1 T1 ON T0.DocEntry = T1.DocEntry
GROUP BY T1.ItemCode, T0.CardCode
),

GrandTotal AS(
SELECT
  T1.itemcode AS ItemCode,
  SUM(T1.Quantity) AS GTQuantity
FROM RDR1 T1
GROUP BY ItemCode
) 

SELECT 
   T0.Cardcode, 
   T0.TQuantity,
   T1.GTQuantity
FROM 
   Totals T0
   INNER JOIN GrandTotal T1 ON T0.ItemCode = T1.ItemCode
WHERE T0.itemcode ='LM4029'

I'll run this:

Then, Check the values using Excel to find the number sum correctly.

Now I know this works on its own. I have two play queries here, and since the second one is already more complex than the first, I'll add the simpler one to the more complex one, Adding LineTotal to the query.

WITH Totals AS(
SELECT 
   T0.CardCode,
   T1.ItemCode,
   SUM(T1.Quantity) AS TQuantity,
   SUM(T1.LineTotal) AS TLineTotal
FROM 
   ORDR T0
   INNER JOIN RDR1 T1 ON T0.Docentry = T1.Docentry
GROUP BY T1.ItemCode, T0.CardCode
),

GrandTotal AS(
SELECT
  T1.ItemCode AS ItemCode,
  SUM(T1.Quantity) AS GTQuantity,
  SUM(T1.LineTotal) AS GTLineTotal
From RDR1 T1
GROUP BY ItemCode
) 


SELECT 
   T0.CardCode, 
   T0.TQuantity,
   T1.GTQuantity,
   T0.TLineTotal,
   T1.GTLineTotal
FROM 
   Totals T0
   INNER JOIN GrandTotal T1 ON T0.ItemCode = T1.ItemCode
WHERE T0.itemcode ='LM4029'

That works. So I can save that.

I need CardCode, CardName, and my percentages. So I'll change the SELECT to 

SELECT 
   T0.Cardcode, 
   (SELECT T2.CardName From OCRD T2 WHERE T2.CardCode = T0.Cardcode) AS "BP Name",
   T0.TQuantity,
   100 * T0.TQuantity / T1.GTQuantity AS "% Units Sold",
   T0.TLineTotal,
   100 * T0.TLineTotal / T1.GTLineTotal AS "% Sales"
FROM 
   Totals T0
   INNER JOIN GrandTotal T1 ON T0.ItemCode = T1.ItemCode
WHERE T0.itemcode =[%0]

I used a subquery for the name since I only needed it once. I also changed the WHERE clause to use a parameter. This is the sales numbers for products over the life of OEC computers. 

Running this, I get my report after entering the ItemCode. 

This example gives you the basic idea of what I do when I code: a Query for SQL or a SwiftUI Application for an iPhone. As I write, I'll make many small programs to ensure the feature works, then add them to the application or query. That makes debugging and trying things much easier without worrying about breaking the project I'm working on.

Special Note:

I'm scheduled to do this session live and online on August 28 at 3 p.m. CT :

ASUG Small and Medium Enterprise Community Alliance Presents: Barcodes and Other Practical Learning Resources for Business One

Forty-nine years ago, a pack of chewing gum changed the IT universe: it was the first item scanned for a bar code. In this session, we'll learn where we've come in this half-century of barcodes and where the future lies. We'll then learn how and why to use barcodes in SAP Business One.

This event is open to non-members of ASUG. For more information and registration Check out the event page