Friday, February 27, 2009

Yahoo! Query Language

Try YQL if you'd like to compare with pure query languages in SQL Server, Oracle, MySQL, etc.
That's very necessary if you want to develop an application by using web services.
Use this link https://developer.yahoo.com/yql/console to run sample YQL

Thursday, February 26, 2009

Get all lookup tables and their values on EPM


SELECT t1.LT_UID, t1.LT_NAME, t2.LT_VALUE_TEXT, t2.LT_VALUE_SORT_INDEX
FROM MSP_LOOKUP_TABLES t1 INNER JOIN
MSP_LOOKUP_TABLE_VALUES t2 ON t1.LT_UID = t2.LT_UID
ORDER BY t1.LT_UID, t2.LT_VALUE_SORT_INDEX

Wednesday, February 18, 2009

Important tables in EPM

The following tables are important tables used in Enterprise Project Management you may need to query or build your custom reports

  1. MSP_PROJECTS: contains all projects
  2. MSP_RESOURCES: contains all resources
  3. MSP_TASKS: contains all tasks for all projects
  4. MSP_ASSIGNMENTS: which tasks a resource are working on

Code-blocks are not allowed in this file

Read here to solve your issues.

Tuesday, February 17, 2009

How to change title for ASPNET page in SharePoint Designer

I don't know why SharePoint Designer does not allow to change page title when I create an ASP.NET page from Master Page.
To be able to change the title, I have to use Javascript like that in asp:Content tag


<script type="text/javascript" >
document.title = 'My Title';
</script>

Change the default images on the home page

Read here

Sunday, February 15, 2009

What Can Database Developers and DBAs Do About SharePoint?

If you are a DBA and are using it. Do you like it or dislike it?
Read here
Source: Redmond Developer News

LEFT JOIN in Oracle and SQL Server

You have two tables:
tblEmployee(EmployeeID, EmployeeName, DepartmentID, ...) contains all employee information
tblDepartment(DepartmentID, DepartmentName) contains all departments in your company
Each employee belongs to one department. Sometimes, new employees don't have related department. You want to display all employees with their related departments. If someone does not have any department, display null
In this case, you have to use LEFT JOIN to query your data
In SQL Server:
SELECT t1.EmployeeID, t1.EmployeeName, t2.DepartmentName
FROM tblEmployee t1 LEFT JOIN tblDepartment t2 ON t1.DepartmentID = t2.DepartmentID

In Oracle:
SELECT t1.EmployeeID, t1.EmployeeName, t2.DepartmentName
FROM tblEmployee t1, tblDepartment t2
WHERE t1.DepartmentID = t2.DepartmentID(+)

Thursday, February 5, 2009

Query with DateTime field in Salesforce

If you have to work with DateTime field in Salesforce, note these differences:

1. Don't use quote in DateTime value, it's different with SQL Server or Oracle
For example:
SELECT Id FROM Account WHERE CreatedDate > 2005-10-08
In SQL Server, you must use:
SELECT Id FROM Account WHERE CreatedDate > '2005-10-08'

2. Use DateTime literals: YESTERDAY, TODAY, TOMORROW, LAST_WEEK, THIS_WEEK, NEXT_WEEK, etc.
For example:
SELECT Id FROM Account WHERE CreatedDate = YESTERDAY
In SQL Server, you may use:
SELECT Id FROM Account WHERE DateDiff(d, CreatedDate, GETDATE()) = 1

3. DateTime in Salesforce is UTC format, therefore you have to convert your datetime value to UTC format in your query which has comparision between date
For example:
In C# code, you have a control DateTimePicker dtpDateTime, you want to build a dynamic query based on this field. In this case, you can convert like that:
string strSQL = "SELECT Id FROM Account WHERE CreatedDate > " + dtpDateTime.value.ToString("yyyy-MM-ddThh:mm:ss.sssZ");


For more information, check here

Oracle Query