Understanding Select Functions

Getting Started #

When editing a query, the Select section offers several functions to manipulate and refine data. These functions are grouped into the following categories:

• Aggregates – Perform calculations on a set of values, often used to summarize data.
• Text Adjustments – Clean, modify, and extract text data for better consistency and accuracy.
• Case Statements – Use conditional logic similar to an IF-THEN-ELSE structure to transform data dynamically.
• Special Functions – TAD-specific functions that are not part of standard SQL, providing unique capabilities for TAD operations.

Aggregates #

Aggregate functions help summarize data by performing calculations on multiple rows and returning a single value. These functions are useful for finding totals, averages, and other key insights in a dataset.

• AVG – Returns the average (mean) value of a numeric column.
• COUNT – Counts the total number of rows in a dataset.
• COUNT DISTINCT – Counts the number of unique values in a column.
• MAX – Returns the highest value in a column.
• MIN – Returns the lowest value in a column.
• SUM – Adds up all values in a numeric column.

These functions are essential for data analysis and reporting, helping users quickly identify trends and key metrics.

AVG #

The AVG function calculates the average (mean) value of a numeric column.

Example Scenario:

You manage employee payroll and want to calculate the average salary amount for employees in a specific company.

Example Data:

Query Example:

This query calculates the average salary amount for employees in Company 1.

SELECT AVG(SalaryAmt) [SalaryAmt] FROM PREH WHERE PRCo=1

Result:

Use Case: Used for calculating average salaries, test scores, or transaction amounts.

COUNT #

The COUNT function returns the total number of rows in a dataset.

Example Scenario:

You want to find out how many invoices exist for customers in Company 1.

Example Data:

Query Example:

SELECT COUNT(*) [TotalInvoices] FROM ARTH WHERE ARCo=1

Result:

Use Case: Useful for counting employees, customers, or transactions.

COUNT DISTINCT #

The COUNT DISTINCT function counts the number of unique values in a column.

Example Scenario:

You want to know how many unique states vendors are from.

Example Data:

Query Example:

The COUNT function counts all rows, while COUNT DISTINCT only counts unique values. This query finds the number of unique states vendors are from.

SELECT COUNT(DISTINCT State) [UniqueStates] FROM APVM WHERE VendorGroup=1

Result:

Use Case: Helps count the number of unique states vendors operate from, useful for regional analysis and compliance tracking.

MAX #

The MAX function returns the highest value in a column.

Example Scenario:

You want to find the highest check amount in a pay period to ensure that an employee’s rate was not entered incorrectly.

Example Data:

Query Example:

SELECT MAX(Earnings) [LargestCheck] FROM PRSQ WHERE PRCo=1

Result:

Use Case: Ensuring the highest check amount in a pay period is correct to detect any potential rate entry errors.

MIN #

The MIN function returns the lowest value in a column.

Example Scenario:

You want to find the minimum hourly rate from employee records to ensure compliance with wage policies.

Example Data:

Query Example:

SELECT MIN(HrlyRate) [HrlyRate] FROM PREH WHERE PRCo=1

Result:

Use Case: Identifying the lowest hourly rate in employee records to ensure compliance with wage policies.

SUM #

The SUM function adds up all values in a numeric column.

Example Scenario:

You want to summarize total billed revenue for jobs.

Example Data:

Query Example:

SELECT SUM(BilledAmt) [BilledAmt] FROM JCIP WHERE JCCo=1

Result:

Use Case: Summarizing the total billed amounts in job revenue details to track financial performance and ensure accurate billing.

Text Adjustments #

Text adjustment functions help clean, modify, and extract text data in a database. These functions are useful for formatting names, standardizing data, and improving search efficiency.

• ISNULL – Replaces NULL (missing) values with a default value.
• LEFT – Extracts a specified number of characters from the left side of a text string.
• LEN – Returns the number of characters in a text string.
• LOWER – Converts all letters in a text string to lowercase.
• LTRIM – Removes leading (left-side) spaces from a text string.
• REPLACE – Replaces occurrences of a substring within a text value.
• RIGHT – Extracts a specified number of characters from the right side of a text string.
• RTRIM – Removes trailing (right-side) spaces from a text string.
• SUBSTRING – Extracts a portion of a text string based on a specified starting position and length.
• TRIM – Removes both leading and trailing spaces from a text string.
• UPPER – Converts all letters in a text string to uppercase.

These functions help ensure data consistency and improve search accuracy by standardizing text formats.

ISNULL #

The ISNULL function replaces NULL (missing) values with a specified default value.

Example Scenario:

You have a list of customers, but some phone numbers are missing.

Example Data:

Query Example:

SELECT Customer, Name, ISNULL(Phone, 'No Number') [Phone Number] FROM ARCM WHERE CustGroup=1

Result:

Use Case: Ensures missing data is replaced with meaningful text.

LEFT #

The LEFT function returns a specified number of characters from the beginning (left side) of a text value.

Example Scenario:

You have material codes, but you only need the first 3 characters.

Example Data:

Query Example:

SELECT Category, LEFT(Material, 3) [ShortMatl] FROM HQMT WHERE MatlGroup=1

Result:

Use Case: Extracting prefixes, country codes, or area codes.

LEN #

The LEN function returns the number of characters in a text string, including spaces.

Example Scenario:

You want to check the length of usernames to ensure they meet a required length.

Example Data:

Query Example:

SELECT VPUserName, LEN(VPUserName) [Length] FROM DDUP

Result:

Use Case: Validating passwords, checking data consistency.

LOWER #

The LOWER function converts all letters in a text value to lowercase.

Example Scenario:

You want to ensure all email addresses are stored in lowercase for consistency.

Example Data:

Query Example:

SELECT LOWER(EMail) [StandardizedEmail] FROM DDUP

Result:

Use Case: Standardizing email addresses, usernames, and search terms.

LTRIM #

The LTRIM function removes any leading (left-side) spaces from a text value.

Example Scenario:

You have a dataset where names have extra spaces at the beginning.

Example Data:

Query Example:

SELECT LTRIM(FirstName) [CleanedName] FROM PREH WHERE PRCo=1

Result:

Use Case: Cleaning up messy text input.

REPLACE #

The REPLACE function finds and replaces occurrences of a substring within a text value.

Example Scenario:

You need to change all instances of “Street” to “St.” in addresses.

Example Data:

Query Example:

SELECT REPLACE(Address1, 'Street', 'St.') [UpdatedAddress] FROM ARCM WHERE CustGroup=1

Result:

Use Case: Fixing spelling errors, updating product names.

RIGHT #

The RIGHT function returns a specified number of characters from the end (right side) of a text value.

Example Scenario:

You have phone numbers and only need the last 4 digits.

Example Data:

Query Example:

SELECT RIGHT(Phone, 4) [Last4Digits] FROM APVM WHERE VendorGroup=1

Result:

Use Case: Extracting last digits of IDs, codes, or phone numbers.

RTRIM #

The RTRIM function removes any trailing (right-side) spaces from a text value.

Example Scenario:

You have a dataset where names have extra spaces at the end.

Example Data:

Query Example:

SELECT RTRIM(FirstName) [CleanedName] FROM PREH WHERE PRCo=1

Result:

Use Case: Cleaning up data before displaying it.

SUBSTRING #

The SUBSTRING function extracts a portion of a text value based on position.

Example Scenario:

You have material codes, and you need to extract the middle portion.

Example Data:

Query Example:

SELECT SUBSTRING(Material, 5, 4) [ExtractedMatl] FROM HQMT WHERE MatlGroup=1

Result:

Use Case: Extracting ID numbers, part of names, or keywords.

TRIM #

The TRIM function removes both leading and trailing spaces from a text value.

Example Scenario:

You receive messy user input where names have extra spaces.

Example Data:

Query Example:

SELECT TRIM(FirstName) [CleanedName] FROM PREH WHERE PRCo=1

Result:

Use Case: Cleaning up messy text input.

UPPER #

The UPPER function converts all text to uppercase.

Example Scenario:

You want all last names in uppercase for uniformity.

Example Data:

Query Example:

SELECT UPPER(LastName) [StandardizedName] FROM PREH WHERE PRCo=1

Result:

Use Case: Standardizing names, formatting for reports.

Case Statements #

The CASE statement in SQL allows you to apply conditional logic to your queries, similar to an IF-THEN-ELSE statement in programming.

This function is useful for:

• Categorizing data into meaningful groups.
• Transforming values based on conditions.
• Calculating values dynamically within a query.

The CASE statement evaluates conditions and returns a specific value when a condition is met. If no conditions match, a default value can be returned using ELSE.

Basic Syntax

CASE  
    WHEN condition1 THEN value1  
    WHEN condition2 THEN value2  
    ELSE default_value  
END

• The statement checks each WHEN condition one by one.
• If a condition is TRUE, it returns the corresponding THEN value.
• If none of the conditions are met, it returns the ELSE value (if provided).

Example Scenario

You have employee salaries and want to classify them as Low, Medium, or High Salary based on salary brackets.

Example Data:

Query Example:

SELECT Employee, SalaryAmt,  
       CASE  
           WHEN SalaryAmt < 40000 THEN 'Low'  
           WHEN SalaryAmt BETWEEN 40000 AND 80000 THEN 'Medium'  
           ELSE 'High'  
       END AS SalaryCategory  
FROM PREH
WHERE PRCo=1

Result:

Use Case: Used for grouping salaries, pricing tiers, and other classifications.

Special Functions #

These functions are unique to TAD and are not standard SQL functions. They provide additional functionality for formatting, automation, and specialized data processing.

• BLANK TOP – Adds a blank column at the top of the Selected Columns, useful for spacing between other columns.
• BLANK BOTTOM – Adds a blank column at the bottom of the Selected Columns, useful for spacing between other columns.
• EXCEL FORMULAS – Allows you to enter an Excel formula that will generate dynamically when data is downloaded.
• COPY RESULTS – Duplicates the data output, which is useful for creating balanced entries in GL Journal Transaction Entry and JC Cost Adjustments.

These functions enhance customization and flexibility when working with data in TAD.