Manage Queries

In this section users are allowed to define query as well as stored procedure for the retrieval of data . Queries can be written to fetch data from the tables stored in the database. Users can also use stored procedures that are defined in SQL server to fetch the data from the database. Innowera Control Panel also allows user to make query to SAP table and fetch the data that is stored in these SAP tables. Innowera Control Panel rules are written in VB .NET code and can use the queries that are defined in the Queries page. When a rule is configured in Mapper of the Process Runner and  the process file is run, these rules are handled by Innowera Control Panel. When the queries are run, the data is fetched from the database or SAP Table.

Queries section displays the following details in tabular form.

Add Query

Add button is used to add new query. Select Add, a new window is displayed to enter the details of the query.

Database with Standard Query type

Standard Query allows users to write queries to fetch data from the tables stored in the Database.

• Query Name: Name set by the user for the query.
• Connect To: Allows to select platform for the data to validate as per your requirement or your environment.
• Database: Allows user to define that the below provided query will retrieve data from the databases mentioned in Database Connection screen.
• Connection Name: Lists all the Connection Name that are present in the Database Connections screen. This refers to the database from which the query will fetch the data. Select appropriate connection from drop down. At a time of process file execution, it will call a data using provided query on selected connection and this data will be used to validate data present in process file.
• Execution Level: Two options are provided for this field. First is 'Client side' and second is 'Server side'. From this option, you can set platform that will execute the queries. On selection of 'Client side' the connection to the database and execution of queries will be done at Process Runner side and on selecting 'Server side' the connection to database and execution of queries will be done at Innowera Control Panel side. 'Client side' is set as default. Please note that for Oracle DB only Server side is available.
• Query type: Allows user to select Standard Query or Stored Procedure to select the method to be used to define the queries. Standard Query allows user to define query in the Query statement section whereas stored procedure allows users to use the stored procedures defined in SQL database.
• Query statement: This is displayed on selection of 'Standard Query' as the Query type. In this section provide query statement to be executed on selected database.

For example

SELECT * FROM MaterialInfo WHERE [MaterialNO] = '%IVALUE@A%'

The following filters can be used in the query to filter data.

1. %IVALUE@A% fetches the data from the column A of the Excel sheet for the required field.
2. %IVALUE@A1%  fetches the data for a field from a particular cell of the Excel sheet.
3. %IVALUE@A1:A5% fetches the data for a field from the specified range in Excel sheet.
4. %IVALUE@G.ADDLEADINGZERO(10)%  appends zero before value and make sure the length of field is 10.
5. %IVALUE@A.DAY% fetches the day.
6. %IVALUE@A.MONTH% fetches the month.
7. %IVALUE@A.YEAR% fetches the year.
8. %IVALUE@A.MINUTE% fetches the minutes.
9. %IVALUE@A.HOUR% fetches the hour.
10. %IVALUE@A.SECOND% fetches the second.

When any of the above filter is applied except the range filter, the rule is executed for each and every row of the process file as per the filter condition. As the rule is complied and executed for each row there may be a drop in the performance.

When range filter is applied, the specified range values are picked and passed as filter condition. The rule is not complied and executed for each row as all the records are fetched at a time. Users have to use "IN" keyword before range specification in the query.

For example,

Fieldname IN '%IVALUE@A2:A10%'

Note: ACS will be applied to the first specified field in filter condition. The default length for ACS is 38000 and this can be modified from the Options settings page.

• Test: The query under selected database gets executed and returns the result. If the above mentioned filters are applied then 'Test' action cannot be performed.
• Save: The details get saved and are displayed in Queries screen.
• Cancel: Closes Add Query window and takes back to Queries screen.

Database with Stored Procedure

Stored Procedure option allows users to select the stored procedures that are defined in SQL server. The stored procedures can be used over the network by several clients using different input data. If the stored procedure is modified all the clients get the updated stored procedure.

• Name: Name set by the user for the query.
• Connect To: Allows to select platform for the data to validate as per your requirement or your environment.
• 'Database' as selection allows user to define that the below provided query will retrieve data from the databases mentioned in Database Connection screen.
• Connection Name: Lists all the Connection Name that are present in the Database Connections screen. This refers to the database from which the query will fetch the data.Select appropriate connection from drop down. At a time of process file execution, it will call a data using provided query on selected connection and this data will be used to validate data present in process file. Please note that this feature is not available with database type other than SQL.
• Execution Level: Two options are provided for this field. First is 'Client side' and second is 'Server side'. From this option, you can set platform that will execute the queries. On selection of 'Client side' the connection to the database and execution of queries will be done at Process Runner side and on selecting 'Server side' the connection to database and execution of queries will be done at Innowera Control Panel side. 'Client side' is set as default. Note that for Oracle database only Server side is available.
• Query type: Allows user to select Standard Query or Stored Procedure to select the method to be used to define the queries. Standard Query allows user to define query in the Query statement section whereas Stored Procedure allows users to use the stored procedures defined in Microsoft SQL database.
• Stored Procedure Name: This is displayed on selection of 'Stored Procedure' as the Query type. Enter the name of the Stored Procedure given by user while creating it in Microsoft SQL database.
• Get Stored Procedure Info.: helps to retrieve details of stored procedure. The input/output parameters defined in the stored procedure are displayed when you select this button as shown in above image. Only users having access to database can perform this action.

Select the plus icon if you need to manually add any parameter that is defined in Stored Procedure. Enter the parameter name, select the type from the drop-down and enter the parameter value to be passed in the Value field.

Select the delete option  to delete any parameter.

Select the edit option to edit any existing parameter.

The below filters can be used  for the input parameters to filter the data.

1. %IVALUE@A% fetches the data from the column A of the excel sheet for the required field.
2. %IVALUE@A1%  fetches the data for a field from a particular cell of the excel sheet.
3. %IVALUE@A1:A5% fetches the data for a field from the specified range in excel sheet.
4. %IVALUE@G.ADDLEADINGZERO(10)% appends zero before value and make sure the length of field is 10
5. %IVALUE@A.DAY% fetches the day.
6. %IVALUE@A.MONTH% fetches the month.
7. %IVALUE@A.YEAR% fetches the year.
8. %IVALUE@A.MINUTE% fetches the minutes.
9. %IVALUE@A.HOUR% fetches the hour.
10. %IVALUE@A.SECOND% fetches the second.

When any of the above filter is applied except the range filter, the rule is executed for each and every row of the process file as per the filter condition. As the rule is complied and executed for each row there may be a drop in the performance.

When range filter is applied, the specified range values are picked and passed as filter condition. The rule is not complied and executed for each row as all the records are fetched at a time. In Stored Procedure users can use "IN" keyword and create query or for the value specified the filter data will come separated by comma, split them and create filter condition for it .  Note that ACS will be not be applied in this scenario.

• Save: The details get saved and are displayed in Queries screen.
• Cancel: Closes Add Query window and takes back to Queries screen.

Live Query

• Query Name: Name set by the user for the query.
• Connect To: Allows to select platform for the data to validate as per your requirement or your environment.
• SAP as selection allows the user to define that the query will retrieve data from the SAP Table /View.
• SAP Table/View Name: Enter the SAP table/view name. When the process file is run, query will make a call to SAP. The data is fetched which is stored in the SAP table. The retrieved data will then be used to validate the data present in the process file.
• Field Name: The SAP table /view usually consist of many fields. The fields which are essential and need to be fetched during SAP call can be mentioned in this text box by entering their field names. Field names are to be separated by comma for multiple fields.

e.g:  MATNR, MATKL, MTART

Note:

• A 'Total Field width' of 512 characters per record is the standard (feature/limitation) delivered in all standard SAP systems. Floating data types are not supported.
• Custom Filter: Filters can be set to fetch conditional data from the SAP table. This reduces processing time by providing extra information like custom filter and max number of records.
• The syntax of the custom filter should be according to ABAP Where condition.

The format of Custom Filter is Field1='conditional value' where Field1 is the name of the field. User can use any of the filter explained previously in Query Statement for SAP Table also.

For example,

1. FieldName='100% ' fetches data starting with 100.
2. If the user wants to fetch the data for a field from a particular column of the excel sheet , then custom filter like MATNR = '%IVALUE@A%' fetches the data from the column A of the excel sheet for MATNR field.
3. If the user wants to fetch the data for a field from a particular cell of the excel sheet , then custom filter like MATNR = '%IVALUE@A1%' fetches the data from the first row of column A of the excel sheet for MATNR field. When range filter is applied, the specified range values are picked and passed as filter condition. The rule is not complied and executed for each row as all the records are fetched at a time. Users have to use "IN" keyword before range specification in the query. For example: Fieldname IN '%IVALUE@A2:A10%' .  Please note that ACS will be applied to the first specified field in filter condition. The default length for ACS is 38000 and this can be modified from the Options settings page.
4. If the user wants to fetch the data for a field from a particular range of the excel sheet, then custom filter like MATNR IN '%IVALUE@A2:A10%' fetches the data from the row A2 to A10 of column A of the excel sheet for MATNR field.
5. If the field value of a particular length is required , then custom filter like SAKNR = '%IVALUE@G.ADDLEADINGZERO(10)%' AND KTOPL = 'INT' will append zeros and make sure the length of field SAKNR is 10.
6. If the year needs to be fetched, then custom filter like [VBKD-BSTDK] = '%IVALUE@A.YEAR%'  will fetch the year and pass on the value for VBKD-BSTDK field.

• Max Records: The number of records will be fetched from the SAP table. Enter '0' to fetch all the maximum available records from the table.
• Save: The details get saved and are displayed in Queries screen.
• Cancel: Closes Add Query window and takes back to Queries screen.

Note: In 'Execution Level' if user select 'Client side' option then it may take more time in execution of calls made to SAP and if user select 'Server side' then it may be possible that Control Panel service throws time out exception if processing takes more time.

Edit Query

Select Edit option to edit the selected query in the list.

Admin Info

• Created By:  Displays the name of the user who created the database query.
• Created On: Displays the date and time when the database query was added.  The time displayed is as per user time zone settings.
• Changed By: Displays the name of the user who updated the database query.
• Changed On: Displays the date and time when the database query was last updated.The time displayed is as per user time zone settings.

What is Automatic Condition Splitter (ACS)

While extracting data from SAP using queries with large where condition and  the length of resultant where condition is more than allowed by SAP underlying database the process will not execute and will result in error from SAP.To overcome this issue Automatic Condition Splitter (ACS) is developed to split the where condition. ACS is designed especially to work with SAP standard delivered function module. ACS will handle the length of where condition by splitting the where condition internally keeping the length within the specified limit. The number of calls made to SAP will be number of slices made by ACS of the where condition. Note that no changes in SAP are required to use ACS. Similarly for database queries if the value of where condition is coming from Microsoft SQL or database and the length of resultant where condition is more, then the ACS will handle the length of where condition by splitting it internally and keeping the length within specified limit.

Delete Query

Select the delete icon to delete selected query from the list.