Merge Join Output Bug?

I've run into something that looks like a bug to me but I wanted to run it by the board:

Merge join 2 sorted tables.

Table1: ColumnA : Sort Order 1, ColumnB Sort Order 2

Table2 : ColumnA: Sort Order 1, ColumnB Sort Order 2, ColumnC not sorted

Merge Join the two tables on ColumnA and ColumnB...

Choose the following as output columns

A + B + C = works

C = works

A + C = works

B + C = NOT work.. error message: The column with the SortKeyPosition value of 0 is not valid. It should be 2.

Basically if you choose one or more of the sorted columns in the output at least one of them has to be the column with Sort position 1 or you'll get that error.

Is this a bug or intentional? If you do not have sort column 1 in the output that output could no longer be considered sorted... so perhaps the error is related to that (instead of error I'd expect some warning about the sorting). Interesting that it lets you choose C only becuase that also makes the output unsorted.

I see your point Chris.

I think it is intential -

. The reason why B+C not work is because column B has a non-zero sortKeyPosition which indicates the output (to which B belongs) should be sorted (in other words, the output's "isSorted" property is true), but the output can not find a column with SortKeyPosition 1

. As for why C column only works is because the output is then not sorted.

If you think the error message is not very helpful, please log a customer issue through our connect website http://connect.microsoft.com/SQLServer and your request will be addressed soon as appropriate.

Thanks

wenyang

Merge Join Output Bug?

I've run into something that looks like a bug to me but I wanted to run it by the board:

Merge join 2 sorted tables.

Table1: ColumnA : Sort Order 1, ColumnB Sort Order 2

Table2 : ColumnA: Sort Order 1, ColumnB Sort Order 2, ColumnC not sorted

Merge Join the two tables on ColumnA and ColumnB...

Choose the following as output columns

A + B + C = works

C = works

A + C = works

B + C = NOT work.. error message: The column with the SortKeyPosition value of 0 is not valid. It should be 2.

Basically if you choose one or more of the sorted columns in the output at least one of them has to be the column with Sort position 1 or you'll get that error.

Is this a bug or intentional? If you do not have sort column 1 in the output that output could no longer be considered sorted... so perhaps the error is related to that (instead of error I'd expect some warning about the sorting). Interesting that it lets you choose C only becuase that also makes the output unsorted.

I see your point Chris.

I think it is intential -

. The reason why B+C not work is because column B has a non-zero sortKeyPosition which indicates the output (to which B belongs) should be sorted (in other words, the output's "isSorted" property is true), but the output can not find a column with SortKeyPosition 1

. As for why C column only works is because the output is then not sorted.

If you think the error message is not very helpful, please log a customer issue through our connect website http://connect.microsoft.com/SQLServer and your request will be addressed soon as appropriate.

Thanks

wenyang

merge datasets

Is there a way to merge to datasets in order to view the data in one table. IE:
Dataset1 Dataset2

Customer Q1 Cutomer Q2
A 1 A 5
B 3 B 2

Table
Customer Q1 Q2
A 1 5
B 3 2yes, you can use the Merge method:
Dataset1.Merge(Dataset2)
you need to specify a primary key for each dataset|||

How would this work in a report. ie.. user opens a report and hits refresh. is there a place this code is run so that it executes when either dataset1 or dataset2 is refreshed. I am new to Reporting Services so please excuse.

|||

Couldnt you merger the two datasets on the SQL side?

Put Q1 and Q2 into temp tables, select Q1 into another temp, then left outer join Q2 ON Q1.ID = Q2.ID.
Just a thought, I have had to merge a bunch of datasets on the back end because old procs were returning multiple datasets,...

|||You definitely could do it on the database side. Seems a bit difficult if the tables are in different databases. I am busy doing an evaluation of reporting services 2005. The ability to merge "datasets" is available and very simple to do in our current reporting enviroment.(Business Objects) We are trying to do the same type of things using RS.

Merge 2 queries

Hi all i have 2 queries. One of which works out where 2 values are equal and the other where where all value are there.. This is in order for me to divid one by another to get percentage.

THIS IS FOR ALL
select results.playerid, players.playername, count (results.playerid) as allgames
from results, players
where results.playerid = players.playerid
group by results.playerid, players.playername
order by allgames desc

THIS IS FOR WON
select results.playerid, players.playername, count (results.result) as wongames
from results, players
where results.result = 1 and
results.playerid = players.playerid
group by results.result, results.playerid, players.playername
order by results.playerid asc

I can make them have the same view output...

How can i merge them into one to get percentage.. Ideally i would like to this on the fly because once i have all the result i want i want to do a front end on the net.

I am using SQL server Enterprise Manage.

Thanks in advance.declare @.var1 float, @.var2 float, @.var3 float

set @.var1 = (select count (results.playerid) from results, players where results.playerid = players.playerid)
set @.var2 = (select count (results.result) as wongames from results, players where results.result = 1 and results.playerid = players.playerid)

set @.var3 = @.var2/@.var1 * 100

select @.var3
--or--
select convert(varchar(50),@.var3)+'%'|||thank you!! i'll try it tomorrow.|||is this method only able to send single value???

"Subquery returned more than 1 value"

Menu Hierarchy Display Order (Recursion)

Hi -
I'm using the following Table and Stored Procedure below, to store and
access a menu hierarchy. The recursive stored procedure is working
perfectly and returns a text string outlining my current menu hierarchy.
Currently the stored procedure returns the hiearchy in the order that the
records were created (MenuItemID)
However, I've recently added a "DisplayOrder" column to my table. For
example, let's say that I want Biking to appear before Skiing. Changing the
item's MenuItemID is not an option. So, I'm using a DisplayOrder column. I
need to integrate the DisplayOrder column into my stored procedure so that
it returns the hierarchy in the correct order. I'm not sure what I'm doing
wrong, but I just can't seem to get this part working.
Any help would be appreciated.
Thanks,
Brian
CREATE TABLE MenuItem
(
MenuItemID INT,
ItemName VARCHAR(30),
ParentID INT,
DisplayOrder INT
)
INSERT INTO MenuItem (MenuItemID, ItemName, ParentID, DisplayOrder) VALUES
(1, 'Activities', NULL, 0)
INSERT INTO MenuItem (MenuItemID, ItemName, ParentID, DisplayOrder) VALUES
(2, 'Fishing', 1, 0)
INSERT INTO MenuItem (MenuItemID, ItemName, ParentID, DisplayOrder) VALUES
(3, 'Skiing', 1, 1)
INSERT INTO MenuItem (MenuItemID, ItemName, ParentID, DisplayOrder) VALUES
(4, 'Hiking', 1, 2)
INSERT INTO MenuItem (MenuItemID, ItemName, ParentID, DisplayOrder) VALUES
(5, 'Biking', 1, 3)
INSERT INTO MenuItem (MenuItemID, ItemName, ParentID, DisplayOrder) VALUES
(6, 'Swimming', 1, 4)
CREATE PROCEDURE spCreateMenu (
@.TargetMenuItemID INT
)
AS
BEGIN
SET NOCOUNT ON
DECLARE @.MenuItemID INT,
@.ItemName VARCHAR(30),
@.ParentID INT,
@.DisplayOrder INT
SELECT @.MenuItemID=MenuItemID, @.ItemName=ItemName, @.ParentID=ParentID FROM
MenuItem WHERE MenuItemID = @.TargetMenuItemID
PRINT REPLICATE('-', @.@.NESTLEVEL * 4) + @.ItemName
SET @.MenuItemID = (SELECT MIN(MenuItemID) FROM MenuItem WHERE ParentID =
@.TargetMenuItemID)
WHILE @.MenuItemID IS NOT NULL
BEGIN
EXEC spCreateMenu @.MenuItemID
SET @.MenuItemID = (SELECT MIN(MenuItemID) FROM MenuItem WHERE ParentID =
@.TargetMenuItemID AND MenuItemID > @.MenuItemID)
END
END
EXEC spCreateMenu 1Hi Brian,
Try this.. Hope this helps.
ALTER PROCEDURE spCreateMenu (
@.TargetMenuItemID INT
)
AS
BEGIN
SET NOCOUNT ON
DECLARE @.MenuItemID INT,
@.ItemName VARCHAR(30),
@.ParentID INT,
@.DisplayOrder INT,
@.rowcount int
SELECT @.ItemName=ItemName, @.ParentID=ParentID FROM
MenuItem WHERE MenuItemID = @.TargetMenuItemID
PRINT REPLICATE('-', @.@.NESTLEVEL * 4) + @.ItemName
SELECT TOP 1 @.MenuItemID = ISNULL(MenuItemID,0), @.DisplayOrder =
DisplayOrder FROM MenuItem WHERE ParentID =
@.TargetMenuItemID ORDER BY DISPLAYORDER
set @.rowcount = @.@.rowcount
WHILE (@.rowcount <> 0)
BEGIN
EXEC spCreateMenu @.MenuItemID
SELECT TOP 1 @.MenuItemID = ISNULL(MenuItemID,0), @.DisplayOrder =
DisplayOrder FROM MenuItem WHERE ParentID =
@.TargetMenuItemID AND DisplayOrder> @.DisplayOrder ORDER BY DISPLAYORDER
set @.rowcount = @.@.rowcount
END
END|||Excellent - Thanks very much for your help!!
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:B9AE2855-8675-4E07-BF3D-52A2BF892335@.microsoft.com...
> Hi Brian,
> Try this.. Hope this helps.
> ALTER PROCEDURE spCreateMenu (
> @.TargetMenuItemID INT
> )
> AS
> BEGIN
> SET NOCOUNT ON
> DECLARE @.MenuItemID INT,
> @.ItemName VARCHAR(30),
> @.ParentID INT,
> @.DisplayOrder INT,
> @.rowcount int
> SELECT @.ItemName=ItemName, @.ParentID=ParentID FROM
> MenuItem WHERE MenuItemID = @.TargetMenuItemID
> PRINT REPLICATE('-', @.@.NESTLEVEL * 4) + @.ItemName
> SELECT TOP 1 @.MenuItemID = ISNULL(MenuItemID,0), @.DisplayOrder =
> DisplayOrder FROM MenuItem WHERE ParentID =
> @.TargetMenuItemID ORDER BY DISPLAYORDER
> set @.rowcount = @.@.rowcount
> WHILE (@.rowcount <> 0)
> BEGIN
> EXEC spCreateMenu @.MenuItemID
> SELECT TOP 1 @.MenuItemID = ISNULL(MenuItemID,0), @.DisplayOrder =
> DisplayOrder FROM MenuItem WHERE ParentID =
> @.TargetMenuItemID AND DisplayOrder> @.DisplayOrder ORDER BY DISPLAYORDER
> set @.rowcount = @.@.rowcount
> END
> END|||Look up CTEs (common table expressions). They handle recursive logic
without actually having a recursive stored procedure. One SQL statement
will do it for you.
"Brian Patrick" <bpatrick100@.hotmail.com> wrote in message
news:OUFoLJKaGHA.3652@.TK2MSFTNGP03.phx.gbl...
> Hi -
> I'm using the following Table and Stored Procedure below, to store and
> access a menu hierarchy. The recursive stored procedure is working
> perfectly and returns a text string outlining my current menu hierarchy.
> Currently the stored procedure returns the hiearchy in the order that the
> records were created (MenuItemID)
> However, I've recently added a "DisplayOrder" column to my table. For
> example, let's say that I want Biking to appear before Skiing. Changing
the
> item's MenuItemID is not an option. So, I'm using a DisplayOrder column.
I
> need to integrate the DisplayOrder column into my stored procedure so that
> it returns the hierarchy in the correct order. I'm not sure what I'm
doing
> wrong, but I just can't seem to get this part working.
> Any help would be appreciated.
> Thanks,
> Brian
>
> CREATE TABLE MenuItem
> (
> MenuItemID INT,
> ItemName VARCHAR(30),
> ParentID INT,
> DisplayOrder INT
> )
> INSERT INTO MenuItem (MenuItemID, ItemName, ParentID, DisplayOrder) VALUES
> (1, 'Activities', NULL, 0)
> INSERT INTO MenuItem (MenuItemID, ItemName, ParentID, DisplayOrder) VALUES
> (2, 'Fishing', 1, 0)
> INSERT INTO MenuItem (MenuItemID, ItemName, ParentID, DisplayOrder) VALUES
> (3, 'Skiing', 1, 1)
> INSERT INTO MenuItem (MenuItemID, ItemName, ParentID, DisplayOrder) VALUES
> (4, 'Hiking', 1, 2)
> INSERT INTO MenuItem (MenuItemID, ItemName, ParentID, DisplayOrder) VALUES
> (5, 'Biking', 1, 3)
> INSERT INTO MenuItem (MenuItemID, ItemName, ParentID, DisplayOrder) VALUES
> (6, 'Swimming', 1, 4)
>
> CREATE PROCEDURE spCreateMenu (
> @.TargetMenuItemID INT
> )
> AS
> BEGIN
> SET NOCOUNT ON
> DECLARE @.MenuItemID INT,
> @.ItemName VARCHAR(30),
> @.ParentID INT,
> @.DisplayOrder INT
> SELECT @.MenuItemID=MenuItemID, @.ItemName=ItemName, @.ParentID=ParentID
FROM
> MenuItem WHERE MenuItemID = @.TargetMenuItemID
> PRINT REPLICATE('-', @.@.NESTLEVEL * 4) + @.ItemName
> SET @.MenuItemID = (SELECT MIN(MenuItemID) FROM MenuItem WHERE ParentID =
> @.TargetMenuItemID)
> WHILE @.MenuItemID IS NOT NULL
> BEGIN
> EXEC spCreateMenu @.MenuItemID
> SET @.MenuItemID = (SELECT MIN(MenuItemID) FROM MenuItem WHERE ParentID =
> @.TargetMenuItemID AND MenuItemID > @.MenuItemID)
> END
> END
>
> EXEC spCreateMenu 1
>