Skip Ribbon Commands
Skip to main content
Navigate Up
Sign In

Quick Launch

Average Rating:

(2 Ratings)
facebook Twitter
Email
Print Bookmark Alert me when this article is updated

Feedback

HOW TO: Use $$$SessStartTime in a PowerCenter source qualifier SQL override
Solution

The system variable $$$SessStartTime returns the session start time as a string value. It can be used to as part of a dynamic filter in the SQL override of a source qualifier.

Example

Here are two examples that select all dates that are $$$SessStartTime minus 1 day using Oracle and DB2:

Oracle

In Oracle you must specify the date format that you are comparing with, such as 'DD/MM/YYYY', for both condition fields.

SELECT TABLENAME.TSCOLUMN
FROM
TABLENAME
WHERE TO_CHAR(TABLENAME.TSCOLUMN,'DD/MM/YYYY') =
TO_CHAR(TO_DATE(('$$$SessStartTime' , 'DD/MM/YYYY HH24:mi:ss')-1), 'DD/MM/YYYY')
 

DB2

SELECT TABLENAME.TSCOLUMN
FROM
TABLENAME
WHERE SUBSTR(CHAR(TABLENAME.TSCOLUMN),1,10) =
DATE(SUBSTR(CHAR('$$$SessStartTime' ),1,10)) - 1 DAYS

Where TABLENAME.TSCOLUMN is defined as DB2 Timestamp datatype.
In DB2 the comparison is done on the format of the timestamp field which is set at database level.
Timestamp format:

YYYY-MM-DD-HH24.MM.SI.SSSSSS

Notes

  • $$$SessStartTime must always be in quotes when used in the SQL override statement (as shown above).

  • For further functionality please refer to the relevant database SQL commands.

  • While the examples above are for the specific databases, the same principle applies to the other databases.
    You need to specifically look at the date functions available.

More Information
Reference
Applies To
Product: PowerCenter
Problem Type:
User Type: Administrator
Project Phase:
Product Version: PowerCenter
Database:
Operating System:
Other Software:
Attachments
Last Modified Date:1/29/2016 3:02 AMID:10861
People who viewed this also viewed

Feedback

Did this KB document help you?



What can we do to improve this information (2000 or fewer characters)