ADO.NET Provider for SASDataSets

Build 21.0.7940

JOIN Queries

The CData ADO.NET Provider for SASDataSets supports joins of a nested array with its parent document and joins of multiple collections.

Joining Nested Structures

The provider expects the left part of the join is the array document you want to flatten vertically. Disable SupportEnhancedSQL to join nested SAS Data Sets documents. This type of query is supported through the SAS Data Sets API.

For example, consider the following query from SASDataSets's restaurants collection:

SELECT [restaurants].[restaurant_id], [restaurants].name, [restaurants.grades].* 
FROM [restaurants.grades]
JOIN [restaurants] 
WHERE [restaurants].name = 'Morris Park Bake Shop'

Joining Multiple Collections

You can join multiple collections just like you would join tables in a relational database. Set SupportEnhancedSQL to True to execute these types of joins. The following examples use the restaurants and zips collections available in the SASDataSets documentation.

The query below returns the restaurant records that exist, if any, for each ZIP code:

SELECT z.city, r.name, r.borough, r.cuisine, r.[address.zipcode]
FROM zips z
LEFT JOIN restaurants r
ON r.[address.zipcode] = z._id

The query below returns records from both tables that match the join condition:

SELECT z.city, r.name, r.borough, r.cuisine, r.[address.zipcode]
FROM restaurants r
INNER JOIN zips z
ON r.[address.zipcode] = z._id

Copyright (c) 2021 CData Software, Inc. - All rights reserved.
Build 21.0.7940