Joining datasets returned from stored procedures.

Hello,
I am restricted to using stored procedures for data access. I would like to
be able to create a "Text" Dataset named C which is a join of two "Stored
Procedure" data sets (sp1, and sp2). Any idea how I can do this. Thanks.Tyr this:
SELECT * FROM OPENQUERY(servername, 'sp_1')
union
SELECT * FROM OPENQUERY(servername, 'sp_2')
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jason Agee" <jason.agee@.tlc.state.tx.us> wrote in message
news:%23%23nvqx4YEHA.1448@.TK2MSFTNGP12.phx.gbl...
> Hello,
> I am restricted to using stored procedures for data access. I would like
to
> be able to create a "Text" Dataset named C which is a join of two "Stored
> Procedure" data sets (sp1, and sp2). Any idea how I can do this. Thanks.
>

joining a table to a user-defined function?

Suppose I have a SQL Server table named 'gadget'. 'gadget' has an integer field named 'gadget_key', which is the primary key of the table.

Now suppose I have a user-defined function named 'udf_gadget_values'. This function takes as its input parameter an integer variable named '@.nGadgetKey'. This function returns a table which will always contain exactly one record. This one record has one field named 'nGadgetKey', which contains the same value that was passed to the function in parameter '@.nGadgetKey'

I would like to join the table 'gadget' with the table that function 'udf_gadget_values' returns kind of like this:

SELECT TOP 10 *
FROM gadget, udf_gadget_values(gadget.gadget_key)

The purpose of this query is to get the top 10 records from 'gadget', as well as the values associated with each record, as returned by the function.

The real issue is this: how do I pass gadget.gadget_key to the function as an input parameter? Or if this will not work, is there an alternative?

Hi,

This syntax is neither supported in Yukon nor Shiloh. I believe the problem is that the output rowset cannot be materialized until the function is evaluated, and yet, the function cannot be evaluated until the output rowset is materialized.

In Yukon we've introduced a new relational operator called CROSS APPLY that you could use in scenarios like this. The LHS of CROSS APPLY is a table source and the RHS is a table-valued function. The formal input parameters of the function can be bound to actual column values materialized in the LHS rowset. In other words, for each row of the LHS, evaluate the function on the RHS and JOIN the results to the LHS, resulting in >=1 row in the ultimate output rowset. In essense, it solves the problem described above by assigning a formal and well-defined evaluation strategy to the LHS and RHS of the CROSS APPLY.

It would look like this:

select * from gadget cross apply udf_gadget_values(gadget_key)

Moreover, in Yukon, we've changed the parser to allow function input parameters to bind to correlated subqueries in FROM clause and in the projection list. The examples below illustrate:

-- Yukon : works
-- Shiloh: !works
select * from gadget where exists
(select * from udf_gadget_values(gadget.gadget_key))

-- Yukon : works
-- Shiloh: !works
select *
, (select gadget_desc
from dbo.udf_gadget_values(gadget.gadget_key))
as function_value
from gadget

Regards,
Clifford Dibble
Program Manager, SQL Server

Joining 2 Foreign keys in a table

I have a table that is named CliCore and has the following fields,
RegNo - Primary Key
CliFName - First Name
CliMM - Middle Initial
CliLName - Last Name
CliDOB - Date of Birth
I have another table that is named CliEvents and has the following fields,
UID - Primary Key
RegNo - Foreign Key from CliCore table
AggressorRegNo - Same as above
EventCatID - Category ID
Comments - Event Comments
How can I get the names of the clients for both RegNo and AggressorRegNo?
Thanks,
Drewyou have to refer 2 times to the CliCore table...something like:
select ... from clievents inner join CliCore A on CliEvents .RegNo =
A.RegNo inner join CliCore B on CliEvents .AggressorRegNo = B.RegNo
Francesco Anti
"Drew" <drew.laing@.NOswvtc.dmhmrsas.virginia.SPMgov> wrote in message
news:OYAKkK0RFHA.1176@.TK2MSFTNGP12.phx.gbl...
>I have a table that is named CliCore and has the following fields,
> RegNo - Primary Key
> CliFName - First Name
> CliMM - Middle Initial
> CliLName - Last Name
> CliDOB - Date of Birth
> I have another table that is named CliEvents and has the following fields,
> UID - Primary Key
> RegNo - Foreign Key from CliCore table
> AggressorRegNo - Same as above
> EventCatID - Category ID
> Comments - Event Comments
> How can I get the names of the clients for both RegNo and AggressorRegNo?
> Thanks,
> Drew
>|||SELECT E.uid,
E.regno, C1.clilname,
E.aggressorregno, C2.clilname,
E.eventcatid, E.comments
FROM CliEvents AS E
JOIN CliCore AS C1
ON E.regno = C1.regno
JOIN CliCore AS C2
ON E.aggressorregno = C2.aggressorregno
David Portas
SQL Server MVP
--
(untested)|||Try,
select a.CliFName, a.CliLName, b.CliFName, b.CliLName
from CliEvents as e inner join CliCore as a on e.RegNo = a.RegNo
inner join CliCore as b on e.AggressorRegNo = b.RegNo
AMB
"Drew" wrote:

> I have a table that is named CliCore and has the following fields,
> RegNo - Primary Key
> CliFName - First Name
> CliMM - Middle Initial
> CliLName - Last Name
> CliDOB - Date of Birth
> I have another table that is named CliEvents and has the following fields,
> UID - Primary Key
> RegNo - Foreign Key from CliCore table
> AggressorRegNo - Same as above
> EventCatID - Category ID
> Comments - Event Comments
> How can I get the names of the clients for both RegNo and AggressorRegNo?
> Thanks,
> Drew
>
>|||Thank you all for the replies... I was trying it like this and it wasn't
working...
SELECT...
FROM Events E INNER JOIN CliCore CC ON E.RegNo = CC.RegNo OR
E.AggressorRegNo = CC.RegNo...
Thanks a bunch for clearing this up!
Drew
"Drew" <drew.laing@.NOswvtc.dmhmrsas.virginia.SPMgov> wrote in message
news:OYAKkK0RFHA.1176@.TK2MSFTNGP12.phx.gbl...
>I have a table that is named CliCore and has the following fields,
> RegNo - Primary Key
> CliFName - First Name
> CliMM - Middle Initial
> CliLName - Last Name
> CliDOB - Date of Birth
> I have another table that is named CliEvents and has the following fields,
> UID - Primary Key
> RegNo - Foreign Key from CliCore table
> AggressorRegNo - Same as above
> EventCatID - Category ID
> Comments - Event Comments
> How can I get the names of the clients for both RegNo and AggressorRegNo?
> Thanks,
> Drew
>

Joined view does not contain all colums

The joined view is named "dbo.viewExecView" and is like:

SELECT Bank_No, data_center
FROM [ALPHA\SQL2000].ev_db.dbo.Bank

The new view that joins to the above view is like:

SELECT bank.BankID, evBank.data_center AS DataCenterID
FROM dbo.Bank AS bank INNER JOIN
dbo.viewExecView_Bank AS evBank ON bank.BankID = evBank.BankID WHERE (bank.InactiveDate IS NULL)

Note: The data_center column (an int) was recently added to the Bank table in the linked ev_db database and it shows up there. It also shows up in the view "dbo.viewExecView". It does not appear in the new view that joins to "dbo.viewExecView". And when I run the 'new' view, I get an Error Message: Invalid column name 'data_center'.

I've tried to simplify this as much as possible while still including the pertinent information. Any help very much appreciated, I am currently stumped.

Regards,

Joe

It looks ok to me. Does your simplified query (below) work?

Joe G wrote:

SELECT bank.BankID, evBank.data_center AS DataCenterID
FROM dbo.Bank AS bank INNER JOIN
dbo.viewExecView_Bank AS evBank ON bank.BankID = evBank.BankID WHERE (bank.InactiveDate IS NULL)

|||

first thing, try running ALTER VIEW with their current defenitions. A view is built at runtime like a table. It has records in syscolumns etc. Thus this thread brings up the old "what does SELECT * return in a view" questions. I got this wrong not too long ago when IView with MSFT...I could have killed myself lol.

I am telling you this info. because whenever someone says "a column that I recently added is not showing up in view", this is usually the problem. rerun THE ALTERVIEW statement.

HTH,

Derek

if this does not solve your problem, let me know and I will dig into it w/you further.

|||

Derek, Thank you very much! That indeed was the solution to my problem. Thanks to Skippy also for responding.

Joe