WQL Query Support

The connector supports writing queries in the Workday Query language (WQL) syntax. For detailed information about WQL, see the insightsoftware Simba Workday JDBC Data Connector Reference Guide.

Splitting the WQL schema table column into sub columns

If the datasource field is the single instance, self referencing instance, or currency type, then it is split into sub fields (sub columns) to extract data of each nested field separately. The multi-instance type field is shown as a single cell value with the WVARCHAR type.

For example, if the datasource field called absoluteDebitOrCreditAmount has the following currency type value: {“currency”: “USD”, “value”: 299.0}.

Then it gets split into two columns:

absoluteDebitOrCreditAmount
The cell value for this column is 299.0.
absoluteDebitOrCreditAmount_currency
The cell value for this column is USD.

Note:

You can use the fields split by the connector in the GROUP BY and HAVING clauses. This process takes longer to retrieve results, as the connector first retrieves all the records from the datasource and then performs the aggregation.
You cannot use the fields split by the connector in any other clauses, for example the WHERE, WHERE ON, or PARAMETERS clauses. If used, the connector returns a WQL error. The purpose is to only segregate the JSON type columns in Workday.

Splitting the multi-instance fields

If the data includes multi-instance fields (array types), it is flattened and displayed in the result set. For a single data row, the connector calculates the number of instances for each multi-instance column and performs a Cartesian product of these fields with the original record.

For example, following is a JSON API response from Workday Server with multi-instance fields like “Contacts” and “Addresses”:

{
   "data": [
   {
          "id": 102,
	  "name": "amit",
      "Addresses": [
	     {
		     "addressLine1": "D 401"
			 "addressLine2": "Sahaj Apartment"
			 "addressLine3": "Pune, 380003"
			 "isPermanentAddress": false
		 },
		 {
		     "addressLine1": "A 301"
			 "addressLine2": "Nancy Colony"
			 "addressLine3": "Mumbai, 390023"
			 "isPermanentAddress": true
		 }
	  ],
	  "Contacts": [
	      {
		      "name": "Kalpesh D"
			  "contactNo": "8137409867"
		  }
	    ]
	}
  ]
}

In the above example, the “Addresses” field contains 2 instances and the “Contacts” field contains 1 instance. Performing a Cartesian product of these fields with the original record (i.e., 2 * 1 * 1) results in 2 records. The transformed result from the connector is as the following:

{
   "data": [
        {
	          "id": 102,
		  "name": "amit",
		  "Addresses_addressLine1": "D 401",
		  "Addresses_addressLine2" : "Sahaj Apartment",
		  "Addresses_addressLine3": "Pune, 380003",
		  "Addresses_isPermanentAddress": false,
		  "Contacts_name": "Kalpesh D"
		  "Contacts_contactNo": "8137409867"
	  },
	  {
	          "id": 102,
		  "name": "amit",
		  "Addresses_addressLine1": "A 301",
		  "Addresses_addressLine2" : "Nancy Colony",
		  "Addresses_addressLine3": "Mumbai, 390023",
		  "Addresses_isPermanentAddress": true,
		  "Contacts_name": "Kalpesh D"
		  "Contacts_contactNo": "8137409867"
	  }
     ]
}	