SFDC – Using a timestamp literal in a where clause

Salesforce Connector

Salesforce Connector

Working with timestamp literals in the Infosphere SFDC Connector soql is much like working date literals.  So, here a quick example which may save you some time.

SOQL Timestamp String Literals Where Clause Rules

Basically, the timestamp pattern is straight forward and like the process for dates, but there are some differences. The basic rules are for a soql where clause:

  • No quotes
  • No functions
  • No Casting function, or casting for the where soql where clause to read it
  • It only applies to datetime fields
  • A Timestamp identifier ‘T’
  • And the ISO 1806 time notations

Example SOQL Timestamp String Literals

So, here are a couple of timestamp string literal examples in SQL:

  • 1901-01-01T00:00:00-00:00
  • 2016-01-31T00:00:00-00:00
  • 9999-10-31T00:00:00-00:00

Example SQL with Timestamp String Literal Where Clause

 

Select e.Id,

e.AccountId,

e.StartDateTime

From Event e

WHERE e.StartDateTime > 2014-10-31T00:00:00-00:00

 

Related References

Salesforce Developer Documentation

Home, Developer Documentation, Force.com SOQL and SOSL Reference

https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_select_dateformats.htm

Salesforce Workbench

Home, Technical Library, Workbench

W3C

Date Time Formats

 

What is an ERP?

Enterprise Resource Planning (ERP)

Enterprise Resource Planning (ERP)

What does ERP mean?

  • ERP Means “Enterprise Resource Planning”

What is an ERP?

  • An ERP is business software application or series of applications, which facilitate the daily operations of business. An ERP an be commercial-off-the-shelf (COTS) applications (which may or may not be customized) or custom built (home grown) by the business and/or assemblages of different vendor applications and/or models.  ERP applications dules from a variety of vendors.

Common ERP Major Functions

  • ERP application software typically support these major business operations:

Financials Management system (FMS)

  • FMS supports accounting, consolidation, planning, and procurement.

Customer Relationship Management (CRM)

  • CRM facilitates customer interactions and data throughout the customer lifecycle, with the goal of improving business relationships with customers, assisting in customer retention and sales growth.

Human Resources Management System (HRMS)

  • HRMS supports workforce acquisition, workforce management, workforce optimization, and benefits administration

Enterprise Learning Management (ELM)

  • ELM is the integrated application which increases workforce knowledge, and skills, and competencies to achieve critical organizational objectives.

Asset Management (AM)

  • AM support activities for deploying, operating, maintaining, upgrading, and disposing of assets cost-effectively.

Supply Chain management (SCM)

  • SCM is the oversight of materials, information, and finances as they move in a process from supplier to manufacturer to wholesaler to retailer to consumer.

Related References

Salesforce – Useful Links

Salesforce Connector,SFDC, salesforce.com, CRM, ERP, useful links

Salesforce

 

Here are few SFDC link, which I have found to be useful.

Salesforce Developers

Home, Developer Documentation, Force.com SOQL and SOSL Reference

IBM Knowledge Center

InfoSphere Information Server, InfoSphere Information Server 11.5.0, Connecting to data sources, Enterprise applications, IBM InfoSphere Information Server Pack for Salesforce.com

Salesforce Developers

Home, Technical Library, Workbench

Salesforce Developers

Home, Technical Library, Force.com Tools and Toolkits

Related References

 

 

SFDC Salesforce Connector – Column Returns Null values, when SOQL Returns Data in Workbench

Salesforce Connector

Salesforce Connector

Recently, encountered a scenario, which is a little out of the norm while using the SFDC Connector.  Once the issue is understood, it is easily remedied.

The problem / Error

  • SOQL run in Salesforce workbench and column returns data
  • The DataStage job/ETL runs without errors or warnings
  • The target column output only returns null values

The Cause

In short the cause is a misalignment between the SOQL field name and the column name in the columns tab of the connector.

The Solution

The fix is simply to convert the dots in the field name to underscores.   Basically, a field name on SOQL of Account.RecordType.Name becomes Account_RecordType_Name.

Example Field / Column Name  Fix

Example SQL

Select c.Id,

c.AccountId,

c.CV_Account_Number__c,

c.Name,

c.Role__c,

c.Status__c,

c.Account.RecordType.Name

From Contact c

Columns Tab With Correct Naming Alignment

Please note that the qualifying dots have been converted to underscores.

infosphere datastage SFDC Connector Columns Tab

SFDC Connector Columns Tab

Related References

 

What does CRM Mean?

Customer Relationship Management (CRM)

Customer Relationship Management (CRM)

 

What is CRM?

CRM (customer relationship management) is a type of ERP application, which are used to facilitate sales, marketing, and business development interactions throughout the customer life cycle.

What does a CRM Application do?

A CRM application capabilities, broadly, encompass:

Marketing Integration

  • Lead management, email marketing, and campaign management

Sales Force Automation

  • Contact management, pipeline analysis, sales forecasting, and more

Customer Service & Support

  • Ticketing, knowledge management systems, self-service, and live chat

Field Service Management

  • Scheduling, dispatching, invoicing, and more

Call Center Automation

  • Call routing, monitoring, CTI, and IVR

Help Desk Automation

  • Ticketing, IT asset management, self-service and more

Channel Management

  • Contact and lead management, partner relationship management, and market development funds management

Business analytics integration

  • Analytics application and Business intelligence and reporting integration, which may include internal reporting capabilities.

Related References

SFDC – Using a date literal in a where clause

Salesforce Connector

I found working with date literal, when working with the Infosphere SFDC Connector soql, to be counterintuitive for me.  At least as I, normally, as I use SQL.  I spent a little time running trials in Workbench, before I finally locked on to the ‘where clause’ criteria data pattern.  So, here a quick example.

SOQL DATE String Literals Where Clause Rules

Basically, the date pattern is straight forward. The basic rules are for a soql where clause:

  • No quotes
  • No functions
  • No Casting function, or casting for the where soql where clause to read.

Example SOQL DATE String Literals

So, here are a couple of date string literal examples in SQL:

  • 1901-01-01
  • 2016-01-31
  • 9999-10-31

Example SQL with Date String Literal Where Clause

 

Select

t.id,

t.Name,

t.Target_Date__c,

t.User_Active__c

From Target_and_Segmentation__c t

where t.Target_Date__c > 2014-10-31

 

Related References

Salesforce Developer Documentation

Home, Developer Documentation, Force.com SOQL and SOSL Reference

https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_select_dateformats.htm

Salesforce Workbench

Home, Technical Library, Workbench