I don’t have experience with SQR for DDO (Direct Data Object), which supports JDBC, SAP BW, SAP R/3, Essbase, MS-OLAP, XML, and CSV. I’ve studied several versions of the SQR Language Reference and tried to extrapolate from the SQL version of load-lookup. I apologize if any of these guesses are misleading, and I welcome any help my readers can supply.
Please remember: don’t hit a man with glasses (and I wear glasses).
Mostly Like SQL Parameters
Load-lookup takes four to thirteen parameters. The syntax looks like this.
load-lookup
name=
table=
key=
return_value=
rows=
extent=
where=
sort=
quiet
schema=
procedure= (parameters=)/ command= / getdata=
from-rowset= / from-parameter=
The parameters may be in any order. If a parameter appears more than once, its last value is used. There can be spaces and line breaks before and after the equals signs. More than one parameter can be on a line.
Name is required. It identifies the lookup object for later reference. It is a literal value with no quote marks.
Table is required but not used in SQR DDO.
Key is required. It must be either a character string, a date, or an integer no longer than 12 digits. The expression capability of the SQL version was supported by DBMS. The DDO datasource probably does not return expressions.
Return_value is required. This definitely is a single data element. DDO does not support expressions.
Rows is optional. (Grammatical note: “rows” has the letter “s” at the end, but it is the name of one parameter. Therefore, I use the singular version of the verb “to be.”) It is an estimate of the number of rows that the query will return, to guide load-lookup in allocating memory. If the rows value is not specified, load-lookup will start with an allocation for 100 rows. If load-lookup appears in the setup section, rows must be a literal number without quote marks. Anywhere else, rows may be a literal number, a numeric variable, or a numeric database column.
Extent is optional. If load-lookup receives more rows from the query than were specified in the rows parameter, it will allocate more memory according to the number of rows specified in the extent parameter. You would think that extent couldn’t be used unless rows is used and that extent should be less than rows, but neither condition is required. If the extent value is not specified, load-lookup will set it to 25% of the value for the rows parameter. If load-lookup appears in the setup section, extent must be a literal number without quote marks. Anywhere else, extent may be a literal number, a numeric variable, or a numeric database column.
Where is optional and not used by SQR DDO.
Sort is optional. It can take one of four values without quote marks: DC and DI are ignored in SQR DDO. SC and SI mean that the keys are sorted by the SQR interpreter. SC means that the SQR interpreter sorts the key and is case sensitive (“A” and “a” are different). SI means that the SQR interpreter sorts the key and is case insensitive (“A” and “a” are the same).
If load-lookup doesn’t have a sort parameter, SQR looks for the –LL parameter on the command line, which uses the same four codes. Lacking that, the situation turns muddy. The SQR Language Reference says “By default, SQR lets the database sort the data.” It also says, “The default is SC …”. The default in my environment (Oracle DBMS accessed by SQL) is SC.
Quiet is optional and does not have an equals sign or a value. If it is present, load-lookup executes without notifying the user. If it is absent, load-lookup displays one or two lines of text in the terminal (DOS) window while the program executes. That text also appears in the log file. The first line of text declares that the program is loading the lookup array and reports the number of rows in the array. The second line of text appears only if there are duplicate values of the key found by the query.
Schema, Not Table
Schema is the location of the object being queried within the datasource. The datasource is specified by the SQR command line or a declare-connection command. If load-lookup appears in the setup section, schema must be a literal string. Anywhere else, schema can be a literal string or a character string variable. Procedure, command, or getdata may be used in the context of the schema – I think exactly one of them is needed to specify the method for retrieving data.
Procedure OR Command OR Getdata
Procedure is the name of a stored procedure in the datasource (i.e. an SAP R/3 BAPI). There may be spaces in a procedure name, which suggests that quote marks are optional. If load-lookup appears in the setup section, procedure must be a literal string. Anywhere else, procedure can be a literal string or a character string variable. If the procedure clause is used, the parameters parameter may be used.
Parameters (not to be confused with from-parameter) is optional with the procedure keyword in the sense that SQR load-lookup does not require it, but if the stored procedure takes parameters, then parameters is mandatory. The parameters for the stored procedure are enclosed in parentheses and separated by commas, as with a SQR procedure call. There must be the same number of parameters as the stored procedure expects, in the same order that the stored procedure expects.
Each parameter may be a list variable, a numeric literal, a numeric variable, a text literal, a text variable, a database column, or the keyword null. Each parameter (except null) may be followed by the keywords in or inout. If the in or inout keyword is missing, in is assumed. I am guessing that in means the value is passed to a variable in the stored procedure but any change in the value of that variable is not passed back to SQR. I am guessing that inout means the value is passed to a variable in the stored procedure and any change in the value of that variable is passed back to SQR.
Command is a command for the datasource to execute. If load-lookup appears in the setup section, command must be a literal string. Anywhere else, command can be a literal string (which may include SQR variables) or a character string variable.
Getdata is for the Java (DDO) GetData method. If load-lookup appears in the setup section, getdata must be a literal string. Anywhere else, getdata can be a literal string or a character string variable.
From-Rowsets OR From-Parameter
Either from-rowsets or from-parameter may be used to filter the results of the load-lookup query, however from-parameter is only for SAP R/3 datasources. The SQR Language Reference states that from-parameter should be used only with the procedure parameter, but it also states that from-parameter may be used with the procedure, command, or getdata parameters. Does anybody know which statement is correct?
From-rowsets selects rowsets by their sequence number. The syntax is similar to the page range field in the Microsoft Windows print dialog box that specifies pages to print. The sequence items are enclosed in parentheses and separated by commas. The sequence items should not overlap; there should not be two items that refer to the same sequence number. The rowsets should be specified in ascending order. All the specified rowsets should have the same column/type signature. Each sequence item can take one of these forms.
- The word “all”, indicating that all rowsets from the datasource should be included. If this is a sequence item, it should be the only sequence item.
- A dash, followed by a positive integer or numeric variable (-7), indicating that all rowsets with sequence numbers less than or equal to that number should be included. If this is a sequence item, it should be the first sequence item.
- A positive integer or numeric variable, indicating that the rowset with that sequence number should be included.
- A positive integer or numeric variable, followed by a dash, followed by a positive integer or numeric variable (3-7), indicating that all the rowsets with sequence numbers in that range (3, 4, 5, 6, 7) should be included.
- A positive integer or numeric variable, followed by a dash (3-), indicating that all rowsets with sequence numbers greater than or equal to that number should be included. If this is a sequence item, it should be the last sequence item.
From-parameter is not well defined in the documentation. Since it is an alternative to from-rowsets, it might serve to select rowsets according to the value of a parameter. If it is only to be used with the procedure parameter, then it might refer to a parameter listed in the parameter parameter. (Yes, I am ashamed of that sentence. This is why I use boldface for SQR keywords.) If load-lookup appears in the setup section, from-parameter must be a literal string. Anywhere else, from-parameter can be a literal string or a character string variable.
Looking Ahead
Next week, we’ll wrap up this mini-series with examples of load-loadup (for SQL databases) in action. I hope the algorithms will justify the time we’ve spent on this command.
Brain Teaser
Meanwhile, here is a brain teaser. Please post the solution as a comment.
Different organizations have different fiscal years. The variable #end_month contains an integer from 1 to 12, which indicates the last month of the fiscal year (1 = January, common for retailers; 9 = September, for federal government agencies). The fiscal year is a four digit number (like 2009 or 2010), which is usually the calendar year that contains the last month of the fiscal year (October 2008 is the first month of the U.S. government’s 2009 fiscal year). For this puzzle, fiscal years, fiscal quarters, and fiscal months always begin on the first day of a calendar month. We are given #end_month and $as_of_date. Please supply the SQR code to answer these questions.
- What are the first and last days of the fiscal year that includes $as_of_date?
- What are the first and last days of the fiscal quarter that includes $as_of_date?
- Which fiscal quarter (1, 2, 3, or 4) includes $as_of_date?
- How many days are in each fiscal quarter in the fiscal year that includes $as_of_date?
Solution for the Brain Teaser (1)
In the solution below #FY_EndMonth and the $Date are the input parameters
Begin-Setup
Declare-Variable
Date $Date $FY_StartMonth_StartDt $FY_EndMonth_StartDt $FY_EndMonth_EndDt
End-Declare
End-Setup
Begin-Program
Let #FY_EndMonth = 8
Let $Date = StrToDate(’11/10/2004′,’MM/DD/YYYY’)
Let $FY_EndMonth_StartDt1 = Edit(#FY_EndMonth,’09’) || ‘/01/’ || DateToStr($Date,’YYYY’)
Let $FY_EndMonth_StartDt = StrToDate($FY_EndMonth_StartDt1,’MM/DD/YYYY’)
Let $FY_StartMonth_StartDt = Dateadd($FY_EndMonth_StartDt,’month’,-11)
Let $FY_EndMonth_EndDt = Dateadd(Dateadd($FY_EndMonth_StartDt,’month’,1),’day’,-1)
If $Date > $FY_EndMonth_EndDt
Let $FY_StartMonth_StartDt = DateAdd($FY_StartMonth_StartDt,’year’,1)
Let $FY_EndMonth_EndDt = DateAdd($FY_EndMonth_EndDt,’year’,1)
End-If
Show ‘Fiscal Year: First Date of First Month ‘ $FY_StartMonth_StartDt
Show ‘Fiscal Year: First Date of Last Month ‘ $FY_EndMonth_StartDt
Show ‘Fiscal Year: Last Date of Last Month ‘ $FY_EndMonth_EndDt
End-Program
!Complete Solution for all the questions in the brain teaser
Begin-Setup
Declare-Variable
Date $Date $FY_StartMonth_StartDt $FY_EndMonth_StartDt $FY_EndMonth_EndDt $Q1_StartDate $Q1_EndDate
Date $Q2_StartDate $Q2_EndDate $Q3_StartDate $Q3_EndDate $Q4_StartDate $Q4_EndDate
End-Declare
End-Setup
Begin-Program
Let #FY_EndMonth = 8
Let $Date = StrToDate(’11/10/2004′,’MM/DD/YYYY’)
Let $FY_EndMonth_StartDt1 = Edit(#FY_EndMonth,’09’) || ‘/01/’ || DateToStr($Date,’YYYY’)
Let $FY_EndMonth_StartDt = StrToDate($FY_EndMonth_StartDt1,’MM/DD/YYYY’)
Let $FY_StartMonth_StartDt = Dateadd($FY_EndMonth_StartDt,’month’,-11)
Let $FY_EndMonth_EndDt = Dateadd(Dateadd($FY_EndMonth_StartDt,’month’,1),’day’,-1)
If $Date > $FY_EndMonth_EndDt
Let $FY_StartMonth_StartDt = DateAdd($FY_StartMonth_StartDt,’year’,1)
Let $FY_EndMonth_EndDt = DateAdd($FY_EndMonth_EndDt,’year’,1)
End-If
Show ‘Fiscal Year: First Date of First Month ‘ $FY_StartMonth_StartDt
!Show ‘Fiscal Year: First Date of Last Month ‘ $FY_EndMonth_StartDt
Show ‘Fiscal Year: Last Date of Last Month ‘ $FY_EndMonth_EndDt
!Quarter 1 Dates
Let $Q1_StartDate = $FY_StartMonth_StartDt
Let $Q1_EndDate = DateAdd(DateAdd($Q1_StartDate,’month’,3),’day’,-1)
If range($Date,$Q1_StartDate,$Q1_EndDate) = 1
Show ‘Q1 Start Date: ‘ $Q1_StartDate
Show ‘Q1 End Date: ‘ $Q1_EndDate
Let #Days = DateDiff($Q1_EndDate,$Q1_StartDate,’day’) + 1
Show ‘No Of Days in this Quarter :’
Show #Days
End-IF
!Quarter 2 Dates
Let $Q2_StartDate = DateAdd($Q1_EndDate,’day’,1)
Let $Q2_EndDate = DateAdd(DateAdd($Q2_StartDate,’month’,3),’day’,-1)
If range($Date,$Q2_StartDate,$Q2_EndDate) = 1
Show ‘Q2 Start Date: ‘ $Q2_StartDate
Show ‘Q2 End Date: ‘ $Q2_EndDate
Let #Days = DateDiff($Q2_EndDate,$Q2_StartDate,’day’) + 1
Show ‘No Of Days in this Quarter :’
Show #Days
End-If
!Quarter 3 Dates
Let $Q3_StartDate = DateAdd($Q2_EndDate,’day’,1)
Let $Q3_EndDate = DateAdd(DateAdd($Q3_StartDate,’month’,3),’day’,-1)
If range($Date,$Q3_StartDate,$Q3_EndDate) = 1
Show ‘Q3 Start Date: ‘ $Q3_StartDate
Show ‘Q3 End Date: ‘ $Q3_EndDate
Let #Days = DateDiff($Q3_EndDate,$Q3_StartDate,’day’) + 1
Show ‘No Of Days in this Quarter :’
Show #Days
End-If
!Quarter 4 Dates
Let $Q4_StartDate = DateAdd($Q3_EndDate,’day’,1)
Let $Q4_EndDate = $FY_EndMonth_EndDt
If range($Date,$Q4_StartDate,$Q4_EndDate) = 1
Show ‘Q4 Start Date: ‘ $Q4_StartDate
Show ‘Q4 End Date: ‘ $Q4_EndDate
Let #Days = DateDiff($Q4_EndDate,$Q4_StartDate,’day’) + 1
Show ‘No Of Days in this Quarter :’
Show #Days
End-If
End-Program