From the Archives: Siebel Query Language And Julian Functions
Time for another 'blast from the past'. From August 2008, the below article covers the all-time favorite query language that is SQL, but not the SQL you think of now.
Romeo and Julia(n)?
About the Siebel Query Language
First of all, it abbreviates to SQL which is kind of funny, of course it has nothing to do with SQL.
Just like you can get more out of a spreadsheet application like Excel when you are familiar with its internal functions and syntax, you can get much more out of Siebel CRM if you are familiar with the Siebel Query Language.
Where is it used?
The answer to this question is very interesting, it is used almost everywhere when you define expressions. It is available in both Siebel Tools and the Siebel Web Client and since version 8.0 both the developement environment and the Web Client (that one since 7.0) sport an expression designer.
Expression Designer in the Siebel Web Client
click to enlarge
Expression Designer in Siebel Tools (since 8.0)
Here is a probably complete list of areas where you can use the Siebel Query Language:
Siebel Web Client
Siebel Tools
Where is the reference?
The Siebel Query Language is documented in the Personalization Administration Guide of the Siebel Bookshelf.
An example:
The following is an example for a predefined query. The task is to filter the list of contacts so that it displays only persons that have their birthday in the current month.
Working with date fields can be tricky and you just can't type something like *June* in a date field. This is where the date functions, also known as julian functions come into play.
Our first choice would be the JulianMonth() function. This function takes a date field as a parameter and returns the julian number of the current month which is for example 80660 for August 2008. So the nerds among you will have quickly calculated this back and month #1 is of course January of 4713 BC.
If we want to query for persons who have their birthday in the current month, then the JulianMonth() function alone is not helpful because the return value for August 1968 is different from August 2008, so we need a way to extract the month number (1-12) from the date. This can be accomplished by multiplying the julian year number (6721 for 2008) by 12 and subtracting it from the julian month number.
So the equation is: # of current month = JulianMonth(Today()) - JulianYear(Today())*12
This equation will be the base for our query in Siebel CRM. So basically if we want to match the Birth Date field of a Contact with the # of current month, the query goes as follows:
JulianMonth([Birth Date])-JulianYear([Birth Date])*12 = JulianMonth(Today()) - JulianYear(Today())*12
Creating the predefined query:
Usually you can enter even complex queries directly in any control or list column of the applet (in query mode) but there are restrictions in field length and data type. So here is a way that always works.
Navigate to the Contacts List and save the current query (even an empty query would do).
click to enlarge
Navigate to Site Map -> Administration - Application -> Predefined Queries and retrieve your previously saved query.
Replace anything that might be between the two double quotes after 'Contact'.Search = with
JulianMonth([Birth Date])-JulianYear([Birth Date])*12 = JulianMonth(Today()) - JulianYear(Today())*12
So finally it looks like this:
click to enlarge
In the Household Contact view you can enter some birth dates and run your test in the contact List. I have added the Birth Date field to the Contact List Applet to verify.
List of contacts with a birth date in August
click to enlarge
***
Romeo and Julia(n)?
About the Siebel Query Language
First of all, it abbreviates to SQL which is kind of funny, of course it has nothing to do with SQL.
Just like you can get more out of a spreadsheet application like Excel when you are familiar with its internal functions and syntax, you can get much more out of Siebel CRM if you are familiar with the Siebel Query Language.
Where is it used?
The answer to this question is very interesting, it is used almost everywhere when you define expressions. It is available in both Siebel Tools and the Siebel Web Client and since version 8.0 both the developement environment and the Web Client (that one since 7.0) sport an expression designer.
Expression Designer in the Siebel Web Client
click to enlarge
Expression Designer in Siebel Tools (since 8.0)
Here is a probably complete list of areas where you can use the Siebel Query Language:
Siebel Web Client
- Predefined Queries
- Conditional Expressions for Personalization Rules
- Conditional Expressions for Runtime Events
- Data Validation Manager rules
- Data Map Editor
- [Update] iHelp branching expressions
Siebel Tools
- Calculated fields
- Pre-default and post-default values for fields
- Field Validation property
- User properties
- Expressions in Siebel Workflow and UI Tasks
Where is the reference?
The Siebel Query Language is documented in the Personalization Administration Guide of the Siebel Bookshelf.
An example:
The following is an example for a predefined query. The task is to filter the list of contacts so that it displays only persons that have their birthday in the current month.
Working with date fields can be tricky and you just can't type something like *June* in a date field. This is where the date functions, also known as julian functions come into play.
Our first choice would be the JulianMonth() function. This function takes a date field as a parameter and returns the julian number of the current month which is for example 80660 for August 2008. So the nerds among you will have quickly calculated this back and month #1 is of course January of 4713 BC.
If we want to query for persons who have their birthday in the current month, then the JulianMonth() function alone is not helpful because the return value for August 1968 is different from August 2008, so we need a way to extract the month number (1-12) from the date. This can be accomplished by multiplying the julian year number (6721 for 2008) by 12 and subtracting it from the julian month number.
So the equation is: # of current month = JulianMonth(Today()) - JulianYear(Today())*12
This equation will be the base for our query in Siebel CRM. So basically if we want to match the Birth Date field of a Contact with the # of current month, the query goes as follows:
JulianMonth([Birth Date])-JulianYear([Birth Date])*12 = JulianMonth(Today()) - JulianYear(Today())*12
Creating the predefined query:
Usually you can enter even complex queries directly in any control or list column of the applet (in query mode) but there are restrictions in field length and data type. So here is a way that always works.
Navigate to the Contacts List and save the current query (even an empty query would do).
click to enlarge
Navigate to Site Map -> Administration - Application -> Predefined Queries and retrieve your previously saved query.
Replace anything that might be between the two double quotes after 'Contact'.Search = with
JulianMonth([Birth Date])-JulianYear([Birth Date])*12 = JulianMonth(Today()) - JulianYear(Today())*12
So finally it looks like this:
click to enlarge
In the Household Contact view you can enter some birth dates and run your test in the contact List. I have added the Birth Date field to the Contact List Applet to verify.
List of contacts with a birth date in August
click to enlarge
have a nice day
@lex
תגובות
הוסף רשומת תגובה