ImportSapQuery Script Examples: Difference between revisions
Jump to navigation
Jump to search
(403051) |
No edit summary |
||
| (One intermediate revision by the same user not shown) | |||
| Line 1: | Line 1: | ||
This page contains script examples for the [[ | This page contains script examples for the [[SQL Scripting Commands#--.23ImportSapQuery|ImportSapQuery]] script command. | ||
The following script will get the "VBELN", "ERDAT", "ERZET", "ERNAM", "NETWR", and "WAERK" columns from the "VBAK" table in a SAP system and put them into a data table named "SapQueryTableExample". | The following script will get the "VBELN", "ERDAT", "ERZET", "ERNAM", "NETWR", and "WAERK" columns from the "VBAK" table in a SAP system and put them into a data table named "SapQueryTableExample". | ||
Latest revision as of 20:47, 25 October 2021
This page contains script examples for the ImportSapQuery script command.
The following script will get the "VBELN", "ERDAT", "ERZET", "ERNAM", "NETWR", and "WAERK" columns from the "VBAK" table in a SAP system and put them into a data table named "SapQueryTableExample".
(SELECT 'ProjectName', 'ImportSapQueryExample') UNION ALL (SELECT 'DataTableName', 'SapQueryTableExample') UNION ALL (SELECT 'Append', 'TRUE') UNION ALL (SELECT 'SapUser', 'exampleuser') UNION ALL (SELECT 'SapPW', 'examplepassword') UNION ALL (SELECT 'SapClient', '200') UNION ALL (SELECT 'SapAppServerHost', '127.0.0.1') UNION ALL (SELECT 'SapSystemNumber', '10') UNION ALL (SELECT 'SapLanguage', 'EN') UNION ALL (SELECT 'SapPoolSize', '5') UNION ALL (SELECT 'SapPeakConnectionsLimit', '10') UNION ALL (SELECT 'SapConnectionIdleTimeout', '600') UNION ALL (SELECT 'SapRouter', '/H/127.0.0.1/A/1234/H/') UNION ALL (SELECT 'SapLogonGroup', 'GROUPXNAME') UNION ALL (SELECT 'SapQueryMode', '1') UNION ALL (SELECT 'SapQueryTable', 'VBAK') UNION ALL (SELECT 'SapDelimiter', '|') UNION ALL (SELECT 'SapRowcount', '0') UNION ALL (SELECT 'SapRowskips', '0') UNION ALL (SELECT 'SapWhereClause', 'VBELN EQ `0060000039`, OR VBELN EQ `0060000040`') UNION ALL (SELECT 'SapFieldNames', 'VBELN,ERDAT,ERZET,ERNAM,NETWR,WAERK') --#ImportSapQuery
The following script will extract values for the VBELN field from the VBAK table where the value of the VBELN field is between 0060000039` and `0060000041. It will also catch possible exceptions when getting the data and print out them on a separate sheet. The extracted data is also shown on its own sheet:
(SELECT 'CatchOperationExceptions', '1') UNION ALL (SELECT 'SapAppServerHost', '127.0.0.1') UNION ALL (SELECT 'SapSystemNumber', '10') UNION ALL (SELECT 'SapUser', 'qpr') UNION ALL (SELECT 'SapPW', 'demo') UNION ALL (SELECT 'SapRouter', '') UNION ALL (SELECT 'SapClient', '200') UNION ALL (SELECT 'SapLanguage', 'EN') UNION ALL (SELECT 'SapPoolSize', '5') UNION ALL (SELECT 'SapPeakConnectionsLimit', '10') UNION ALL (SELECT 'SapConnectionIdleTimeout', '600') UNION ALL (SELECT 'ExecuteInClientSide', '1') UNION ALL (SELECT 'TargetTable', '#SAPmode1') UNION ALL (SELECT 'Append', '0') UNION ALL (SELECT 'SapWhereClause', 'VBELN BETWEEN `0060000039` AND `0060000041`') UNION ALL (SELECT 'SapQueryTable', 'VBAK') --#ImportSapQuery DECLARE @_SuccessOrNot as NVARCHAR(MAX); SET @_SuccessOrNot = CASE @_ExceptionOccurred WHEN 1 THEN 'Exception(s) occurred!' ELSE 'SAP import OK.' END SELECT @_SuccessOrNot as Result, @_ExceptionOccurred as ExceptionOccurred, @_ExceptionType as ExceptionType, @_ExceptionMessage as ExceptionMessage, @_ExceptionDetails as ExceptionDetails (SELECT 'SheetName' , 'ExceptionData') --#ShowReport (SELECT * FROM #SAPmode1) (SELECT 'SheetName' , 'SAPmode1') --#ShowReport
The following script will return the values for the VBELN, ERDAT, ERZET, ERNAM, NETWR, and WAERK fields from the VBAK table where the value of the VBELN field is between 0060000039 and 0060000041:
/* First, create the temporary table that holds the WHERE clause. */ CREATE TABLE #SapWhereClauseTable (sap_select_string varchar(255), order_number int) INSERT INTO #SapWhereClauseTable SELECT 'VBELN BETWEEN ''0060000039''', 1 INSERT INTO #SapWhereClauseTable SELECT 'AND ''0060000041''', 2 /* Specify the target for the data that the script extracts from SAP */ (SELECT 'TargetTable', '#SAPmode1') UNION ALL (SELECT 'Append', '0') UNION ALL /* Define the SAP connection parameters */ (SELECT 'SapAppServerHost', '127.0.0.1') UNION ALL (SELECT 'SapSystemNumber', '10') UNION ALL (SELECT 'SapUser', 'exampleuser') UNION ALL (SELECT 'SapPW', 'examplepassword') UNION ALL (SELECT 'SapClient', '200') UNION ALL (SELECT 'SapLanguage', 'EN') UNION ALL (SELECT 'SapPoolSize', '5') UNION ALL (SELECT 'SapPeakConnectionsLimit', '10') UNION ALL (SELECT 'SapConnectionIdleTimeout', '600') UNION ALL (SELECT 'SapFieldNames', 'VBELN,ERDAT,ERZET,ERNAM,NETWR,WAERK') UNION ALL /* Use the WHERE clause defined in the temporary table */ (SELECT 'SapWhereClauseSelect', 'SELECT * from #SapWhereClauseTable ORDER BY order_number') UNION ALL (SELECT 'SapQueryTable', 'VBAK') --#ImportSapQuery /* Show the results */ (SELECT * FROM #SAPmode1) (SELECT 'SheetName' , 'SAPmode1') --#ShowReport
The following script will get the "VBELN", "ERDAT", "ERZET", "ERNAM", "NETWR", and "WAERK" columns from the "VBAK" table where the value of the VBELN field is between 0060000039 and 0060000041 and put them into a data table named "SapQueryTableExample". The query is made on the client side.
/* First, create the temporary table that holds the WHERE clause.*/ CREATE TABLE #SapWhereClauseTable (sap_select_string varchar(255), order_number int) INSERT INTO #SapWhereClauseTable SELECT 'VBELN BETWEEN ''0060000039''', 1 INSERT INTO #SapWhereClauseTable SELECT 'AND ''0060000042''', 2 /* Define that the command is executed in the client side.*/ (SELECT 'ExecuteInClientSide', 'True') UNION ALL /* Specify the data table where the data is imported into.*/ (SELECT 'DataTableName', 'SapQueryTableExample') UNION ALL (SELECT 'Append', '0') UNION ALL /* Define the SAP connection parameters.*/ (SELECT 'SapAppServerHost', '127.0.0.1') UNION ALL (SELECT 'SapSystemNumber', '10') UNION ALL (SELECT 'SapUser', 'exampleuser') UNION ALL (SELECT 'SapPW', 'examplepassword') UNION ALL (SELECT 'SapRouter', '') UNION ALL (SELECT 'SapClient', '200') UNION ALL (SELECT 'SapLanguage', 'EN') UNION ALL (SELECT 'SapPoolSize', '5') UNION ALL (SELECT 'SapPeakConnectionsLimit', '10') UNION ALL (SELECT 'SapConnectionIdleTimeout', '600') UNION ALL (SELECT 'SapFieldNames', 'VBELN,ERDAT,ERZET,ERNAM,NETWR,WAERK') UNION ALL /* Use the WHERE clause defined in the temporary table.*/ (SELECT 'SapWhereClauseSelect', 'SELECT sap_select_string from #SapWhereClauseTable ORDER BY order_number') UNION ALL (SELECT 'SapQueryTable', 'VBAK') --#ImportSapQuery /* Create an analysis.*/ (SELECT 'AnalysisType', '18') UNION ALL (SELECT 'DataTableName', 'SapQueryTableExample') UNION ALL (SELECT 'TargetTable', '#Result') --#GetAnalysis /* Show the results.*/ SELECT * FROM #Result --#ShowReport