Right Outer Join with Where clause

Using SQL Server 2005

I am reporting on a system with 32 devices, each of these devices can have certain events that happen to it that are logged and timestamped.
I need a to show the count of each events that have happened to it within a certain time period.
This code snippet below works fine BUT if there are no events that happen to a certain device in the time period, then that device is 'missing' from the table.
What I need is basically a row for every device, regardless of if it has had any events happen to it (I will just show '0' for the event count)
Any thoughts? I'm a complete newbie at this by the way.

Thanks

Code Snippet

SELECT DeviceStatusWords.DeviceName, COUNT(DeviceEventDurationLog.StatusBit) AS BitCount, DeviceEventDurationLog.StatusBit AS Bit
FROM DeviceEventDurationLog RIGHT OUTER JOIN
DeviceStatusWords ON DeviceEventDurationLog.DeviceID = DeviceStatusWords.DeviceID
WHERE (DeviceEventDurationLog.TimeIn > @.StartDate) AND (DeviceEventDurationLog.TimeIn < @.EndDate)
GROUP BY DeviceStatusWords.DeviceName, DeviceEventDurationLog.StatusBit
ORDER BY DeviceStatusWords.DeviceName

Here it is,

Code Snippet

select

devicestatuswords.devicename,

count(deviceeventdurationlog.statusbit) as bitcount,

deviceeventdurationlog.statusbit as bit

from

deviceeventdurationlog

right outer join devicestatuswords

on deviceeventdurationlog.deviceid = devicestatuswords.deviceid

and (deviceeventdurationlog.timein > @.startdate)

and (deviceeventdurationlog.timein < @.enddate)

group by

devicestatuswords.devicename,

deviceeventdurationlog.statusbit

order by

devicestatuswords.devicename

|||

It looks to me like you need to join this stuff to a "DEVICE" table or something similar that lists all "DeviceID" entries. If you will take the "DEVICE" table (or similar) and left join to the "DEVICE" table the results of this query you should have what you are after.

|||

I believe the reason you're missing rows is because the engine is fulfilling the FROM clause first, and then filtering out rows based upon the WHERE clause. In the following example, the engine will do all the filtering at the same time and so the RIGHT join maintains the records in the DeviceStatusWords table.

Code Snippet

....

FROM DeviceEventDurationLog RIGHT OUTER JOIN
DeviceStatusWords ON DeviceEventDurationLog.DeviceID = DeviceStatusWords.DeviceID
AND (DeviceEventDurationLog.TimeIn > @.StartDate) AND (DeviceEventDurationLog.TimeIn < @.EndDate)

.....

HTH!

|||The DeviceStatusWords table is the table that contains the list of devices. That's why I needed the RIGHT OUTER JOIN to select all devices from the DeviceStatusWords table. I just couldn't fiugure out the exact syntax - the previous reply was just what I was looking for though
- thanks guys for once again educating me!sql

ReturnProviderSpecificTypes

Is it possible to set this in Analysis Services 2005?

I am going against an oracle number and Analysis Services is picking Double as the datatype but it is not working?

I am getting the following as the error- 'Errors in the high-level relational engine. The following exception occurred while the managed IDataReader interface was being used: Arithmetic operation resulted in an overflow..'

If I cast the column to number(28) it works which is the size of system.decimal

Moved to SQL Server Analysis Services forum.

For AS group -- ReturnProviderSpecificTypes is a setting on the DataAdapter that allows the DataReader to return column types that more closely align with the underlying backend types, rather than standard CLR types. For example, OracleNumber instead of Decimal or Double.

DataAdapter.ReturnProviderSpecificTypes Property
http://msdn2.microsoft.com/en-us/library/system.data.common.dataadapter.returnproviderspecifictypes(d=ide).aspx

Thanks,
Sarah

|||Is there anything i can do in analysis services to accept the number(38) that oracle is sending instead of casting to number(28)?|||

What provider you are using to connect to Oracle?

You have 2 options: Microsoft OLEDB provider and .NET native Oracle client.

If you are using one, try the second option, see if it works for you.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.