SAP Business One – Using CASE in SAP B1 SQL – Top Ten CASEs
Am currently working with an individual, hoping to help her improve her SQL skills. She has a great start, but we are going to get her up to the next level, and one way is going to be the usage of CASE in her new SQL.
USING CASE IN SAP B1 CAN HELP EVERYONE
The CASE function in SQL is a great way to customize the output of your SQL to better suit the needs of your End Users. Instead of having an “O” under a DocStatus column, you can display the word of “Open”. The concept can not only provide better descriptions, it can provide accurate values, handle exceptions correctly, can perform the correct calculation, and can also act as a filter when End Users (or yourself) are “slicing and dicing” the SQL results.
What a CASE does is group together statements of “IF something is this THEN do this” (display a name, term, perform a calculation, display a value, etc.). You must tell SQL first that you are about to do the IF/THEN by using the word “CASE”. And you use END to let the SQL know you are finished with your IF/THEN lines.
The CASEs in this blog are some commonly requested types of CASE Statements in the Forum. Many of them are interchangeable with various tables. All Marketing (Sales – A/R and Purchasing – AP) use the same data fields names such as DocStatus, so most of the below CASEs can be used as templates. I have a Word Document, about 8 pages long, with various CASEs used while writing SQL. Very handy when writing any kind of report for the End Users – just copy it in and onwards you go.
Just a reminder that this blog is to introduce a concept. You might need to change some of the CASEs given below, or you might want to modify the SQL to your own preferences. You can use CAST and CONVERT and ISNULL and many other SQL parts in the CASES for Numeric Value below – I am keeping it simple right now, and am presenting simple CASEs. Some CASEs you write will need to check three or four data fields to come up with the correct displayed value (and it is important how you write that type of CASE in the way it is ordered) – doing three and four data fields is too much to do in this blog as it can get very complicated, depending upon what you are doing.
If you have any CASE you think would be helpful to others as “templates”, please do share with everyone and post it below as a Comment. I have dozens of these in a Word Document so I can just copy them into SQL while writing, and that saves a lot of time!!!
PROVIDING A WAY TO IDENTIFY MISSING INFORMATION/CODES.
Itis a good idea to include the fields in the CASE when drafting the SQL – helps to identify how things are being handled. Comment or remove those fields when you go to finalize the SQL (see first line bolded below). However, I believe one technique you should always use while doing CASE is what I call the “escape” or “research” portion of the CASE (see second bolded below). A long, long time ago I learned this lesson – in a new version (2007!) of SAP B1, SAP introduced a new code on a table. Some SQL I had written covering Financials was not working correctly, and it took me a long time to find out what was wrong. This little “research’ trick will help you out on that situation.
Say, for instance, you must write some SQL to display what the Document Name is on the Inventory History table (OINM). Without the CASE, you would get numbers (the TransType), and the End User would have to use a cross-reference list to find out what the number means. Instead, you could list the number AND create a CASE to give a good description of what that number means. I generally start out like this, always including the research line in the SQL below:
–Do not run this – it will not work (just part of a CASE coming later)
WHEN T0.TransType = 13 THEN ‘AR Invoices’
WHEN T0.TransType = 14 THEN ‘AR Credit Memo’
WHEN T0.TransType = 15 THEN ‘AR Deliveries’
ELSE ‘ ***RESEARCH*** ‘
END AS ‘Details’,
By running and re-running the SQL, this ELSE will let you very easily find what TransTypes descriptions you are missing while doing the SQL on the Inventory History. Just use the “Filter” function and reduce your SQL to those lines having the phrase ***RESEARCH*** in the column of “Details”. Then go out and find what that TransType is. Or just double click on the column name of “Details” to sort. Instead of the word ***RESEARCH***, some folks like to use a blank field. Your choice as it is just a personal preference, but you will see the escape or research line throughout all of the CASEs below.
So, a countdown on the examples:
NUMBER 10 – The Business Partner Type on OCRD.
--NUMBER 10 SELECT T0.CardType, CASE WHEN T0.CardType = 'C' THEN 'Customer' WHEN T0.CardType = 'L' THEN 'Lead' WHEN T0.CardType = 'S' THEN 'Vendor' ELSE ' ***RESEARCH*** ' END AS 'BP Type' FROM OCRD T0
NUMBER 9 – The Document Type on ORDR (but this can be used on any Document table).
--NUMBER 9 SELECT T0.DocType, CASE WHEN T0.DocType = 'I' THEN 'Item' WHEN T0.DocType = 'S' THEN 'Service' ELSE ' ***RESEARCH*** ' END AS 'Doc Type' FROM ORDR T0
NUMBER 8 – The Address Type on CRD1 (depending upon how you have your Addresses set up, you might have to use the ***RESEARCH*** to find out what AdresType code you are missing). Just to let you know – I wrote it this way intentionally to give you a chance to play a bit – you probably know which type I left out. &;-D
--NUMBER 8 SELECT T0.AdresType, CASE WHEN T0.AdresType = 'S' THEN 'Ship To' WHEN T0.AdresType = 'B' THEN 'Bill To' ELSE ' ***RESEARCH*** ' END AS 'Address Type' FROM CRD1 T0
NUMBER 7 – The Details on OINM Table.
[SPECIAL NOTE: This is NOT all of the TransTypes you can find on the OINM table. Depending upon how you are using SAP B1, the list can get rather lengthy):
--NUMBER 7 SELECT T0.TransType, CASE WHEN T0.TransType = 13 THEN 'A/R Invoices' WHEN T0.TransType = 14 THEN 'A/R Credit Memos' WHEN T0.TransType = 15 THEN 'Deliveries' WHEN T0.TransType = 16 THEN 'Returns' WHEN T0.TransType = 18 THEN 'A/P Invoices' WHEN T0.TransType = 19 THEN 'A/P Credit Memos' WHEN T0.TransType = 20 THEN 'Goods Receipt PO' WHEN T0.TransType = 21 THEN 'A/P Returns' WHEN T0.TransType = 59 THEN 'Goods Receipt' WHEN T0.TransType = 60 THEN 'Goods Issue' WHEN T0.TransType = 67 THEN 'Inventory Transfers' WHEN T0.TransType = 69 THEN 'Landed Costs' WHEN T0.TransType = 162 THEN 'Inventory Revaluations' WHEN T0.TransType = 202 THEN 'Production Order' WHEN T0.TransType = 10000071 THEN 'Inventory Posting' ELSE ' ***RESEARCH*** ' END AS 'OINM Details' FROM OINM T0
NUMBER 6 – User-Defined Fields can also be used in the CASE portion of the SQL. This seems to be a fairly common occurrence. Someone sets up a UDF and uses only numeric or alphanumeric codes in both fields on the UDF Set Up. After the person leaves, personnel are not really certain what the codes mean, so you need to get the definitions down pretty quick. PS – you can change the description of the UDF, and there is another way to do this with (SELECT DESC FROM UDF1…) but I want to keep it simple – you can play with the UDF1 table after you get some of these down.
--NUMBER 6 SELECT T0.U_Material, CASE WHEN T0.U_Material = 'FP1' THEN 'Finished Product' WHEN T0.U_Material = 'FP2' THEN 'Semi-Finished Product' WHEN T0.U_Material = 'FP3' THEN 'Raw Material' ELSE ' ***RESEARCH*** ' END AS 'Material Group' FROM OITM T0
NUMBER 5 – Query Groups on the OCRD Table. It is always good to have a template going from 1 to 64 for both Business Partners and Items. You should make one yourself with 64 for both areas. This can get rather complicated depending on how the Query Groups are used. This Customer used 1 and 19, therefore I modified this one for the blog.
--NUMBER 5 SELECT CASE WHEN T3.QryGroup1 = 'Y' THEN 'House Account' WHEN T3.QryGroup2 = 'Y' THEN 'OEM' WHEN T3.QryGroup7 = 'Y' THEN 'Mixed' WHEN T3.QryGroup14 = 'Y' THEN 'Retail' WHEN T3.QryGroup16 = 'Y' THEN 'Government' WHEN T3.QryGroup18 = 'Y' THEN 'E-Commerce' WHEN T3.QryGroup19 = 'Y' THEN 'Top 10 House Sales' WHEN T3.QryGroup20 = 'Y' THEN 'Top 25 House Sales' WHEN T3.QryGroup21 = 'Y' THEN 'Material' WHEN T3.QryGroup22 = 'Y' THEN 'Non Material' WHEN T3.QryGroup23 = 'Y' THEN 'Cost Center' ELSE ' ***Unassigned*** ' END AS 'Sales Promotion Cust Group' FROM OCRD T3
NUMBER 4 – Canceled AR Invoices on the OINV Table. I have found some folks who write SQL do not know how to handle a document with the Canceled Value of “C” properly. As far as codes and types and statuses go, how to handle canceled documents is one piece of information you MUST understand to get your SQL correct. This data field determines how to calculate dollars and quantities which are consistent with SAP B1’s Sales Analysis or Purchasing Analysis. Telling your End User what kind of Cancellation it is will help them. There will be examples of this in some of the Numeric CASE examples below. I know the “Y” looks crazy, but that is the way it is – look it up in SAP!!!
--NUMBER 4 SELECT T0.CANCELED, CASE WHEN T0.CANCELED = 'C' THEN 'Yes – Reversing Transaction' WHEN T0.CANCELED = 'N' THEN 'No' WHEN T0.CANCELED = 'Y' THEN 'Yes – Non-Reversing Transaction' ELSE ' ***RESEARCH*** ' END AS 'Canceled?' FROM OINV T0
BEFORE SOME NUMERIC CASES ARE LISTED, A NOTE:
You probably get the idea by now, that wherever you see codes or types, you can use a CASE. However, if SAP B1 does have a connecting table, use a JOIN. I would never suggest you use a CASE for Sales Person’s Name when doing an SQL on the AR Invoice. The OSLP table already has the names, so why say “WHEN T1.SlpCode = 101 THEN ‘Zal Parchem’??? It just does not make sense, do the JOIN and get the name from OSLP. Don’t go wild with CASE..
CASES FOR NUMERIC VALUES
I have a financial background, and to me, the most important are those CASEs dealing with numeric values, be it quantities, or dollars, or rates, or currencies, etc. If it is a numeric value, I am interested!!!
NUMBER 3 – Using Two Tables in a NUMERIC CASE on OPOR and POR1 with Currency and ELSE of $9,999,999
[NOTE: With Numeric Cases I use some absurd number like 9,999,999 to find Research Items. As an example, below, if there were a Service Type Purchase Order, the Unit Cost would stick out like a sore thumb in that column. Unless you have an Item with a cost of $10,000,000!!!)]
--NUMBER 3 SELECT T0.DocType, T1.Currency, T1.INMPrice, T1.Rate, CASE WHEN T1.Currency = 'RMB' AND T0.DocType = 'I' THEN (T1.INMPrice * T1.Rate) WHEN T0.DocType = 'I' THEN T1.INMPrice ELSE 9999999 END AS 'Unit Cost (USD) Rounded' FROM OPOR T0 LEFT OUTER JOIN POR1 T1 ON T0.DocEntry = T1.DocEntry
NUMBER 2 – Invoiced Quantity on a AR Invoice Line in INV1. Taking into account the Canceled Status of “C”. SAP keeps a Positive Value in the INV1 table, so you must convert a “C” Canceled POSITIVE VALUE TO A NEGATIVE VALUE – this is the same with sales dollars. Go back up to Number 4 above.
--NUMBER 2 SELECT T1.CANCELED, T0.InvQty, CASE WHEN T1.CANCELED = 'C' THEN (T0.InvQty) * -1 WHEN T1.CANCELED = 'N' THEN (T0.InvQty) WHEN T1.CANCELED = 'Y' THEN (T0.InvQty) ELSE 9999999 END AS 'Invoiced Quantity' FROM INV1 T0 LEFT OUTER JOIN OINV T1 ON T0.DocEntry = T1.DocEntry
NUMBER 1 – Gross Profit on a AR Invoice Document in OINV. Taking into account the Canceled Status of “C”. Go back up to Number 4 above.
--NUMBER 1 SELECT T0.CANCELED, T0.GrosProfSy, CASE WHEN T0.CANCELED = 'C' THEN T0.GrosProfSy * -1 WHEN T0.CANCELED = 'N' THEN T0.GrosProfSy WHEN T0.CANCELED = 'Y' THEN T0.GrosProfSy ELSE 9999999 END AS 'Gross Profit (USD)' FROM OINV T0
SO, WE ARE TO THE END AND THERE IS A NUMBER 0 – If you have any CASE you think would be helpful to others as a “template”, please do share with everyone and post it below as a Comment. I can imagine there are a lot of valuable ones out there.
SAP B1 Forum SQL 03 2019 01 05 CASE in SAP B1 SQL Top Ten