multi-table join problem

I have three tables that all contain different types of data but have a customer name in common. I can join the three of them into one view that gives me an overview of what issues they are having by using the following query:

SELECT dbo.ProjectContentCount.SiteName AS SiteContent, dbo.ProjectIssueCount.SiteName AS SiteIssue,
dbo.ProjectFeatureCount.SiteName AS SiteFeature, dbo.ProjectContentCount.[Open] AS OpenContent,
dbo.ProjectContentCount.Fixed AS FixedContent, dbo.ProjectContentCount.Closed AS ClosedContent, dbo.ProjectIssueCount.[Open] AS OpenIssue,
dbo.ProjectIssueCount.Pending AS PendingIssue, dbo.ProjectIssueCount.Closed AS ClosedIssue, dbo.ProjectFeatureCount.[Open] AS OpenFeature,
dbo.ProjectFeatureCount.Pending AS PendingFeature, dbo.ProjectFeatureCount.Closed AS ClosedFeature
FROM dbo.ProjectFeatureCount FULL OUTER JOIN
dbo.ProjectIssueCount ON dbo.ProjectFeatureCount.SiteName = dbo.ProjectIssueCount.SiteName FULL OUTER JOIN
dbo.ProjectContentCount ON dbo.ProjectIssueCount.SiteName = dbo.ProjectContentCount.SiteName AND
dbo.ProjectFeatureCount.SiteName = dbo.ProjectContentCount.SiteName

This works fine because it gets a list of all clients and data for whichever row there exists data of that type, whether or not there is data for a given client on each table.

However, I now need to join this group of data to a master client table that contains contact information. The master table has info about all the clients but every join I try breaks the join I have working above and causes duplicate rows.

Any ideas would be greatly appreciated!Well, I found my answer after many iterations in query analyzer and here is what I came up with. I hope it helps someone else...

SELECT dbo.SiteMaster.ItemID, dbo.SiteMaster.ModuleID, dbo.SiteMaster.SiteID, dbo.SiteMaster.SiteManager, dbo.SiteMaster.SiteStatus,
dbo.SiteMaster.SiteName, dbo.ProjectIssueCount.[Open] AS OpenIssue, dbo.ProjectIssueCount.Pending AS PendingIssue,
dbo.ProjectIssueCount.Closed AS ClosedIssue, dbo.ProjectContentCount.[Open] AS OpenContent, dbo.ProjectContentCount.Fixed AS FixedContent,
dbo.ProjectContentCount.Closed AS ClosedContent, dbo.ProjectFeatureCount.[Open] AS OpenFeature,
dbo.ProjectFeatureCount.Pending AS PendingFeature, dbo.ProjectFeatureCount.Closed AS ClosedFeature
FROM dbo.ProjectContentCount FULL OUTER JOIN
dbo.ProjectIssueCount FULL OUTER JOIN
dbo.SiteMaster ON dbo.ProjectIssueCount.SiteName = dbo.SiteMaster.SiteName FULL OUTER JOIN
dbo.ProjectFeatureCount ON dbo.ProjectIssueCount.SiteName = dbo.ProjectFeatureCount.SiteName AND
dbo.SiteMaster.SiteName = dbo.ProjectFeatureCount.SiteName ON dbo.ProjectContentCount.SiteName = dbo.SiteMaster.SiteName AND
dbo.ProjectContentCount.SiteName = dbo.ProjectIssueCount.SiteName

Multiple-step OLE DB operation generated errors

I'm trying to view a report on Report Manager (Reporting Services 2000) that displays Analysis Services (2000) data. I keep getting the following error message:

An error has occurred during report processing. (rsProcessingAborted) Get Online Help

Cannot create a connection to data source 'CubeName'. (rsErrorOpeningConnection) Get Online Help

Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.

I am using Visual Studio 2003 to build the report and I can successfully view the cube and pull data but when I deploy the report and data source to Report Manager I keep getting this error message. I am not using my credentials for the data source I am using a SQL account that is a sys admin and has access to the cube I am trying to view.

Additional Information: Visual Studio - local machine
SQL Server/Analysis Services - Machine A
Reporting Services - Machine B

See this http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=706585&SiteID=1 is any help.

Multiple-step OLE DB operation generated errors

I'm trying to view a report on Report Manager (Reporting Services 2000) that displays Analysis Services (2000) data. I keep getting the following error message:

An error has occurred during report processing. (rsProcessingAborted) Get Online Help

Cannot create a connection to data source 'CubeName'. (rsErrorOpeningConnection) Get Online Help Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.

I am using Visual Studio 2003 to build the report and I can successfully view the cube and pull data but when I deploy the report and data source to Report Manager I keep getting this error message. I am not using my credentials for the data source I am using a SQL account that is a sys admin and has access to the cube I am trying to view.

Additional Information: Visual Studio - local machine
SQL Server/Analysis Services - Machine A
Reporting Services - Machine B

See this http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=706585&SiteID=1 is any help.

Multiple view from a single select statement

Hi,
I want to write an SQL query which should return me 2 kinds of outputs depending on which condition is true. i.e.

the query should be something like

select someview if (condition1 = true)
else select someotherview if (condition2 = true)

where someview is a set of columns from one table only, and someotherview is a set of columns which is a superset of someview (& is generated from two tables, which have no common field)

Let me explain this further --
I want to select data from one table and a single field from other table, however if I the first table does not return any data, I still want data from the other table to be returned.
Can I write a SQL query to do this?

-- Amitdo a union query.

select * from table1
union
select 'ed' from table2;

if table 1 is return not rows table2 will still return data.

Does this answer your question?|||Originally posted by edwinjames
do a union query.

select * from table1
union
select 'ed' from table2;

if table 1 is return not rows table2 will still return data.

Does this answer your question?

I know, that I can use a union, however I want to support TimesTen & Oracle using thsame query...TT does not allow union while oracle does, can I write another query which emulates union?|||I am sorry I am not farmiliar with TT.

Does it support outer joins?|||Originally posted by edwinjames
I am sorry I am not farmiliar with TT.

Does it support outer joins?

Yes it dows, hence I am now using outer joins|||Glad to be of help

:)

multiple view create

Hi
How can I create multiple views in a single SQL script ?
(I get an error: 'CREATE VIEW' must be the first statement in a query
batch.)
thanksYou can use the batch terminator, GO, to seperate the batches:
CREATE VIEW xyz
AS
SELECT
..
GO
CREATE VIEW abc
AS
SELECT
..
GO
... etc
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"romy" <romy1000@.hotpop.com> wrote in message
news:e6ml7BM5FHA.636@.TK2MSFTNGP10.phx.gbl...
> Hi
> How can I create multiple views in a single SQL script ?
> (I get an error: 'CREATE VIEW' must be the first statement in a query
> batch.)
>
> thanks
>sql

Multiple set result from a stored procedure as the data source

My stored procedure generates two results:
1) declares a variable executes a view to obtain date driven data which has
a begin and end
2) selects the data associated with date driven info
When RS executes the stored procedure I only get the first set results (the
date information).
Can RS handle multiple results from a stored procedure? If so is there any
information to read.
I can change my stored procedure with a couple joins but I am curious how RS
works (I'm a newbee).
SincerelyRS only works with the first resultset.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"HelpInRS" <HelpInRS@.discussions.microsoft.com> wrote in message
news:CDADB1FA-43D3-40A0-BD94-CFD98F9FAFCE@.microsoft.com...
> My stored procedure generates two results:
> 1) declares a variable executes a view to obtain date driven data which
has
> a begin and end
> 2) selects the data associated with date driven info
> When RS executes the stored procedure I only get the first set results
(the
> date information).
> Can RS handle multiple results from a stored procedure? If so is there any
> information to read.
> I can change my stored procedure with a couple joins but I am curious how
RS
> works (I'm a newbee).
> Sincerely|||Sorry...RS doesn't support multiple result sets.
Adrian M.
"HelpInRS" <HelpInRS@.discussions.microsoft.com> wrote in message
news:CDADB1FA-43D3-40A0-BD94-CFD98F9FAFCE@.microsoft.com...
> My stored procedure generates two results:
> 1) declares a variable executes a view to obtain date driven data which
> has
> a begin and end
> 2) selects the data associated with date driven info
> When RS executes the stored procedure I only get the first set results
> (the
> date information).
> Can RS handle multiple results from a stored procedure? If so is there any
> information to read.
> I can change my stored procedure with a couple joins but I am curious how
> RS
> works (I'm a newbee).
> Sincerely|||Thanks for the info
"Adrian M." wrote:
> Sorry...RS doesn't support multiple result sets.
> Adrian M.
> "HelpInRS" <HelpInRS@.discussions.microsoft.com> wrote in message
> news:CDADB1FA-43D3-40A0-BD94-CFD98F9FAFCE@.microsoft.com...
> > My stored procedure generates two results:
> > 1) declares a variable executes a view to obtain date driven data which
> > has
> > a begin and end
> > 2) selects the data associated with date driven info
> >
> > When RS executes the stored procedure I only get the first set results
> > (the
> > date information).
> >
> > Can RS handle multiple results from a stored procedure? If so is there any
> > information to read.
> >
> > I can change my stored procedure with a couple joins but I am curious how
> > RS
> > works (I'm a newbee).
> >
> > Sincerely
>
>

multiple selection listbox as data control?

Hi, i have a listbox with multiple selection enabled, the end user uses this listbox to select what data they want to view eg. they select "green" to view all the green cars, "red" to select all the red cars etc.

i have the listbox as the control that is connected to the datasource (the sql used for it isselect * from cars_table where color =@.color

this works fine when one item in the listbox is selected, but when multiples are selected it does not work

what format does the=@.colorhave to be when multiples are selected? i've tried "green, red" "green + red" etc. but cannot seem to get it working

does anybody have any working examples that i can take a look at? it seems to be a common action, yet i cannot seem to find any documentation on how to get it to work

thanks in advance!

You will have to iterate through the selections and dynamically build your SQL

foreach (ListItem itemin ListBox1.Items)
{
if(item.Selected)
 {
sql += " Or color = '" + item.Text +"'";
 }
}