appRules
  • Welcome to appRules®
  • PLATFORM
    • appRules® Concepts & Features
    • Platform Overview
    • Platform Features
      • Sourced Values
        • Overview
        • Sourced Values List
      • Actions
        • Overview
        • Action Categories
          • Actions-DataSource
            • Accounting
            • AIMachineLearning
            • Analytics
            • ApplicationPlatforms
            • BigData
            • CalendarsAndContactMgrs
            • CloudStorage
            • Collaboration
            • CRM
            • CustomerService
            • Databases
            • DataWarehouse
            • ECommercePlatforms
            • EmailSystems
            • ERP
            • ExpenseManagement
            • Files
            • Financial
            • Marketing
            • Miscelleanous
            • NoSQL
            • PaymentProcessing
            • PLM
            • ProjectManagement
            • Search
            • Services
            • ShippingAndMailing
            • Social
            • Spreadsheets
          • Actions-Execution
          • Actions-Fields-Field
          • Actions-Fields-Library-Financial
          • Actions-Fields-Library-Math
          • Actions-Utility-Automation
          • Actions-Utility-FileSystem
      • Conditions
        • Overview
        • Condition Categories
          • Conditions-Cache
          • Conditions-Custom
          • Conditions-Fields
          • Conditions-FileSystem
          • Conditions-Records
          • Conditions-Results
          • Conditions-Workflow
      • Workflow Activities
        • Control Flow Activities
        • Base Activities
        • Dialog Activities
        • Base Data Activities
      • System Settings
        • Runtime Settings
        • Users
        • Data Sources
          • Overview
          • Configuring Connection Settings
          • Generating Metadata
          • Connection Settings
            • Accounting
              • Exact Online
              • Raisers Edge NXT
              • Xero
              • Quickbooks
              • Quickbooks Online
              • Quickbooks POS
              • Freshbooks
              • Sage Business Cloud Accounting
              • Sage Intacct
              • Sage50 UK
              • Reckon
            • Application Platforms
              • Alfresco Platform
              • Kintone Platform
              • Quickbase Platform
              • Veeva Vault
            • AI & Machine Learning
              • DataRobot
              • Salesforce Einstein
              • Splunk
            • Analytics
              • Google Analytics
              • Adobe Analytics
                • Adobe Custom oAuth App
              • Azure Analysis Services
              • SAS
              • Youtube Analytics
            • Big Data
              • Apache HBase
              • Apache Hive
              • Apache HDFS
              • Apache Spark
              • Greenplum
              • HPCC LexisNexis
            • Calendar & Contact Managers
              • Google Calendar
              • Google Contacts
            • Cloud Storage
              • Azure Table Storage
              • Amazon S3
              • IBM Cloud Object Storage
              • Wasabi
            • Collaboration
              • Airtable
              • Asana
              • Box
              • Dropbox
              • Exchange
              • Google Drive
              • Microsoft Teams
              • Office365
              • Microsoft OneDrive
              • Microsoft OneNote
              • Salesforce Chatter
              • SharePoint
              • Slack
              • Smartsheet
            • CRM
              • Act! CRM
              • Bullhorn CRM
              • Microsoft Dynamics 365 Sales
              • Microsoft Dynamics CRM
              • Highrise
              • Oracle Sales Cloud
              • Salesforce
              • SAP Hybris C4C
              • Sugar CRM
              • Suite CRM
              • Veeva CRM
              • Zoho CRM
            • Customer Service & Support
              • ServiceNow
              • Zendesk
              • Jira
            • Data Warehouse
              • Azure Data Lake Storage Gen1
              • Azure Data Lake Storage Gen2
              • Google BigQuery
              • Redshift
              • Databricks
              • Snowflake
              • Azure Synapse
            • E-Commerce Platforms
              • Amazon MarketPlace
              • Magento
              • Shopify
              • Woocommerce
            • Electronic Signature
              • Docusign
            • Email
              • Gmail
              • Email
            • ERP
              • NetSuite
              • Odoo
              • Acumatica
              • Dynamics NAV
              • Dynamics GP
              • Microsoft Dynamics 365 FinOp
              • Microsoft Dynamics365 Business Central
                • Business Central Endpoints
              • SAP Business One
              • SAP ERP
            • Expense Management
              • SAP Concur
            • Files
              • CSV Database
              • Fixed Length File
              • Delimited File
            • Marketing
              • Bing Ads
              • Marketo
              • MailChimp
              • HubSpot
              • Eloqua
              • Salesforce Marketing Cloud
            • NoSQL
              • Cassandra
                • Cassandra Advanced Settings
              • Azure CosmosDB
              • DynamoDB
              • Couchbase
              • MongoDB
              • IBM Cloudant
              • Redis
            • Payment Processing
              • Authorize.Net
              • Square
              • Paypal
              • Stripe
            • Product Lifecycle Management (PLM)
              • Propel
            • Relational Databases
              • appRules Embedded Database
              • Firebird
              • IBM DB2
              • IBM Informix
              • Microsoft Access
              • MySQL
              • MariaDB
              • Oracle
              • PostgreSQL
              • Microsoft SQL Server
              • Microsoft SQL Server CE
              • SQL Azure
              • SQLlite
              • Vertica
            • Search
              • ElasticSearch
              • Bing
              • Google Search
            • Shipping and Mailing
              • Fedex
              • UPS
              • USPS
            • Social Networks
              • Linkedin
              • Facebook
              • Instagram
              • Twitter
            • Services
              • REST
              • RSS
              • XML
                • Connecting to XML Datasources
                • Parsing Hierarchical Data
                • Raw Data
              • Active Directory
              • Amazon Athena
              • Microsoft Dataverse
              • JSON
              • LDAP
              • GraphQL
            • Spreadsheets
              • Excel
              • Excel Services
              • Excel Online
              • GoogleSheets
          • SQL Compliance
            • Accounting
              • Exact Online
              • Raisers Edge NXT
              • Xero
              • Quickbooks
              • Quickbooks Online
              • QuickBooks POS
              • Freshbooks
              • Reckon
              • Sage Intacct
              • Sage Business Cloud Accounting
              • Sage50 UK
            • AI & Machine Learning
              • Salesforce Einstein
              • DataRobot
              • Splunk
            • Analytics
              • Google Analytics
              • Youtube Analytics
              • Azure Analysis Services
              • Adobe Analytics
              • SAS
            • Application Platforms
              • Alfresco Platform
              • Kintone Platform
              • Quick Base Platform
              • Veeva Vault
            • Big Data
              • Apache HBase
              • Apache HDFS
              • Apache Hive
              • Apache Spark
              • Greenplum
              • HPCC Systems
            • Calendar & Contact Managers
              • Google Calendar
              • Google Contacts
            • Cloud Storage
              • Azure Table Storage
              • Amazon S3
              • Wasabi
              • IBM Cloud Object Storage
            • Collaboration
              • Airtable
              • Asana
              • Box
              • Dropbox
              • Google Drive
              • Microsoft Teams
              • Microsoft OneDrive
              • Microsoft OneNote
              • Microsoft Exchange
              • Office365
              • Salesforce Chatter
              • SharePoint
              • Slack
              • Smartsheet
            • CRM
              • Act! CRM
              • Bullhorn CRM
              • Highrise
              • Microsoft Dynamics CRM
              • Microsoft Dynamics 365 Sales
              • Oracle Sales Cloud
              • Salesforce
              • SAP Hybris C4C
              • Sugar CRM
              • Veeva CRM
              • Zoho CRM
            • Customer Service & Support
              • ServiceNow
              • Zendesk
              • Jira
            • E-commerce Platforms
              • Woocommerce
              • Magento
              • Shopify
              • Amazon Marketplace
            • DataWarehouse
              • Azure Data Lake Storage Gen1
              • Azure Data Lake Storage Gen2
              • Azure Synapse
              • Databricks
              • Google BigQuery
              • Redshift
              • Snowflake
            • Email
              • Gmail
            • ERP
              • Acumatica
              • NetSuiteSQL
              • NetSuiteTalk
              • Odoo
              • Dynamics NAV
              • Dynamics GP
              • Microsoft Dynamics 365 FinOp
              • Microsoft Dynamics365 Business Central
              • SAP Business One
              • SAP NetWeaver
              • Workday
            • Expense Management
              • SAP Concur
            • Files
              • CSV Database
            • Marketing
              • Bing Ads
              • Google Ads
              • HubSpot
              • MailChimp
              • Marketo
              • Oracle Eloqua
              • Salesforce Marketing Cloud
              • Survey Monkey
              • Youtube 2020
            • NoSQL
              • Cassandra
              • Azure CosmosDB
              • Amazon SimpleDB
              • Amazon DynamoDB
              • Couchbase
              • MongoDB
              • IBM Cloudant
              • Redis
            • Payment Processing
              • Authorize.Net
              • Paypal
              • Stripe
              • Square
            • Product Lifecycle Management (PLM)
              • Propel
            • Project Management
              • Basecamp
              • Microsoft Project
            • Search
              • ElasticSearch
              • Bing
              • Google Search
            • Shipping and Mailing
              • FedEx
              • UPS
              • USPS
            • Spreadsheets
              • Excel Online
              • Excel
              • Excel Services
              • GoogleSheets
            • Social Networks
              • Facebook
              • Instagram
              • Linkedin
              • Twitter
            • Services
              • Active Directory
              • Amazon Athena
              • JSON
              • LDAP
              • Microsoft Dataverse
              • OData
              • REST
              • RSS
              • XML
        • Projects
          • Overview
          • Project Settings
      • Utilities
        • DataSource Browser
        • Job Monitor
        • Logs/Statistics
        • CheckIn/CheckOut
        • Master Admin Options
      • Creating & Running Projects
        • Creating a New Project
        • Orchestrating Workflows with Designer
          • Designer Home Page Overview
          • Using the appRules Designer
        • Reading Data
        • Mapping Data
        • Writing Data
        • Project Validation
        • Running Projects
          • Running from the Home Page
          • Using the Web API
          • Using the Scheduler
        • Monitoring Jobs
        • Logging
      • Flow Data Migration
        • Overview
        • Basic Flow Data Migrations
        • Bulk Loader Flow Data Migrations
    • Self-Hosting
      • Software Installation
        • Installation Prerequisites
        • Running the Installer
        • HostService Settings
        • IIS Settings
        • Web API Settings
        • Scheduler
        • Creating a New Project Database
      • Application Configuration
      • appRules Embedded Database DBA Tool
  • PLATFORM PRODUCTS
    • appRules Data Migration Engine
      • Overview
      • Flow Data Migration
      • The Migrate Data Activity
        • Overview
        • Initializing the Migrate Data Activity
        • Target Entities Collection
          • Overview
          • Toolbar
          • Target Entity Properties
        • Basic Data Migration
        • Matching Schema Migration
        • One-To-Many Migration
        • Entities to Csv Migration
        • Data Replication
        • Data Synchronization
        • Bidirectional Data Synchronization
      • Field Mapping & Transformation
      • Reviewing the Project Settings
      • Validations, Error Handling & Notifications
      • Job Execution & Monitoring
      • Evaluating Data Migration Job Results
    • appRules Integration & Automation Engine
      • Overview
      • Flow Data Migration
      • Process Automation, Business Rules & Decision Support
        • Overview
        • Control Flow Activities
          • If
          • While
        • Conditions & Actions
          • Overview
          • Conditions
          • Actions
        • Evaluating Conditions
        • Performing Actions
        • Decision Tables
        • Executing Rule Sets
    • appRules Enterprise 360
      • Overview
      • Data Migration
      • Integration & Automation
  • Samples
    • Samples Overview
    • Sample Projects
      • Data Migration, Replication & Synchronization
        • Replicate Dynamics365 In Snowflake
        • Migrate Parent/Child Records Using Target / Bulk Loader
        • Export SQL Table to CSV Using Flow
        • One-to-Many Data Migration
        • Batch Data Source Entities to Files
      • Integration & Automation
        • Decision Table - Customer Discount
        • Iterate Deleted Salesforce Records
        • Use CSV Folder As SQL Database
        • Conditional Actions Execution
        • Round Robin Lead Assignment
      • Snippets
        • Snippet - Arguments, Variables, Dynamic Values & Sourced Values
        • Snippet - Counters & Running Totals
        • Snippet - Executing Financial Functions
        • Snippet - Infinite Loop
        • Using Composite Keys
      • Miscellaneous
        • Custom Queries
        • Parent-Child: Child Project
        • Parent-Child: Parent Project
        • Scheduled System Maintenance
        • Target Activity Errors & Logs
        • Task Parallelism
  • APPENDIX
    • Miscellaneous
      • Common Connection Properties
      • Troubleshooting a Connection
      • Dynamics365 - Connection Settings
      • Dynamics365 App Registration
      • Getting the oAuth Access and Refresh Tokens
      • Editing Windows Environment Variables
      • PickLists
      • Creating a Custom Azure oAuth App
      • Using Kerberos
      • Stored Procedures
    • FAQs
Powered by GitBook
On this page
  • SELECT Statements
  • SELECT Syntax
  • Aggregate Functions
  • JOIN Queries
  • Date Literal Functions
  • Projection Functions
  • Predicate Functions
Export as PDF
  1. PLATFORM
  2. Platform Features
  3. System Settings
  4. Data Sources
  5. SQL Compliance
  6. CRM

Salesforce

SELECT Statements

A SELECT statement can consist of the following basic clauses.

  • SELECT

  • INTO

  • FROM

  • JOIN

  • WHERE

  • GROUP BY

  • HAVING

  • UNION

  • ORDER BY

  • LIMIT

SELECT Syntax

The following syntax diagram outlines the syntax supported by the SQL engine of the provider:

SELECT {

[ TOP <numeric_literal> | DISTINCT ]

{

*

| {

<expression> [ [ AS ] <column_reference> ]

| { <table_name> | <correlation_name> } .*

} [ , ... ]

}

[ INTO csv:// [ filename= ] <file_path> [ ;delimiter=tab ] ]

{

FROM <table_reference> [ [ AS ] <identifier> ]

} [ , ... ]

[ [

INNER | { { LEFT | RIGHT | FULL } [ OUTER ] }

] JOIN <table_reference> [ ON <search_condition> ] [ [ AS ] <identifier> ]

] [ ... ]

[ WHERE <search_condition> ]

[ GROUP BY <column_reference> [ , ... ]

[ HAVING <search_condition> ]

[ UNION [ ALL ] <select_statement> ]

[

ORDER BY

<column_reference> [ ASC | DESC ] [ NULLS FIRST | NULLS LAST ]

]

[

LIMIT <expression>

[

{ OFFSET | , }

<expression>

]

]

} | SCOPE_IDENTITY()

<expression> ::=

| <column_reference>

| @ <parameter>

| ?

| COUNT( * | { [ DISTINCT ] <expression> } )

| { AVG | MAX | MIN | SUM | COUNT } ( <expression> )

| NULLIF ( <expression> , <expression> )

| COALESCE ( <expression> , ... )

| CASE <expression>

WHEN { <expression> | <search_condition> } THEN { <expression> | NULL } [ ... ]

[ ELSE { <expression> | NULL } ]

END

| <literal>

| <sql_function>

<search_condition> ::=

{

<expression> { = | > | < | >= | <= | <> | != | LIKE | NOT LIKE | IN | NOT IN | IS NULL | IS NOT NULL | AND | OR | CONTAINS | BETWEEN } [ <expression> ]

} [ { AND | OR } ... ]

Examples

  1. Return all columns:

    SELECT * FROM Account

  2. Rename a column:

    SELECT [Name] AS MY_Name FROM Account

  3. Cast a column's data as a different data type:

    SELECT CAST(AnnualRevenue AS VARCHAR) AS Str_AnnualRevenue FROM Account

  4. Search data:

    SELECT * FROM Account WHERE Industry = 'Floppy Disks'

  5. Return the number of items matching the query criteria:

    SELECT COUNT(*) AS MyCount FROM Account

  6. Return the number of unique items matching the query criteria:

    SELECT COUNT(DISTINCT Name) FROM Account

  7. Return the unique items matching the query criteria:

    SELECT DISTINCT Name FROM Account

  8. Summarize data:

    SELECT Name, MAX(AnnualRevenue) FROM Account GROUP BY Name

    See Aggregate Functions below for details.

  9. Retrieve data from multiple tables.

    SELECT Customers.ContactName, Orders.OrderDate FROM Customers, Orders WHERE Customers.CustomerId=Orders.CustomerId

    See Join Queries section below for details.

  10. Sort a result set in ascending order:

    SELECT BillingState, Name FROM Account ORDER BY Name ASC

  11. Restrict a result set to the specified number of rows:

    SELECT BillingState, Name FROM Account LIMIT 10

  12. Parameterize a query to pass in inputs at execution time. This enables you to create prepared statements and mitigate SQL injection attacks.

    SELECT * FROM Account WHERE Industry = @param

Aggregate Functions

COUNT

Returns the number of rows matching the query criteria.

SELECT COUNT(*) FROM Account WHERE Industry = 'Floppy Disks'

COUNT(DISTINCT)

Returns the number of distinct, non-null field values matching the query criteria.

SELECT COUNT(DISTINCT BillingState) AS DistinctValues FROM Account WHERE Industry = 'Floppy Disks'

AVG

Returns the average of the column values.

SELECT Name, AVG(AnnualRevenue) FROM Account WHERE Industry = 'Floppy Disks' GROUP BY Name

MIN

Returns the minimum column value.

SELECT MIN(AnnualRevenue), Name FROM Account WHERE Industry = 'Floppy Disks' GROUP BY Name

MAX

Returns the maximum column value.

SELECT Name, MAX(AnnualRevenue) FROM Account WHERE Industry = 'Floppy Disks' GROUP BY Name

SUM

Returns the total sum of the column values.

SELECT SUM(AnnualRevenue) FROM Account WHERE Industry = 'Floppy Disks'

JOIN Queries

This section discusses some of the features and restrictions that are specific to how the connector supports joins. If possible, the connector to performs joins server side. Joins that Salesforce cannot process are performed client side when SupportEnhancedSQL is set to true. The connector for Salesforce supports server-side joins based on Salesforce Object Query Language (SOQL). The connector supports standard SQL syntax instead of proprietary SOQL to allow easy integration with a wide variety of SQL tools. Join queries in Salesforce are based on the relationships among Salesforce objects.

Relationship Queries

Salesforce objects can be linked using relationships. The standard Salesforce objects have predefined relationships. You can define relationships for your custom objects.

Parent to Child Relationships

Salesforce relationships are directional and are of the following types: one-to-many (parent to child) or many-to-one (child to parent). Since the relationships are directional the order in which the tables are included in the query determines the path of relationship traversal.

The following query shows a simple parent-to-child join query. This query returns all Accounts and the first and last name of each Contact associated with that Account.

SELECT Contact.FirstName, Account.NameFROM Account, Contact

Polymorphic Relationships

Salesforce relationships can be polymorphic. That is, a given relationship on a field can refer to more than one type of entity. For example, the Task entity contains a Who relationship, which by default may refer to a Contact or Lead.

The following query shows a join based on a polymorphic relationship. To return only Tasks referring to a Contact on the Who relationship, specify the type of entity in the relationship:

SELECT Task.Subject, Contact.NameFROM Task, ContactWHERE Contact.Type='Contact'

Custom Relationships

You can specify a join condition that is a custom relationship. The following query retrieves the names of all Account records and the first names of all Contacts that match the specified join condition:

Select Contact.Firstname, Account.NameFROM AccountJOIN ContactON Account.MyCustomColumn__c = Contact.Id

Server-Side Join Syntax

You can use the syntax detailed below to execute joins on Salesforce objects that are processed by the Salesforce servers. Joins on related objects are processed on the server. A benefit of predefined relationships is that you do not need to specify the join conditions to execute a join with Salesforce data; the conditions are already accounted for based on the relationship.

The following query returns the first names of all the Contacts in the organization and for each Contact the name of the parent Account associated with that Contact.

Select Contact.Firstname, Account.Name FROM Contact, Account

If there are multiple relationships between the tables, you can explicitly set the join criteria. The following query matches on a custom relationship determined by the columns in the join criteria (Account.MyCustomColumn__c and Contact.Id), instead of the default parent-to-child relationship between Accounts and Contacts:

Select Contact.Firstname, Account.Name FROM AccountJOIN ContactON Account.MyCustomColumn__c = Contact.Id

Salesforce supports inner joins. The following query retrieves all Account records that are associated with an Opportunity:

SELECT Account.Id, Account.Name, Account.Fax, Opportunity.AccountId, Opportunity.CloseDateFROM AccountINNER JOIN OpportunityON Account.Id = Opportunity.AccountId

The following query shows a join between three custom objects. This query joins the custom tables NW_Product__c, NW_Category__c, and NW_Suppliers__c and returns the Name field from each of them. Additionally, the results from the NW_Category__c table are filtered for names that start with "Dairy":

SELECT A.Name, B.Name, C.Name FROM NW_Product__c as A, NW_Category__c as B, NW_Suppliers__c as CWHERE B.Name LIKE 'Dairy%'

Date Literal Functions

The following date literal functions can be used to filter date fields using relative intervals. Note that while the <, >, and = operators are supported for these functions, <= and >= are not.

L_TODAY()

The current day.

SELECT * FROM MyTable WHERE MyDateField = L_TODAY()

L_YESTERDAY()

The previous day.

SELECT * FROM MyTable WHERE MyDateField = L_YESTERDAY()

L_TOMORROW()

The following day.

SELECT * FROM MyTable WHERE MyDateField = L_TOMORROW()

L_LAST_WEEK()

Every day in the preceding week.

SELECT * FROM MyTable WHERE MyDateField = L_LAST_WEEK()

L_THIS_WEEK()

Every day in the current week.

SELECT * FROM MyTable WHERE MyDateField = L_THIS_WEEK()

L_NEXT_WEEK()

Every day in the following week.

SELECT * FROM MyTable WHERE MyDateField = L_NEXT_WEEK()

Also available:

  • L_LAST/L_THIS/L_NEXT MONTH

  • L_LAST/L_THIS/L_NEXT QUARTER

  • L_LAST/L_THIS/L_NEXT YEAR

L_LAST_N_DAYS(n)

The previous n days, excluding the current day.

SELECT * FROM MyTable WHERE MyDateField = L_LAST_N_DAYS(3)

L_NEXT_N_DAYS(n)

The following n days, including the current day.

SELECT * FROM MyTable WHERE MyDateField = L_NEXT_N_DAYS(3)

Also available:

  • L_LAST/L_NEXT_90_DAYS

L_LAST_N_WEEKS(n)

Every day in every week, starting n weeks before current week, and ending in the previous week.

SELECT * FROM MyTable WHERE MyDateField = L_LAST_N_WEEKS(3)

L_NEXT_N_WEEKS(n)

Every day in every week, starting the following week, and ending n weeks in the future.

SELECT * FROM MyTable WHERE MyDateField = L_NEXT_N_WEEKS(3)

Also available:

  • L_LAST/L_NEXT_N_MONTHS(n)

  • L_LAST/L_NEXT_N_QUARTERS(n)

  • L_LAST/L_NEXT_N_YEARS(n)

Projection Functions

CONVERTCURRENCY(column)

Returns the currency field converted to the user's currency

  • column: Any column expression.

CALENDAR_MONTH(column)

Returns a number representing the calendar month of a date field (1 for January, 12 for December).

  • column: Any column expression.

CALENDAR_QUARTER(column)

Returns a number representing the calendar quarter of a date field (1 for January 1 through March 31, 2 for April 1 through June 30, 3 for July 1 through September 30, 4 for October 1 through December 31).

  • column: Any column expression.

CALENDAR_YEAR(column)

Returns a number representing the calendar year of a date field (2009).

  • column: Any column expression.

DAY_IN_MONTH(column)

Returns a number representing the day in the month of a date field (20 for February 20).

  • column: Any column expression.

DAY_IN_WEEK(column)

Returns a number representing the day of the week for a date field (1 for Sunday, 7 for Saturday).

  • column: Any column expression.

DAY_IN_YEAR(column)

Returns a date representing the day portion of a dateTime field (32 for February 1).

  • column: Any column expression.

DAY_ONLY(column)

Returns a date representing the day portion of a dateTime field (2009-09-22 for September 22, 2009).

  • column: Any column expression.

FISCAL_MONTH(column)

Returns a number representing the fiscal month of a date field. This differs from CALENDAR_MONTH() if your organization uses a fiscal year that does not match the Gregorian calendar. If your fiscal year starts in March: 1 for March, 12 for February.

  • column: Any column expression.

FISCAL_QUARTER(column)

Returns a number representing the fiscal quarter of a date field. This differs from CALENDAR_QUARTER() if your organization uses a fiscal year that does not match the Gregorian calendar. If your fiscal year starts in July: 1 for July 15, 4 for June 6.

  • column: Any column expression.

FISCAL_YEAR(column)

Returns a number representing the fiscal year of a date field. This differs from CALENDAR_YEAR() if your organization uses a fiscal year that does not match the Gregorian calendar (2009).

  • column: Any column expression.

HOUR_IN_DAY(column)

Returns a number representing the hour in the day for a dateTime field (18 for a time of 18:23:10).

  • column: Any column expression.

WEEK_IN_MONTH(column)

Returns a number representing the week in the month for a date field (2 for April 10). The first week is from the first through the seventh day of the month.

  • column: Any column expression.

WEEK_IN_YEAR(column)

Returns a number representing the week in the year for a date field (1 for January 3). The first week is from January 1 through January 7.

  • column: Any column expression.

Predicate Functions

CONVERTCURRENCY(column)

Returns the currency field converted to the user's currency

  • column: Any column expression.

CALENDAR_MONTH(column)

Returns a number representing the calendar month of a date field (1 for January, 12 for December).

  • column: Any column expression.

CALENDAR_QUARTER(column)

Returns a number representing the calendar quarter of a date field (1 for January 1 through March 31, 2 for April 1 through June 30, 3 for July 1 through September 30, 4 for October 1 through December 31).

  • column: Any column expression.

CALENDAR_YEAR(column)

Returns a number representing the calendar year of a date field (2009).

  • column: Any column expression.

DAY_IN_MONTH(column)

Returns a number representing the day in the month of a date field (20 for February 20).

  • column: Any column expression.

DAY_IN_WEEK(column)

Returns a number representing the day of the week for a date field (1 for Sunday, 7 for Saturday).

  • column: Any column expression.

DAY_IN_YEAR(column)

Returns a date representing the day portion of a dateTime field (32 for February 1).

  • column: Any column expression.

DAY_ONLY(column)

Returns a date representing the day portion of a dateTime field (2009-09-22 for September 22, 2009).

  • column: Any column expression.

FISCAL_MONTH(column)

Returns a number representing the fiscal month of a date field. This differs from CALENDAR_MONTH() if your organization uses a fiscal year that does not match the Gregorian calendar. If your fiscal year starts in March: 1 for March, 12 for February.

  • column: Any column expression.

FISCAL_QUARTER(column)

Returns a number representing the fiscal quarter of a date field. This differs from CALENDAR_QUARTER() if your organization uses a fiscal year that does not match the Gregorian calendar. If your fiscal year starts in July: 1 for July 15, 4 for June 6.

  • column: Any column expression.

FISCAL_YEAR(column)

Returns a number representing the fiscal year of a date field. This differs from CALENDAR_YEAR() if your organization uses a fiscal year that does not match the Gregorian calendar (2009).

  • column: Any column expression.

HOUR_IN_DAY(column)

Returns a number representing the hour in the day for a dateTime field (18 for a time of 18:23:10).

  • column: Any column expression.

WEEK_IN_MONTH(column)

Returns a number representing the week in the month for a date field (2 for April 10). The first week is from the first through the seventh day of the month.

  • column: Any column expression.

WEEK_IN_YEAR(column)

Returns a number representing the week in the year for a date field (1 for January 3). The first week is from January 1 through January 7.

  • column: Any column expression.

PreviousOracle Sales CloudNextSAP Hybris C4C

Last updated 2 years ago