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). NUMBER 9 – The Document Type on ORDR (but this can be used on any Document table). NUMBER 10 – The Business Partner Type on OCRD. Your choice as it is just a personal preference, but you will see the escape or research line throughout all of the CASEs below. Instead of the word ***RESEARCH***, some folks like to use a blank field. Or just double click on the column name of “Details” to sort. Then go out and find what that TransType is. Just use the “Filter” function and reduce your SQL to those lines having the phrase ***RESEARCH*** in the column of “Details”. WHEN T0.TransType = 15 THEN ‘AR Deliveries’īy 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. WHEN T0.TransType = 14 THEN ‘AR Credit Memo’ WHEN T0.TransType = 13 THEN ‘AR Invoices’ –Do not run this – it will not work (just part of a CASE coming later) I generally start out like this, always including the research line in the SQL below: Instead, you could list the number AND create a CASE to give a good description of what that number means. 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. Say, for instance, you must write some SQL to display what the Document Name is on the Inventory History table (OINM). This little “research’ trick will help you out on that situation. Some SQL I had written covering Financials was not working correctly, and it took me a long time to find out what was wrong. 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. 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). Comment or remove those fields when you go to finalize the SQL (see first line bolded below). Itis a good idea to include the fields in the CASE when drafting the SQL – helps to identify how things are being handled. PROVIDING A WAY TO IDENTIFY MISSING INFORMATION/CODES. 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!!! 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. 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. You might need to change some of the CASEs given below, or you might want to modify the SQL to your own preferences. Just a reminder that this blog is to introduce a concept. Very handy when writing any kind of report for the End Users – just copy it in and onwards you go. I have a Word Document, about 8 pages long, with various CASEs used while writing SQL. 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. Many of them are interchangeable with various tables. The CASEs in this blog are some commonly requested types of CASE Statements in the Forum. And you use END to let the SQL know you are finished with your IF/THEN lines. You must tell SQL first that you are about to do the IF/THEN by using the word “CASE”. 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.). 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. Instead of having an “O” under a DocStatus column, you can display the word of “Open”. 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. 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. Am currently working with an individual, hoping to help her improve her SQL skills.
0 Comments
Leave a Reply. |