Table of Contents

• Preface
  • Scope
  • Who Should Use This Document
  • Summary of Contents
• Introduction
• General Overview of Virtual DataPort
  • Creating or Defining Data
    • Defining Base Views
    • Defining Data Sources and Wrappers
    • Defining the Views of the Global Schema
      • Post-Processing
  • Executing Statements
• Language for Defining and Processing Data: VQL
  • Data Types
    • Data Types for Dates, Timestamps and Intervals
      • Rules to Build Queries that Deal with Datetime Types
        • Most Appropriate Method to Define a Datetime Value
        • Use the Appropriate Function to Obtain the Current Datetime
        • Converting a Datetime Value to Another Datetime Value
        • Data Types for Intervals
        • Arithmetic Operators for Datetime and Interval Values
      • Nanosecond Precision of Time Values
    • Internationalization
  • Statements
  • Unicode Identifiers
  • Logical Operators
  • Comparison Operators
    • Comparing Literals
    • Three-valued Logic
  • Functions for Conditions and Derived Attributes
  • Syntax Conventions
    • Syntax of Functions and Condition Values
  • Comments
• Generating Wrappers and Data Sources
  • Creating Data Sources
    • JDBC Data Sources
      • The Pool of Connections of the JDBC Data Sources
        • Parameters of the Connection Pool of a JDBC Data Source
        • Recommended Settings of the Connection Pool in Environments with a Firewall
    • ODBC Data Sources
    • Multidimensional Data Sources
      • Creating Multidimensional Data Sources with the SAP BAPI
      • Creating Multidimensional Data Sources with the Oracle
      • Creating Multidimensional Data Sources with the Generic
      • Invalidating the Metadata Cache of SAP BAPI Data Sources
    • Data Sources for SOAP Web Services
    • XML Data Sources
    • JSON Sources
    • DF Data Sources
    • Denodo Aracne Data Sources
    • Google Search Data Sources
    • LDAP Data Sources
    • BAPI Data Sources
    • Salesforce Data Sources
    • Custom Data Sources
    • Data Source Configuration Properties
      • CONTAINS Operator Configuration Properties
    • Specifying Paths in Virtual DataPort
  • Creating Wrappers
    • Execution Context and Interpolation Strings
    • Wrapper Metadata
    • JDBC Wrappers
      • Specification of a Table in the Remote Database
      • Using a SQL Statement
        • Using WHEREEXPRESSION
        • Importing an Oracle PL/SQL Stored Procedure
    • Multidimensional Databases Wrappers
    • ODBC Wrappers
    • WWW Wrappers
    • Web Services Wrappers
    • XML Wrappers
    • JSON Wrappers
    • DF Wrappers
    • Denodo Aracne Wrappers
    • Google Search Wrappers
    • LDAP Wrappers
    • BAPI Wrappers
    • Salesforce Wrappers
      • Using WHEREEXPRESSION in SOQL Queries
    • CUSTOM Wrappers
    • Wrapper Configuration Properties
  • Valid Conversions Between Types in Wrappers and VDP Types
    • Native-type Conversions of a Wrapper to Java Types
      • Type Conversion Tables for JDBC Wrappers
      • Type Conversion Table for ODBC Wrappers
      • Type Conversion Table for Web Source Wrappers
      • Type Conversion Table for Web Services Wrappers
      • Type Conversion Table for XML Wrappers
      • Type Conversion Table for Delimited File Wrappers
      • Type Conversion Table for CUSTOM Wrappers
      • Type Conversion Table for Aracne Wrappers
      • Type Conversion Table for Google Search Wrappers
    • Filters
  • QUERY WRAPPER Statements
• Creating a Base View
  • Query Capabilities: Search Methods and Wrappers
    • Query Constraints
    • Assigning Wrappers to Search Methods
    • Example of How a Search Method Is Created
  • Modifying a Base View
• Queries: SELECT Statement
  • FROM Clause
    • JOIN Operation
    • INTERSECT Operation
    • MINUS Operation
    • FLATTEN View (Flattening Data Structures)
    • Subqueries in the WHERE Clause of the Query
  • WITH Clause
  • SELECT Clause
    • Derived Attributes
  • WHERE Clause
    • Conditions with Compound Values
  • Group BY Clause
    • Use of Aggregation Functions
  • HAVING Clause
  • UNION Clause
    • Enabling the Standard SQL Union
  • ORDER BY Clause
  • OFFSET, FETCH and LIMIT
  • CONTEXT Clause
  • TRACE Clause
  • CASE Clause
• Defining a Derived View
  • Modifying a Derived View
  • Defining an Interface View
• Defining the Statistics of a View
• Materialized Tables
  • Creating Materialized Tables
    • Inserting Data into Materialized Tables
• Temporary Tables
  • Creating Temporary Tables
    • Privileges Required
• Remote Tables
  • CREATE REMOTE TABLE Command
• Inserts, Updates and Deletes Over Views
  • INSERT Statement
  • UPDATE Statement
  • DELETE Statement
  • Return Modified Rows
    • Limitations
  • Use of WITH CHECK OPTION
• Refreshing Views
  • REFRESH Statement
• Transactions in Virtual DataPort
• Stored Procedures
  • Importing a Stored Procedure
  • Use of Stored Procedures
  • Predefined Stored Procedures
    • CACHE_CONTENT
    • CATALOG_ELEMENTS
    • CATALOG_FKS
    • CATALOG_METADATA_VIEWS
    • CATALOG_PERMISSIONS
    • CATALOG_PKS
    • CATALOG_VDP_METADATA_VIEWS
    • CATALOG_VIEWS
    • CLEAN_CACHE_DATABASE
    • COLUMN_DEPENDENCIES
    • COMPACT_HADOOP_CACHE
    • COMPACT_METADATA_TABLES
    • CREATE_REMOTE_TABLE
    • DROP_REMOTE_TABLE
    • DUAL
    • GENERATE_SMART_STATS_FOR_FIELDS
    • GENERATE_SMART_STATS_FOR_FIELDS_BY_TABLENAME
    • GENERATE_STATS
    • GENERATE_STATS_FOR_FIELDS
    • GENERATE_VQL_TO_CREATE_JDBC_BASE_VIEW
    • GET_ACTIVE_LOGGERS
    • GET_ASSOCIATIONS
    • GET_CACHE_COLUMNS
    • GET_CACHE_CONFIGURATION
    • GET_CACHE_TABLE
    • GET_CATALOG_EFFECTIVE_PERMISSIONS
    • GET_CATALOG_METADATA_WS
    • GET_DELEGATED_SQLSENTENCE
    • GET_ELEMENTS
    • GET_EXPORTED_KEYS
    • GET_FOREIGN_KEYS
    • GET_JDBC_DATASOURCE_TABLES
    • GET_PRIMARY_KEYS
    • GET_PROCEDURE_COLUMNS
    • GET_SERVER_CONNECTIVITY
    • GET_SESSIONS
    • GET_SOURCE_CHANGES
    • GET_SOURCE_COLUMNS
    • GET_SOURCE_TABLE
    • GET_TYPE_ATTRIBUTES
    • GET_USER_DEFINED_TYPES
    • GET_VIEW_COLUMNS
    • GET_VIEW_INDEXES
    • GET_VIEW_STATISTICS
    • GET_VIEWS
    • LIST_JDBC_DATASOURCE_TABLES
    • LOGCONTROLLER
    • MIGRATE_DATE_TYPES
    • PING_DATA_SOURCE
    • SOURCE_CHANGES
    • VIEW_DEPENDENCIES
    • WAIT
    • WEBCONTAINER_ELEMENTS
    • WEBCONTAINER_ELEMENT_STATUS
    • WRITELOGERROR
    • WRITELOGINFO
• Defining Other Elements of the Catalog
  • Creating Folders
  • Defining a Data Type
  • Defining a Map
  • Defining JAR Extensions
  • Defining JMS Listeners
• Creating Databases, Users, Roles and Access Privileges
  • Creating and Modifying Virtual DataPort Databases
  • Managing Users
    • Modifying and Deleting Users
      • Modifying User Data
    • Connecting to Another Database
    • Modifying the Privileges of a User
      • Granting Privileges by Databases
      • Granting Privileges to a User/Role
    • Managing User Roles
  • Ownership of Elements
• Describing Catalog Elements
  • Exporting Metadata
  • Importing Metadata
• Listing Elements in the Catalog
• Removing Elements from the Catalog
• Publication of Web Services
  • Creating SOAP Web Services
  • Modifying SOAP Web Services
  • Creating REST Web Services
  • Modifying REST Web Services
  • Connection Parameters
  • Web Services Authentication
    • Basic and Digest
    • OAuth 2.0 and OpenID
    • SAML
    • VDP
    • WSS
  • Embedded Web Container Management
  • Deploying and Exporting SOAP and REST Web Services
• Publication of Widgets
  • Create New Widgets
  • Export a Widget
  • Deployment and Export of Auxiliary Web Services
• RESTful Architecture
  • Associations
  • Navigational Queries
• Help Command
• Version Control Systems Integration Commands
  • Statements to Work with Centralized Version Control Systems
    • VCSCOMMIT
    • VCSUPDATE
    • VCSCONTENTS
    • VCSSHOW
    • REVISIONS
  • Statements to Work with GIT
    • DVCSCOMMIT
    • DVCSPUSH
    • DVCSPULL
    • DVCSREVERT
  • Commands for Environment Management
  • Other Version Control Commands
    • VCSDISCARD
• Resource Manager
  • Managing the Plans of the Resource Manager
  • Managing the Rules of the Resource Manager
• Advanced Characteristics
  • Management of Compound Values
    • Processing of Compound Types: Example
  • Changing Settings of Virtual DataPort and the Web Container
  • Dynamic Choice of Join Strategy
  • Using the Cache
    • Cache Invalidation
    • Displaying the Contents of the Cache
  • Configuring Swapping Policies
  • Managing Internationalization Configurations
    • Replacing an I18n Map
    • Deleting an I18n Map
  • Execution Context of a Query and Interpolation Strings
  • Adding Variables to Selection Conditions (GETVAR and SETVAR)
• Appendix
  • Syntax of Condition Functions
    • Arithmetic Functions
      • ABS
      • ACOS
      • ASIN
      • ATAN
      • ATAN2
      • CEIL
      • COS
      • COT
      • DEGREES
      • DIV
      • EXP
      • FLOOR
      • LN
      • LOG
      • MAX
      • MIN
      • MOD
      • MULT
      • PI
      • POWER
      • RADIANS
      • RAND
      • ROUND
      • SIGN
      • SIN
      • SQRT
      • SUBTRACT
      • SUM
      • TAN
      • TRUNC
    • Text Processing Functions
      • ASCII
      • CHAR
      • CONCAT
      • INSTR
      • LEN
      • LOWER
      • LTRIM
      • MAX
      • MIN
      • POSITION
      • REGEXP
      • REMOVEACCENTS
      • REPEAT
      • REPLACE
      • REPLACEMAP
      • RTRIM
      • SIMILARITY
      • SPLIT
      • SUBSTRING / SUBSTR
      • TEXTCONSTANT
      • TRIM
      • UPPER
    • Datetime Processing Functions
      • ADDDAY
      • ADDHOUR
      • ADDMINUTE
      • ADDMONTH
      • ADDSECOND
      • ADDWEEK
      • ADDYEAR
      • AT TIME ZONE
      • CONVERT_TIMEZONE
      • CURRENT_DATE
      • CURRENT_TIMESTAMP
      • EXTRACT
      • FIRSTDAYOFMONTH
      • FIRSTDAYOFWEEK
      • FORMATDATE
      • GETDAY
      • GETDAYOFWEEK
      • GETDAYOFYEAR
      • GETDAYSBETWEEN
      • GETHOUR
      • GETMILLISECOND
      • GETMINUTE
      • GETMONTH
      • GETMONTHSBETWEEN
      • GETQUARTER
      • GETSECOND
      • GETTIMEINMILLIS
      • GETWEEK
      • GETYEAR
      • LASTDAYOFMONTH
      • LASTDAYOFWEEK
      • LOCALTIMESTAMP
      • MAX
      • MIN
      • NEXTWEEKDAY
      • NOW
      • PREVIOUSWEEKDAY
      • SUBTRACT
      • TO_DATE
      • TO_LOCALDATE
      • TO_TIME
      • TO_TIMESTAMP
      • TO_TIMESTAMPTZ
      • TRUNC
    • XML Processing Functions
      • XMLQUERY
      • XPATH
      • XSLT
      • Converting XML Data into Virtual DataPort Compound Types
    • Type Conversion Functions
      • ARRAY_TO_STRING
      • CAST
      • CREATETYPEFROMXML
      • REGISTER
    • Aggregation Functions
      • AVG
      • COUNT
      • FIRST
      • GROUP_CONCAT
      • LAST
      • LIST
      • MAX
      • MEDIAN
      • MIN
      • NEST
      • STDEV
      • STDEVP
      • SUM
      • VAR
      • VARP
    • Analytic Functions (Window Functions)
      • AVG
      • COUNT
      • CUME_DIST
      • DENSE_RANK
      • FIRST_VALUE
      • LAG
      • LAST_VALUE
      • LEAD
      • LISTAGG
      • MAX
      • MIN
      • NTILE
      • PERCENTILE_CONT
      • PERCENTILE_DISC
      • PERCENT_RANK
      • RANK
      • ROW_NUMBER
      • SUM
      • Workaround to Execute Analytic Functions
    • Other Functions
      • COALESCE
      • CONTEXTUALSUMMARY
      • GETSESSION
      • HASH
      • IS_PROJECTED_FIELD
      • MAP
      • NULLIF
      • ROWNUM
  • Syntax of Search Expressions for the Contains Operator
    • Exact Terms and Phrases
    • Term Modifiers
      • Search Wildcards
      • Fuzzy Searches
      • Proximity Searches
      • Range Searches
      • Boosting the Relevance Level of a Term
    • Boolean Operators
    • Groups
    • Escaping Special Characters
  • Support for the Contains Operator of Each Source Type
  • CASE Clause Examples
  • Date and Time Pattern Strings
  • Web Services Created with Previous Versions of the Denodo Platform