JDBC Driver for Workday

Build 24.0.9062

CreateWQLSchema

Creates a custom schema file that executes a specific WQL query.

Basic Schemas

The 本製品 allows you to create tables from WQL queries using the CreateWQLSchema stored procedure. With this stored procedure, you can take the output from the Convert Report to WQL task in Workday and pass it to the 本製品:

EXEC CreateWQLSchema TableName = 'FirstTenReports', WQL = 'SELECT reportName FROM myCustomReports LIMIT 10'

The table can be queried once the stored procedure completes. By default the table is saved to a file and can be queried on other connections with the same Location setting.

SELECT * FROM FirstTenReports

Note that tables created this way do not support any server-side query optimizations. Operations that would normally be executed by Workday, such as GROUP BY, ORDER BY, and aggregations, are instead executed within the 本製品. This limits their performance compared to querying WQL sources through SQL.

Parameterized Schemas

Workday reports support filtering data sources using dynamic values, but Workday does not support converting these reports directly to WQL. For example, if you create a report that limits the createdDate field to the last month, Convert Report to WQL will output WQL that contains the computed date. The results from this query will not change if you execute it again next month:

SELECT reportName FROM myCustomReports WHERE createdDate > '2024-06-26'

The 本製品 supports WQL parameters which lift this restriction. When you create the table using a parameter, the 本製品 exposes additional extra columns that you can use to provide dynamic values. For example:

EXEC CreateWQLSchema TableName = 'ReportsCreatedDuring', 
                     WQL = 'SELECT reportName FROM myCustomReports WHERE createdDate >= <<from_date>> AND createdDate <= <<to_date>>',
                     Parameters = 'from_date:DATE,to_date:DATE';

SELECT * FROM ReportsCreatedSince WHERE from_date_Prompt = DATEADD('month', -1, GETDATE()) AND to_date_Prompt = GETDATE()

The parameters are given as a comma-separated list, where each entry is the parameter name and parameter type separated by a colon. The 本製品 supports the following parameter types. Make sure that the parameter type is the same as the type of the relevant field. The 本製品 formats the value from the WHERE clause as needed (for example, dates are converted to the YYYY-mm-dd format)

  • Instance (includes single-instance and self-referencing instances)
  • Text
  • Boolean
  • Time
  • Date
  • DateTimeZone
  • Numeric

Input

Name Type Accepts Output Streams Description
TableName String False The name for the new table.
WQL String False The WQL query that the schema executes.
Parameters String False A list of parameter names and types used by the query. Refer to the documentation for more information.
WriteToFile String False Whether to write to a file or not. If false, this writes to the FileStream (if provided) or the FileData output.
FileStream String True OutputStream to write the created schema. Only used if WriteToFile set to false

Result Set Columns

Name Type Description
Result String Whether or not the schema was created successfully.
FileData String The generated schema encoded in base64. Only returned if WriteToFile set to false and FileStream is not set.

Copyright (c) 2024 CData Software, Inc. - All rights reserved.
Build 24.0.9062