Registration

Dear SAP Community Member,
In order to fully benefit from what the SAP Community has to offer, please register at:
http://scn.sap.com
Thank you,
The SAP Community team.
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  )

When writing SQL there can be some specific standards which are extremely helpful for both IT and End User Personnel. Standards, the act of documentation/explanation, providing information which can be helpful later on, and providing structure so the SQL is easily read is often left off in the rush to get the SQL results into the hands of the End User. Not providing some of this information can come back to haunt you later on down the road. Although there can be a number of ways to include standards in SQL, the following is just a personal suggestion from many years of dealing with SAP B1, SQL, and the End Users. There will be an example SQL provided in this Wiki to demonstrate the standards listed below.

STANDARD 1 - TITLE: Instead of giving the SQL a very generic title such as "Late Orders", it might be good to give more of a full description inside the SQL using the following pieces of information:

1. A reference to the module where the SQL appears in the Query Manager or where the SQL is mainly used. For example, I often set up the Query Manager to agree with the Main Menu of SAP B1, starting with "A". With the fourth Module of the Main Menu being "Sales - AR", a code of "D-SL" could be the first part of the SQL title. Other coded examples for the title might be "A-AD", "B-FN", "C-SO", etc with the last entry of the Query Manager being "Z-DV" (Development and Testing).  The full list of codes and organization can be seen at the bottom of the SAP B1 SQL Tips and Tricks page.

2. A desciption indicating what selection parameters are available. For example, "Late Sales Order by Date Range".

3. If SQL is changed or updated often, a good practice is to indicate the version number. Something similar to "Ver 2".

4. The initials of the person who wrote the SQL. In my case it would be "ZP".

5. And lastly the date of when the SQL was written. Today's date is 2011 01 31.

The final SQL title would be "D-SL Late Sales Order by Date Range Ver 2 ZP 2011 01 31". This will help the End User to easily recognize which SQL they are running and will also help IT when it comes time to determine the SQL used by the End User in case of problems or additional requirements for updating the SQL. A title with the above information helps IT to know how many versions of the SQL have been written, who to talk to in order to gain more insight into the purpose of the SQL, and how old the SQL is for maintenance and/or removal from the Query Manager. Include that title inside the text of the written SQL for documentation and back up purposes. The start of the SQL would look like this:

-- D-SL Late Sales Order by Date Range Ver 2 ZP 2011 01 31

[NOTE: Using two dashes in front of any line is a way to notate that this is a non-excutable line to SQL. There are other ways to notate non-executable lines, but I have found this is easier to read by both IT and the End User.  This is the exact SQL Name I use while saving the SQL in the Query Manager after executing the final test of the SQL.]

STANDARD 2 - DESCRIPTION: SAP B1 displays a few lines of the SQL at the top when being run, so use that space to everyone's advantage and give the End Users a chance to see what the SQL is all about. If a company has a large number of SQL under the Query Manager, knowing ahead of time what the results or purpose of the SQL will help eliminate confusion as to what the SQL is doing. Continuing with the example being used here, the additional information placed in the SQL might go something like this:

-- D-SL Late Sales Order by Date Range Ver 2 ZP 2011 01 31

-- DESCRIPTION: SQL provides Customer contact and ship to information so the customer can be contacted about possible late shipments being made. Remarks are also displayed in case the expected ship date has been entered by the Production Department as to when the sales order might ship.

STANDARD 3 - AUTHOR(s) and Versions: The last part of the header information for SQL would display the version number, the author of that version, the date when the version was written, and a short description of what was done after version one. If there is more than one person creating SQL in the company, this becomes extremely important when changes are made so that if any questrion arises you can go back to the original author of the SQL and anyone else involved in updating and/or changing the SQL. The final header of the SQL might look like this:

-- D-SL Late Sales Order by Date Range Ver 2 ZP 2011 01 31

-- DESCRIPTION: SQL provides Customer contact and ship to information so the customer can be contacted about possible late shipments being made.  Remarks are also displayed in case the expected ship date has been entered by the Production Department as to when the sales order might ship.

--AUTHOR(s):
--Version 1 Zal Parchem 2009 11 16
--Version 2 Zal Parchem 2011 01 31 - Included Remarks field used by Production Department for possible shipping dates.

STANDARD 4 - STRUCTURE: No matter how you write the SQL (Query Wizard, Query Generator, SQL Management Studio, etc), the end result being posted to SAP B1 is just a long, single line that is extremely difficult to read, even with shorter SQL. But when the SQL starts including advanced functionality such as CASE and nested SELECT, it can almost be impossible to read. There are several ways to do this and you might have your own ideas, but I generally split up and block out specific sections according to the function inside the SQL to make it easier to read. These are the main sections which every SQL must have to function properly. Building upon the example we are following, when this portion is added to the SQL, it might look something like this:

-- START OF SQL EXAMPLE 

-- D-SL Late Sales Order by Date Range Ver 2 ZP 2011 01 31

-- DESCRIPTION: SQL provides Customer contact and ship to information so the customer can be contacted about possible late shipments being made.  Remarks are also displayed in case the expected ship date has been entered by the Production Department as to when the sales order might ship.

--AUTHOR(s):
--Version 1 Zal Parchem 2009 11 16
--Version 2 Zal Parchem 2011 01 31 - Included Remarks field used by Production Department for possible shipping dates.

SELECT

T0.CardCode AS 'Cust Numb',
T0.CardName AS 'Customer Name',

CASE
WHEN T2.FrozenFor = 'Y' THEN 'On Fin Hold'
WHEN T2.FrozenFor = 'N' THEN 'Not on Fin Hold'
ELSE 'Error'
END AS 'Financial Stat',

T0.DocNum AS 'Sls Ord Numb',
T0.NumAtCard AS 'Cust PO Ref',
T0.DocDueDate AS 'Orig Ship Date',
T0.DocTotal AS 'Sls Ord Total',
T1.Name AS 'Contact Person',
T2.Phone1 AS 'Cust Phone',
T1.Tel1 AS 'Contact Phone',
T0.Comments AS 'SO Remarks'

FROM ORDR  T0

LEFT OUTER JOIN OCPR T1
ON T0.CntctCode = T1.CntctCode

LEFT OUTER JOIN OCRD T2
ON T0.CardCode = T2.CardCode

WHERE

T0.DocStatus = 'O'
AND  T0.DocDueDate >= [%0]
AND  T0.DocDueDate <= [%1]

ORDER BY

T0.CardName,
T0.DocDueDate

FOR BROWSE

--END OF SQL EXAMPLE

SUMMARY:  Providing information and introducing structure to your SQL is definitely a bit more work, but can prove extremely helpful when talking with End Users or when preparing to do a change in the SQL itself.  The above SQL can be easily understood both by the End User and the IT personnel. Some might not appreciate performing this as a habit until the time comes when a change is needed in the SQL.  Again, this is just a suggested outline of what your SQL can look like.  I am sure you can improve upon the information or structure provided here, but the most important point of this section is for you to go that extra step to provide pertinent information and structure inside your SQL.  Those using the SQL and those who follow behind you in IT will greatly appreciate it!  [NOTE: The above SQL example can be copied from this posting and pasted into the Query Generator in case you need some SQL like this.]

  • No labels