Skip to end of metadata
Go to start of metadata

 (Back to SAP B1 SQL Tips and Tricks Main Page - SAP B1 SQL Tips and Tricks  )


For an example of what is being discussed on this page, see these postings to see how they are done.

SAP B1 SQL D-SL AR Inv and CM List by Gross Profit .

SAP B1 SQL V-MS Listing of All SQL on System  .

SAP B1 SQL S-FS Average Lapsed Days

Title of SQL:  Every posting done in this area must start with "SAP B1 SQL" to help in the Search function of SAP SCN. 

Type of SQL: Include whether this is a Frequently Requested SQL (FRS) or if it is a Monthly Special (MS) in the page you create.

The FRS SQL is the type of query you have seen multiple times in the SAP B1 Forums. Hopefully, the postings will reduce the number of times Forum Members are asking for the same type of query over and over again.

The MS is any SQL which you might have written which is providing a function or serving a purpose a little bit beyond what SAP B1 is reporting and/or providing, or the SQL might be demonstrating a certain concept. This is a good time to highlight your talents as an expert or guru of SQL (and we all know there are lots of good practitioners in SAP B1 SQL generation).

Assignment: To keep it simple, let's assign the SQL to only one of the SAP B1 Modules. If an SQL ends up in two areas it will probably get confusing if and when the SQL changes.

Name: Do not forget to include your name on the posting.

The KISS Concept: Try to keep the SQL with the “KISS” concept (Keep it simple and succinct). A lot of individuals will be coming here (with various levels of SQL knowledge) and the simpler you can keep it, the better for everyone. Of course, if you need to include things like DECLARES and such, just try to provide a small explanation of why you are doing this in the purpose and background section below.

User-Defined Fields: UDFs should be avoided, if at all possible. Again, this Wiki is to share SQL anyone can use. If you must include UDFs in your SQL, please include the reason why, where the UDF is located in the USF Management area, and the complete specifications of the UDF in the purpose and background section below.

Purpose and Background: The background section is probably the most critical piece in providing direction and explanation of the posting which will help others understand what you are doing with your SQL. Why did you create this SQL, what was the business purpose, what are the results, etc? This section is also a good area to explain certain points which are in your SQL. You could include a screen shot of the SQL view in this section which might come in handy for others to see what they will be looking at.

Usage of Caps for SQL Commands/Terms: To help distinguish SQL points and help others learn how SQL is written, use caps for the SQL Commands and words, so it could be SELECT (instead of Select), AND (instead of and), DATEDIFF (instead of DateDiff), etc. It definitely helps out those who are just starting - you get the idea from the posted SQL.

Copying Over SQL: I have found that if I copy the SQL directly from the Query Generator or after executing the query in SAP B1, it formats rather nicely in the posting. The use of the suggested notation with “code:sql” throws some additional formatting in the post (I have no idea why) and makes it a bit more difficult to read.


Put any kind of tip you want in this area. Just try to keep it to a small paragraph explaining what it is you have entered. Sometimes a small example helps demonstrate what the trick or tip is about. There is a lot of these tricks that were shared with me and I bet there are a ton more!


Structure and formatting standards are there for several reasons. One reason is to make sure the information is presented consistently (remember this is going out around the world!). The second reason is to ensure people will be comfortable going from SQL to SQL knowing they will be seeing the same type of information presented in the same manner. What is on the Wiki right now as “standards” is a bare-bones minimum so that help can be provided without requiring a lot of gyrations getting the SQL in the right format. Standards are so subjective, so if you have one, please let us know and let's see if we can include anything new that will contribute to making this a good experience for everyone involved.

  • No labels