Derived Table Alternative

I have an application that has two different database backends, one is SQL Server Compact Edition and the other is SQL Server. The reason is because the application may run at home on one of our sales agent's computers or here in the office.

I have a query that uses a derived table and works just fine in SQL Server, however when I run it in the compact edtion (having the exact same table structures) it will not run. My question is...does the Compact Edtion or the Mobile Edition allow derived tables. If not is there a way to work around this? I will happily give an example if it will help.

Thank you,

Adam

Hi Adam,

a sample would be very useful, thanks.

|||

The three tables used for this are Assignment, Activity, and User. The Assignment table can have multiple Activities entered by different Users (only 1 user per activity). I need a list of all of the active assignments for a given User along with the last activity that was added to that assignment. The assignment may or may not have an activity but the assignment still needs listed. The activity returned must be one entered by the same user that the assignment belongs to.

Here is an example that works in SQL Server but not SqlCE:

select assignment.assignmentID, activityDateStamp
from assignment
left join
(
select assignment.assignmentID, max(activityDateStamp) as activityDateStamp
from activity
join assignment on assignment.assignmentID = activity.assignmentID
where assignment.userID = 40
and activity.userID = 40
and activityActive = 1
and assignmentActive = 1
group by assignment.assignmentID
) maxActivity on maxActivity.assignmentID = assignment.assignmentID
where assignment.userID = 40
and assignmentActive = 1

The output would look similar to this:

assignmentID activityDateStamp
-
123 NULL
4322 2006-06-23
423 2006-12-15
431 NULL

Thanks again for any help.

|||

Derived tables are not supported in SSCE.

|||I guess if we can provide some feedback to the team for future versions having derived/nested queries would definitely be something worth having. This limitation currently prevents us doing more than the simplest of queries.|||

Thanks Nick.

What I did not mention earlier was that support for derived tables aka nested queries would be there with the next release of Orcas!!