Skip to main content
Documentation
Documentation
Administrator Guide
Last Updated: 2023-06-23
Concept: SQL and Subquery Columns

Concept: SQL and Subquery Columns

You can drag-and-drop these custom column types in a Workday data source from the
Data Components
 pane:
  • SQL columns.
  • Subquery columns.
Click
view online help
 in the SQL Expression editor when creating an SQL column or Subquery column to view the SQL Expression Reference.

SQL Columns

Use an SQL column to make new values based on the criteria of other columns. SQL columns act like an SQL
WHERE
 clause, but they don't allow a
SELECT
 statement. Use an SQL column to:
  • Reformat data.
  • Concatenate values with a delimiter.
  • Return a
    SUBSTRING
     within a column value.
  • Write
    CASE
     statements that function as IF statements.
  • Replace a character string with another character string.
  • Hardcode values.
CASE Statements
A
CASE
 statement provides conditional logic that starts with a condition and a resulting value. When the first condition isn't met, the statement proceeds to the next condition and resulting value. A final result returns if none of the conditions are met.
Example:
CASE
WHEN Condition 1
	THEN Result 1 
WHEN Condition 2 
	THEN Result 2
ELSE Final Result
END
Guidelines for SQL Columns
  • Keep SQL columns as simple as possible in case other users need to maintain them.
  • Minimize hardcoding. If you can't avoid hardcoding, use the Workday ID (WID) or Reference ID when referencing a value.
  • Use consistently named SQL columns to speed up building metadata and data loaders.
  • Use the custom SQL command
    TO_ACCOUNT_CODE
     to replace spaces and special characters with underscores.
  • Use the
    TIMESTAMP
     column type for
    TIME
     related fields. Adaptive Planning automatically uses
    TIMESTAMP
     for date columns in the format
    MM/DD/YYYY HH:MM:SS AM
     even when another format gets specified.
  • Concatenate with the same delimiter for all of your data sources.

Subquery Columns

Subquery columns act like a
JOIN
 condition to another table. If the criteria is met on the other table, the
JOIN
 returns a true/false Boolean. Build the
JOIN
 statement using the Primary table and a selected Related Table. You can specify if a record
EXISTS
 or
DOES NOT EXIST
. You can also use subquery columns with an integer result. To generate numerical results, include
SUM
,
COUNT
,
MIN
, or
MAX
 in your join expression.
Subquery columns help when looking up a value in another table. Use them to:
  • Verify that a value exists in another table.
  • Filter that table for a true/false result.
  • Count the number of duplicate values in that table.
  • Find the max costs for a cost center.