Here are some basic tips and tricks when writing SQL in SAP Business One (SAP B1):
(Back to SAP B1 SQL Tips and Tricks Main Page - SAP B1 SQL Tips and Tricks )
SAP B1 FORUM: The SAP B1 Forum is probably the best place to go to when you are having difficulties in writing SQL or if the results are not what you expected. There are several individuals there who are always willing to help and who are the REAL experts in SQL. Some pretty amazing SQL has been posted in the forums, so do a search on the term "Query" and you might find a lot of examples or previous SQL you can use directly as it was posted in the forum or you can modify the posted SQL according to your needs.
DOCUMENT AND STRUCTURE YOUR SQL: One of the best ways to ensure your SQL will be used and understood correctly is to provide adequate documentation on the SQL and structure the SQL so it is easily read during error research and updating the SQL. There are some special pages on that topic located here: SAP B1 SQL Suggested Standards - Basic Outline of the SQL .
SHARING YOUR SQL: You have some dynamite SQL you have written or maybe you have modified/enhanced some SQL from the SAP B1 Forums? Then do everyone a favor and help others out by posting some of your own SQL here under the SQL and Queries Landing Page. Just do a quick view to see you are not duplicating previously posted SQL. Make sure your posted SQL can be used by any installation of SAP B1 as a "copy and paste" out of the Wiki pages (no user-defined fields or user-defined tables). Place the SQL in the structure described above (or maybe someone will come along and modify it later on) so it can be read easily and understood by the vast range of personnel coming to the Wiki pages (from the basic SQL Beginners to the SQL Gurus).
LEARNING SQL (some suggested resources): There are a lot of books and publications out there dealing with learning SQL. There are also many free web sites where you can research commands and do practice with SQL. You will probably see more entries in this area as time goes on. For a book suggestion, a good basic SQL book is from Alan Beaulieu with O'Reilly Publishers titled appropriately "Learning SQL". Check it out at Amazon. And as a basic website it is http://www.w3schools.com/sql/default.asp : one that I frequently mention in the Forums. Also check out this page for more suggestions - SAP B1 SQL Links and Additional Information .
USE SQL TO IMPORT DIRECTLY TO EXCEL 2007: If you are experienced enough in SQL and feel like acquiring a new technique on using SQL, check out this video which shows how you can import data directly into Microsoft Excel 2007 http://technet.microsoft.com/en-us/sqlserver/ff686858.aspx . Even though Office 2010 is now standard for MS, hopefully this training video from Microsoft on 2007 will be around for a while.
PLACE FORMATTED SEARCHES IN THEIR OWN CATEGORY: While you are setting up your Categories in the Query Manager for SAP B1, set one Category as "S - Formatted Searches". Make sure you have the proper Authorizations set by using a special and specific group while adding the Category. Why? Because Formatted Searches can be used across the entire spectrum of SAP B1 Modules and if they are available to update or create by anyone, others could possibly update them and, therefore, cause havoc in the business processes.
ALL TABLES ON SAP B1: To get a list of all tables that are on SAP B1, take this path Tools > Queries > Query Generator. In the upper left hand field enter an asterick "*" and then hit the "Tab" key. Another window will pop up which shows a list of tables on SAP B1. Export the results to Excel and then you can sort the spreadsheet by Name or by Description depending upon your needs. You can also make notes in the spreadsheet about those tables when some question arises in your work. Here is a list from SAP2007A - All Tables on SPL2007A 2011 02 08.xls
ALERT MESSAGES HAVE A LIMIT: The amount of information which is displayed by an SQL being used as an Alert Message is limited. If you have created an Alert Message, make sure only critical data is displayed so the Alert Message displays all of the rows (records) of the SQL display. Leave out long fields like BP name or Item Description, but make sure the drill-down arrows appear in the Alert Message so the End User can view the detail. Run the SQL on your own and note the number of records displayed, then run the Alert Message. If the number of records are not the same, then go back and do a reduction in the number of fields being displayed until the SQL results and the Alert Message results are the same.
FOR BROWSE: Sometimes you will loose the orange drill-down arrows while writing SQL in SAP B1 (for a variety of reasons). One way to get the orange drill-down arrows to reappear in the SQL results is to put the phrase "FOR BROWSE" at the very end of your SQL. Sometimes the "FOR BROWSE" will not help bring back the orange drill-down arrows; for example, when using a "UNION" in your SQL or with some other advanced functions. Go ahead and get into the habit of entering the phrase at the end of each SQL you write and when the SQL does not allow it, you will get an error message specifically stating "FOR BROWSE" cannot be used.
CONCANTENATE: Although this is a common command in other types of SQL, it does not work when writing SQL for SAP B1. The solution is to combine two fields together with a "+" (plus sign) on a single line. For example, T1.First Name + ' (leave a space between the single quote marks) ' + T1.LastName AS 'Employee Name'. This will combine the two fields together in a single field on the SQL results and give a better touch to the results than two seperate fields on the SQL view.
CHECK FOR ALL CODES/VALUES IN CASE: When using the function of CASE in your SQL, make sure you check for all of the possible values associated with the field or calucation. It is usually best to have the ELSE to contain some indicator that a field has different information by using words such as "Error" for alphanumeric fields, or "9999999" for numeric, etc. This is extremely important when you use a CASE in your SQL dealing with User-Defined Fields (UDFs) where the End Users can create their own values for the UDF. Here is an example of how this check can be used (T1 = OITM table):
WHEN T1.PrcrmntMtd = 'B' THEN 'Buy'
WHEN T1.PrcrmntMtd = 'M' THEN 'Produce'
END AS 'Buy Or Make'
DO NOT USE CERTAIN WORDS IN COMMENTS AND IN CASE TEXT: For some reason, the SQL will read the word "from" as being part of a JOIN statement and will produce an error message. Instead of saying 'Buy From' in your CASE text section, use something like "Buy Frm" when you want to use the word "from". Kind of funky, but might help you from getting an error message. The word "change" can sometimes cause a problem also - use "chg" instead.
USE CHARACTERS OF ! AND = FOR DENOTING "NOT EQUAL": For some unknown reason, when posting to the SAP B1 forums and even though you are using the code markup in your postings, the system does not recognize " < > " for the "not equal" function and it comes out blank in the forum. So, if you are posting to the SAP B1 forums, use the exclamation point and equal sign to indicate NOT EQUAL. Here is an example of this: Different NOT EQUAL Characters Show Different Results
REPLICATION OF PARTS WHEN USING UNION: When using the UNION function, make sure the top part of the UNION agrees completely with the second part of the UNION in commands and in data fields. There should be a mirror image when looking at the two parts. Making a change some times causes problems where the SQL fails to execute. If that happens, the first thing is to do a good review of the top part and subseqent parts which helps immensely in avoiding this common error.
GETTING ONLY ONE OR ALL WITH A SPECIAL WHERE CLAUSE: You can use a special WHERE clause to be able to select only one from a list or all from a list. For example, to get one individual Sales Person, you can enter the Sales Person's name - OR - you can enter nothing in the Selection Criteria screen and hit enter to get a listing of all of the Sales Persons in your SQL. Notice the special use of LIKE and the % in this example:
WHERE (T1.SlpName LIKE '%%[%0]%%' OR '[%0]' = ' ')
TRANSACTION TYPES AND THEIR DEFINTIONS: When working with the General Ledger or possibly Stored Procedures, you might need to know what the TransType is for completing your work. Here is a list contained in a CASE statement showing the TransType and what they mean. Please be aware of the fact that this list might not be complete, but it contains probably the vast majority of the TransType your work requires, so take a look : SAP B1 SQL Journal Entry Detail by Date Range