Combine two tables into one RS

Here is my dilemma, i'm trying to combine the results of two different tables. Both tables are very similar but the data returned must be sorted before they are combined because I'm only returning the top xx records based on a hits column.

Here is a sample of the two databases:

Table 1
ID - SONG - HITS
1 - tb1SONG 1 - 356
2 - tb1SONG 2 - 1459
3 - tb1SONG 3 - 278
4 - tb1SONG 4 - 965
5 - tb1SONG 5 - 124

Table 2
ID - tb2SONG - HITS
1 - tb2SONG 1 - 412
2 - tb2SONG 2 - 85
3 - tb2SONG 3 - 2035
4 - tb2SONG 4 - 693
5 - tb2SONG 5 - 745

I have tried the following union query which combines the two RS's then sorts the data:
SELECT Top 2 ID, Song, Hits FROM Table1
UNION SELECT Top 2 ID, Song, Hits from Table2
Which would return the first two records from each then sort them like this:
2 - tb1SONG 2 - 1459
1 - tb2SONG 1 - 412
1 - tb1SONG 1 - 356
2 - tb2SONG 2 - 85

I would like to sort based on the hits column then combine the RS producing this:
3 - tb2SONG 3 - 2035
2 - tb1SONG 2 - 1459
4 - tb1SONG 4 - 965
5 - tb2SONG 5 - 745

Any ideas or solutions will be greatly appreciated.
Thankstry this (untested) --select *
from (
select top 2
ID
, Song
, Hits
from Table1
order by Hits
) as t1
union all
select *
from (
select top 2
ID
, Song
, Hits
from Table2
order by Hits
) as t2|||r937,

:)THANK YOU!!!:)
That worked great. I threw a Order By at the very end of your code and it sorted both together exactly as I wanted.

select *
from (
select top 2
ID
, Song
, Hits
from Table1
order by Hits
) as t1
union all
select *
from (
select top 2
ID
, Song
, Hits
from Table2
order by Hits
) as t2 ORDER BY Hits

Thanks Again!!!

Combine multiple rows into one

I have two tables that I need to join to get the required data, but I get too many rows returned with everything I have tried. Basically, both tables have and IDEA_ID field, and a simple SELECT query on TABLE1 will give me the data that I want from that table, but I get too many rows returned from TABLE2 as there are multiple rows with the same IDEA_ID in TABLE2.

Basically,

SELECT *
FROM TABLE1

gives me all the data I need with the exception of 6 columns for which the data is in TABLE2.

TABLE2 has 3 columns - IDEA_ID, ROLE_ID and ADATE. IDEA_ID matches the IDEA_ID field in TABLE1; ROLE_ID is a number from 1 to 6; and ADATE is a date. There can be 1 to 6 rows for each IDEA_ID, with the IDEA_ID and ROLE_ID combination being unique.

How can I run a query that will give me rows that have the fields from TABLE1, and 6 additional columns from TABLE2?

Table structures are:

TABLE1
IDEA_ID, PROD_TYPE, TITLE, DESC

TABLE2
IDEA_ID, ROLE_ID, ADATEWhich six columns do you need from the second table?

-PatP|||Pat

For each IDEA_ID in TABLE2, there are potentially 6 rows with that IDEA_ID, but with a number from 1 to 6 in the ROLE_ID column.

What I'm trying to do is to create a dataset that has all of the columns from TABLE1, but I want to create 6 new columns with data from TABLE2. Each of the new columns will represent a row from TABLE2 for each of the values of ROLE_ID from 1 to 6. The value that will go into each of these columns is the value in ADATE.

I can query TABLE2 to get these values:

SELECT idea_id,
decode(role_id, 1, adate),
decode(role_id, 2, adate),
decode(role_id, 3, adate),
decode(role_id, 4, adate),
decode(role_id, 5, adate),
decode(role_id, 6, adate)
FROM TABLE2

but this produces 6 rows of output. What I want from TABLE2 is a single row with each of these values. The query above produces a dataset with IDEA_ID plus 6 columns, each row having only one of those 6 column containing a value.

How do I produce just one row of output with all 6 of these columns having a value in it?

Column in profiler that shows no. of rows returned

I want to see for every stored procedure that completes, how many rows were
returned as output ? Is there a column in profiler that shows that value or
is there any other way to get it as opposed to running every query manually
on my Management Studio session.
Using SQL 2005.
Thanks
Found the answer.. Its the rowcounts column.
"Hassan" <hassan@.test.com> wrote in message
news:%2326rYcZLIHA.5224@.TK2MSFTNGP02.phx.gbl...
>I want to see for every stored procedure that completes, how many rows were
>returned as output ? Is there a column in profiler that shows that value or
>is there any other way to get it as opposed to running every query manually
>on my Management Studio session.
> Using SQL 2005.
> Thanks
>
|||Hi Hassan
There is nothing to show the rows for the entire procedure, since one
procedure can do lots of different things. However, if you also capture
SP:StmtCompleted, the IntegerData column will show the rows affected for
each statement within your stored procedure.
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"Hassan" <hassan@.test.com> wrote in message
news:%2326rYcZLIHA.5224@.TK2MSFTNGP02.phx.gbl...
>I want to see for every stored procedure that completes, how many rows were
>returned as output ? Is there a column in profiler that shows that value or
>is there any other way to get it as opposed to running every query manually
>on my Management Studio session.
> Using SQL 2005.
> Thanks
>
|||Wow... I'd never noticed that column before. I've always just used
IntegerData.
However, it appears that Rowcounts is for all rows affected, not just rows
returned. If your procedure does any data modification operations, the rows
changed will be included in the rowcounts.
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"Hassan" <hassan@.test.com> wrote in message
news:%23vIphkZLIHA.3940@.TK2MSFTNGP05.phx.gbl...
> Found the answer.. Its the rowcounts column.
> "Hassan" <hassan@.test.com> wrote in message
> news:%2326rYcZLIHA.5224@.TK2MSFTNGP02.phx.gbl...
>

Column in profiler that shows no. of rows returned

I want to see for every stored procedure that completes, how many rows were
returned as output ? Is there a column in profiler that shows that value or
is there any other way to get it as opposed to running every query manually
on my Management Studio session.
Using SQL 2005.
ThanksFound the answer.. Its the rowcounts column.
"Hassan" <hassan@.test.com> wrote in message
news:%2326rYcZLIHA.5224@.TK2MSFTNGP02.phx.gbl...
>I want to see for every stored procedure that completes, how many rows were
>returned as output ? Is there a column in profiler that shows that value or
>is there any other way to get it as opposed to running every query manually
>on my Management Studio session.
> Using SQL 2005.
> Thanks
>|||Hi Hassan
There is nothing to show the rows for the entire procedure, since one
procedure can do lots of different things. However, if you also capture
SP:StmtCompleted, the IntegerData column will show the rows affected for
each statement within your stored procedure.
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"Hassan" <hassan@.test.com> wrote in message
news:%2326rYcZLIHA.5224@.TK2MSFTNGP02.phx.gbl...
>I want to see for every stored procedure that completes, how many rows were
>returned as output ? Is there a column in profiler that shows that value or
>is there any other way to get it as opposed to running every query manually
>on my Management Studio session.
> Using SQL 2005.
> Thanks
>|||Wow... I'd never noticed that column before. I've always just used
IntegerData.
However, it appears that Rowcounts is for all rows affected, not just rows
returned. If your procedure does any data modification operations, the rows
changed will be included in the rowcounts.
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"Hassan" <hassan@.test.com> wrote in message
news:%23vIphkZLIHA.3940@.TK2MSFTNGP05.phx.gbl...
> Found the answer.. Its the rowcounts column.
> "Hassan" <hassan@.test.com> wrote in message
> news:%2326rYcZLIHA.5224@.TK2MSFTNGP02.phx.gbl...
>

Column in profiler that shows no. of rows returned

I want to see for every stored procedure that completes, how many rows were
returned as output ? Is there a column in profiler that shows that value or
is there any other way to get it as opposed to running every query manually
on my Management Studio session.
Using SQL 2005.
ThanksFound the answer.. Its the rowcounts column.
"Hassan" <hassan@.test.com> wrote in message
news:%2326rYcZLIHA.5224@.TK2MSFTNGP02.phx.gbl...
>I want to see for every stored procedure that completes, how many rows were
>returned as output ? Is there a column in profiler that shows that value or
>is there any other way to get it as opposed to running every query manually
>on my Management Studio session.
> Using SQL 2005.
> Thanks
>|||Hi Hassan
There is nothing to show the rows for the entire procedure, since one
procedure can do lots of different things. However, if you also capture
SP:StmtCompleted, the IntegerData column will show the rows affected for
each statement within your stored procedure.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"Hassan" <hassan@.test.com> wrote in message
news:%2326rYcZLIHA.5224@.TK2MSFTNGP02.phx.gbl...
>I want to see for every stored procedure that completes, how many rows were
>returned as output ? Is there a column in profiler that shows that value or
>is there any other way to get it as opposed to running every query manually
>on my Management Studio session.
> Using SQL 2005.
> Thanks
>|||Wow... I'd never noticed that column before. I've always just used
IntegerData.
However, it appears that Rowcounts is for all rows affected, not just rows
returned. If your procedure does any data modification operations, the rows
changed will be included in the rowcounts.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"Hassan" <hassan@.test.com> wrote in message
news:%23vIphkZLIHA.3940@.TK2MSFTNGP05.phx.gbl...
> Found the answer.. Its the rowcounts column.
> "Hassan" <hassan@.test.com> wrote in message
> news:%2326rYcZLIHA.5224@.TK2MSFTNGP02.phx.gbl...
>>I want to see for every stored procedure that completes, how many rows
>>were returned as output ? Is there a column in profiler that shows that
>>value or is there any other way to get it as opposed to running every
>>query manually on my Management Studio session.
>> Using SQL 2005.
>> Thanks
>

Column Count from Stored Procedure

I am trying to determine the number of columns that are returned from
a stored procedure using TSQL. I have a situation where users will be
creating their own procedures of which I need to call and place those
results in a temp table. I will not be able to modify those users
procedures. I figure if I have the number of columns I can dynamically
create a temp table with the same number of columns, at which point I
can then perform an INSERT INTO #TempTableCreatedDynamically EXEC
@.UserProcCalled. With that said, does anyone have any idea how to
determine the number of rows that an SP will return in TSQL?

Thanks!rolandobarberis@.hotmail.com (Rolando Barberis) wrote in message news:<ca3f9ee4.0405051740.7e090c11@.posting.google.com>...
> I am trying to determine the number of columns that are returned from
> a stored procedure using TSQL. I have a situation where users will be
> creating their own procedures of which I need to call and place those
> results in a temp table. I will not be able to modify those users
> procedures. I figure if I have the number of columns I can dynamically
> create a temp table with the same number of columns, at which point I
> can then perform an INSERT INTO #TempTableCreatedDynamically EXEC
> @.UserProcCalled. With that said, does anyone have any idea how to
> determine the number of rows that an SP will return in TSQL?
> Thanks!

If you really need to do this, the easiest way would be to do it on
the client side - for example, retrieve an ADO RecordSet, then use the
RecordSet metadata to create a table.

However, there are some issues with this general approach - if you
don't know the format of the result set in advance, then it's
difficult to do anything meaningful with it. You can't write SQL code
to process the temp table, because you don't know anything about the
number of columns, the data types, the row count (you might want to
use paging for a large result set, for example) etc. And this is in
addition to the obvious issues (security, performance, maintenance)
which may arise from allowing users to create their own code in the
database.

But since you don't give any detailed information about your goals or
your environment, it's possible that you do have good reasons for
looking at this solution. If you can give more information about what
you're trying to do, though, someone may have an alternative idea to
propose.

Simon|||Rolando Barberis (rolandobarberis@.hotmail.com) writes:
> I am trying to determine the number of columns that are returned from
> a stored procedure using TSQL. I have a situation where users will be
> creating their own procedures of which I need to call and place those
> results in a temp table. I will not be able to modify those users
> procedures. I figure if I have the number of columns I can dynamically
> create a temp table with the same number of columns, at which point I
> can then perform an INSERT INTO #TempTableCreatedDynamically EXEC
> @.UserProcCalled. With that said, does anyone have any idea how to
> determine the number of rows that an SP will return in TSQL?

This is a dead end. There is no way you can do this in SQL only. As Simon
says, you need to go client-side and deal with the the procedures there.

Not even client-side there is any good way to determine the number of
columns without running the procedure. In some contexts, ADO uses
SET FMTONLY ON which causes SQL Server to only sift through the statements
without executing them, but still return information about the result
sets. But there are several unexpected things that can happen with SET
FMTONLY ON, so in my opinion it's useless.

Then again, once you are client-side, it is not problem to run the
procedures. SQL Server will return information about the result sets,
and you will get recordsets, data tables or whatever depending on
which client library you use. (My personal preference is for ADO .Net.)

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Column Count from Stored Procedure

I am trying to determine the number of columns that are returned from
a stored procedure using TSQL. I have a situation where users will be
creating their own procedures of which I need to call and place those
results in a temp table. I will not be able to modify those users
procedures. I figure if I have the number of columns I can dynamically
create a temp table with the same number of columns, at which point I
can then perform an INSERT INTO #TempTableCreatedDynamically EXEC
@.UserProcCalled. With that said, does anyone have any idea how to
determine the number of rows that an SP will return in TSQL?

Thanks!rolandobarberis@.hotmail.com (Rolando Barberis) wrote in message news:<ca3f9ee4.0405051740.7e090c11@.posting.google.com>...
> I am trying to determine the number of columns that are returned from
> a stored procedure using TSQL. I have a situation where users will be
> creating their own procedures of which I need to call and place those
> results in a temp table. I will not be able to modify those users
> procedures. I figure if I have the number of columns I can dynamically
> create a temp table with the same number of columns, at which point I
> can then perform an INSERT INTO #TempTableCreatedDynamically EXEC
> @.UserProcCalled. With that said, does anyone have any idea how to
> determine the number of rows that an SP will return in TSQL?
> Thanks!

If you really need to do this, the easiest way would be to do it on
the client side - for example, retrieve an ADO RecordSet, then use the
RecordSet metadata to create a table.

However, there are some issues with this general approach - if you
don't know the format of the result set in advance, then it's
difficult to do anything meaningful with it. You can't write SQL code
to process the temp table, because you don't know anything about the
number of columns, the data types, the row count (you might want to
use paging for a large result set, for example) etc. And this is in
addition to the obvious issues (security, performance, maintenance)
which may arise from allowing users to create their own code in the
database.

But since you don't give any detailed information about your goals or
your environment, it's possible that you do have good reasons for
looking at this solution. If you can give more information about what
you're trying to do, though, someone may have an alternative idea to
propose.

Simon

Column ''cb.CurrentBalance'' is invalid in the HAVING clause ...

Not sure why I am getting this error below. It has someting to do with my CurrentBalance calculation portion in my INNER JOIN area:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

SELECT rm.rmsacctnum AS [Rms Acct Num],

SUM(rf.rmstranamt) AS [TranSum],

SUM(rf10.rmstranamt10) AS [10Sum],

SUM(rf10.rmstranamt10) - SUM(rf.rmstranamt) AS [Balance]

FROM RMASTER rm

INNER JOIN

(

SELECT RMSFILENUM,

SUM(rmstranamt) AS rmstranamt10

FROM RFINANL

WHERE RMSTRANCDE = '10'

GROUP BY RMSFILENUM

) AS rf10 ON rf10.RMSFILENUM = rm.RMSFILENUM

INNER JOIN

(

SELECT RMSFILENUM,

RMSTRANCDE,

SUM(rmstranamt) AS rmstranamt

FROM RFINANL

WHERE RMSTRANCDE <> '10'

GROUP BY RMSFILENUM, RMSTRANCDE

) AS rf ON rf.RMSFILENUM = rm.RMSFILENUM

INNER JOIN

(SELECT RMSFILENUM, (SELECT (rb.RMSCHGAMT - rb.RMSRCVPCPL)

+(rb.RMSASSCCST - rb.RMSRCVDCST)

+(rb.RMSACRDINT - rb.RMSRCVDINT)

+(rb.UDCCOSTS1 - rb.UDCRECCS1)

+(rb.UDCCOSTS2 - rb.UDCRECCS2)

+(rb.RMSCOST1 - rb.RMSCOST1R)

+(rb.RMSCOST2 - rb.RMSCOST2R)

+(rb.RMSCOST3 - rb.RMSCOST3R)

+(rb.RMSCOST4 - rb.RMSCOST4R)

+(rb.RMSCOST5 - rb.RMSCOST5R)

+(rb.RMSCOST6 - rb.RMSCOST6R)

+(rb.RMSCOST7 - rb.RMSCOST7R)

+(rb.RMSCOST8 - rb.RMSCOST8R)

+(rb.RMSCOST9 - rb.RMSCOST9R)

+(rb.RMSCOST10 - rb.RMSCOST10R)

- rb.RMSXCSRCVS

FROM RPRDBAL rb) as CurrentBalance

FROM RPRDBAL)

AS cb ON cb.RMSFILENUM = rm.RMSFILENUM

WHERE rm.rmsacctnum = '4313030999894992'

GROUP BY rm.rmsacctnum, rf10.rmstranamt10

HAVING cb.CurrentBalance <> SUM(rf10.rmstranamt10) - SUM(rf.rmstranamt)

AND cb.CurrentBalance <> 0.00

SELECT rm.rmsacctnum AS [Rms Acct Num],

SUM(rf.rmstranamt) AS [TranSum],

SUM(rf10.rmstranamt10) AS [10Sum],

SUM(rf10.rmstranamt10) - SUM(rf.rmstranamt) AS [Balance],

cb.CurrentBalance

FROM RMASTER rm

INNER JOIN

(

SELECT RMSFILENUM,

SUM(rmstranamt) AS rmstranamt10

FROM RFINANL

WHERE RMSTRANCDE = '10'

GROUP BY RMSFILENUM

) AS rf10 ON rf10.RMSFILENUM = rm.RMSFILENUM

INNER JOIN

(

SELECT RMSFILENUM,

RMSTRANCDE,

SUM(rmstranamt) AS rmstranamt

FROM RFINANL

WHERE RMSTRANCDE <> '10'

GROUP BY RMSFILENUM, RMSTRANCDE

) AS rf ON rf.RMSFILENUM = rm.RMSFILENUM

INNER JOIN

(SELECT RMSFILENUM,( (RMSCHGAMT - RMSRCVPCPL)

+(RMSASSCCST - RMSRCVDCST)

+(RMSACRDINT - RMSRCVDINT)

+(UDCCOSTS1 - UDCRECCS1)

+(UDCCOSTS2 - UDCRECCS2)

+(RMSCOST1 - RMSCOST1R)

+(RMSCOST2 - RMSCOST2R)

+(RMSCOST3 - RMSCOST3R)

+(RMSCOST4 - RMSCOST4R)

+(RMSCOST5 - RMSCOST5R)

+(RMSCOST6 - RMSCOST6R)

+(RMSCOST7 - RMSCOST7R)

+(RMSCOST8 - RMSCOST8R)

+(RMSCOST9 - RMSCOST9R)

+(RMSCOST10 - RMSCOST10R)

- RMSXCSRCVS

) as CurrentBalance

FROM RPRDBAL)

AS cb ON cb.RMSFILENUM = rm.RMSFILENUM

--WHERE rm.rmsacctnum = '4313030999894992'

GROUP BY rm.rmsacctnum, cb.CurrentBalance

HAVING cb.CurrentBalance <> SUM(rf10.rmstranamt10) - SUM(rf.rmstranamt)

AND cb.CurrentBalance <> 0.00