LDW 2.3.8 released: New SPLIT_PART string function

A common need when working with strings is to split them by a given separator. This was already possible in previous versions using TEXTTABLE, but we now made it much more convenient and also improved performance by enabling pushdown. Here is how to use it:

SELECT SPLIT_PART('A,BB,C,DDD', ',', 3);;
-- returns: A

SELECT SPLIT_PART('A,BB,C, DDD', ',', 4);;
-- returns: DDD

It can of course not only be used with string literals, but also reference columns:

SELECT SPLIT_PART(SalesOrderID, ',', 1)
FROM PostgreSQL.salesorderdetail;;

If the delimiter is not found in a string, index 1 will return the original string value.

For Snowflake, Redshift, Vertica and PostgreSQL the function call will be pushed down, for other data sources it will be executed in-memory by Data Virtuality LDW.

In the Web Business Data Shop (the web interface for business users), we extended the set of icons to open a data set by now also including connection information for R, Python and HTTP requests:

DV Web

• DVCORE-6731 (Improvement) Web Business Data Shop: Add snippets for R, Python and HTTP request

Connectors

• SQL-262 (New Feature) YouTube Reporting connector: Create YouTube Reporting connector
• SQL-261 (New Feature) YouTube Analytics connector: Create YouTube Analytics connector
• SQL-235 (New Feature) Lead Alliance connector: Create a Lead Alliance connector
• SQL-6 (Improvement) Stripe connector: Add missing properties
• SQL-263: Microsoft Excel Online connector: An issue with multiple createdDateTime columns
• SQL-17: DoubleClick Campaign Manager Connector: Report_NewParsing procedure fails for generating some reports
• DVCORE-6869: SQL Server: Comparison and concatenation fail on UNIQUEIDENTIFIER data type column

Backend

• DVCORE-5511 (Improvement): Implement the SPLIT_PART function
• SQL-253 (Improvement): Modify the internal procedure for better metadata matching during replications
• SQL-10 (Improvement): Improve UTILS.tryCast with automatic integer/long parsing and custom date/time formats
• DVCORE-6932: Small value of FetchSize JDBC driver property may cause an assertion error on results fetching
• DVCORE-6914: Triggered INTERVAL and CRON schedules create an EDIT entry in SYSLOG.ScheduleHistory
• DVCORE-6909: Wrong rewriting on full outer join
• DVCORE-6844: Nested LEFT JOIN returns wrong results if combined with WHERE and IS NULL
• DVCORE-6814: The OFFSET 1 ROW clause returns 0 rows in complex queries
• DVCORE-6683: The TextTable removes a quoted character when it matches the delimiter
• DVCORE-6352: HAVING in combination with LEFT JOIN of two XMLTABLE sub-queries returns incorrect results
• DVCORE-6346: Reusing the same alias in dependent objects results in query planner error
• DVCORE-6174: Running a job creates an edit entry in SYSLOG.SchedulerJobHistory